• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 186
  • Last Modified:

Microsoft Access 2010 report from 2 queries

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.

Joe Sorensen
Joe Sorensen
1 Solution
Dale FyeCommented:
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

Jeffrey CoachmanCommented:
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, ...post 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.


Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now