Using Records from a Table to Feed into Queries to Generate a Report

[Jessica240]

Hello. I have a table with only one field, "Names." It has a list of about 50 names. I simply want Access to generate a report for each of the names in the table.

My form has a combo box which allows the user to select a name from the Name table.

The name the user selects then becomes the criteria in the queries that provide the data for the report. The user selects the name and then clicks a button which opens the report for that name.



I have no VBA knowledge, but I can copy and paste some code in if that is possible. Or if someone knows another way around it.

Thank you.

[JeffChr]

You do not need vba to accomplish your mission. Just reference the form control as a criteria in your query. You can use the builder to assist with the reference. See the attached example.

[Jessica240]

Thank you for your help - I really appreciate it.

I currently have the form's combo box in the criteria of my queries. So I can generate a report for each name, but only one report at a time.

My goal is to have Access look at my Names table and automatically generate 50 reports at once (one report for each person). Do you know if that is doable?

[Jessica240]

Basically I want Access to look at my Names table, and insert each name into the queries, create a report based on those queries, and then move on to the next name and do the same thing until all names in the table have been used.

[JeffChr]

Sure. Just use the names table as the record source for your report and create a field on your report for the name - which it sounds like you have already done. No query needed.

[Jessica240]

The queries contain the data needed on the report. Basically I am trying to redesign my payroll system for my employees. Each pay period, I will import an Excel sheet that has thousands of records. Each record contains elements for which the employees should get paid on.

After I import the Excel spreadsheet, this data is filtered through a series of queries that will generate a report showing how much each employee needs to be paid.

So for example, in this last pay period, employee Bob might have mowed the lawn, fixed 5 light bulbs, and power-washed the sidewalk. Each of those items is paid differently.

The queries use formulas to calculate what Bob is owed.

I want Access to calculate what all 50 employees need to get paid separately so that I can print their report and include it with their paycheck. So the record source for the report has to be Query X, which has all the elements and the pay calculations. I then just want to click one button and then have 50 separate reports generated all at once.

[JeffChr]

Assuming your final query has one pay record per employee you should be able to accomplish your goal by using the query as a data source for your report. Just remove the criteria.

[Jessica240]

No that's the problem. My final query has to summarize the pay calculations for the employee and has a record for each payable item. The resulting report will look like this:

Emp. Name...........Item.....................Payable
Bob......................Car Wash..............$10.00
Bob......................Transportation.......$50. 00
Bob......................Maintenance..........$75. 00

Total............................................. ....$135.00

[JeffChr]

OK - took a while but now I understand. Unfortunately reporting is not an area that I can help you with, much. But I can point you in the right direction. You want to utilizing grouping on your report and then page break on the group. Sorry I am not an expert in this area but this will get you started: https://support.office.com/en-in/art...__toc285530060