Microsoft Access 2010 report from 2 queries

Posted on 2011-05-10
Last Modified: 2012-05-11
I have 2 tables called customers and chemistries. I need to create a report that prompts for a heat number (queries chemistries table,) customer ID (queries customers table) and also some other free form fields and then displays the corresponding info in one report.

I have created to separate queries with parameters and they work individually.

I have read that you need to use a form and generate a report containing sub reports how ever if i try and create 1 main report with 2 sub reports (one with chemistry data and the other with the simple customer query) it prompts me for the parameter information like 4 times.

Can anyone explain how to do this in easy terms.

1 sub report takes the inputted customer id and returns the customer name, address, zip

1 sub report takes the inputted heat number and returns chemistry compositions of an item

i need to lay out both these reports in a single report and only have the user input the parameters once.

I read something about using a form but i don't know how to link two un-related queries to a form and then have it generate a report.

Question by:JSorensen
    LVL 47

    Accepted Solution

    The easiest way is to create a form where you enter the Heat#, CustomerID, and the other fields, then refer to those controls in your queries.  For example, you might have a control named txt_HeatNum on your form.

    In the query, to refer to that value you would use a syntax similar to:

    SELECT * FROM yourTABLE WHERE [Heat#] = Forms!formname.txt_HeatNum

    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    First you need to explain the structure of your DB, ...the tables and the fields.

    Then you need to explain the relationships behind these tables.

    The you must past a graphic example of the output you are requesting...

    To this end, (at least), I would like to see a sample DB to avoid guessing at all of the above variables:

     Sample database notes:
    1. Back up your database(s).
    2. Combine the front and back ends into one database file.
    3. Remove any startup options, unless they are relevant to the issue.
    4. Remove any records unless they are relevant to the issue.
    5. Delete any objects that do not relate directly to the issue.
    6. Remove any references to any "linked" files (files outside of the database, Images, OLE Files, ...etc)
    7. Remove any references to any third party Active-x Controls (unless they are relevant to the issue)
    8. Remove, obfuscate, encrypt, or otherwise disguise, any sensitive data.
    9. Compile the code. (From the database window, click: Debug-->Compile)
    10. Run the compact/Repair utility.
    11. Remove any Passwords and/or security.
    12. Post explicit steps to replicate the issue.
    13. Test the database before posting.

    In other words, a database that we can easily open and immediately see the issue, and if applicable, also include a Graphical representation of the Exact results you are expecting.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Top 6 Sources for Identifying Threat Actor TTPs

    Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

    It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now