Creating Individual Reports from Data in a Table

[Melissa]

I have a table of information that I am using to generate a report in Access
2003.

ID (unique field) Name Score
1 Melissa 80
2 Amy 85
3 Jeff 87

I am generating a separate report for each value. In this case, I would have
three different reports.

The field names never change which makes it easy to keep a template for my
report.

The problem I have is the data source changes. For example, I have the table
above but tomorrow I might need to generate a new set of reports for….

ID (unique field) Name Score
4 Bob 80
5 Sue 85
6 Dawn 87


Right now to make this work, I have to change the record source for every
report I need to generate. Is there a way to set it up so the reports are
automatically created for all the records in the table?

I have a feeling this may require some coding to make work. Let me say that
I have very little experience with code. So, as much explanation as you can
provide to make it work if code is necessary would be much appreciated.

 

[Clifford Bass]

Hi Melissa,

There should not be any need to do any coding for basic reporting
needs. So, I am confused. You state that you have to change the data
source. But you show the same structure, which implies the same table. Do
you mean that the data is in a different table each time? If so, why?

Clifford Bass

 

[Melissa]

Hi Clifford,

The table always stays the same but the information in the table is what
changes. So, referring back to my previous example, I have a table of info
for IDs 1,2 and 3 that I have to generate a report for. Then the next week
the table of info will change and will have info for IDs 4, 5, & 6 that I
have to generate a new report for. So, the problem is the IDs I use to create
the custom report are ever changing. And there are over 500 IDs. So, I could
create 500 separate reports but it seems like there might be an easier way to
do it. I just don't know how complex the solution may be.

 

[Clifford Bass]

Hi Melissa,

All you need is one report. Create a query that asks for/uses a couple
of parameters and use that query as the data source for the report. When you
create the query, choose your fields, including the ID field. In the
criteria line for the ID field, enter "between [Start ID:] and Nz([End ID:],
[Start ID:])", without the quotes. If using Access 2003 or earlier, go to
the Query menu and choose Parameters. If using Access 2007 click on the
Parameters item in the Show/Hide section of the Query Tools/Design ribbon.
On the first line Enter [Start ID:] and choose the data type of your ID
column. On the second line enter [End ID:] and the data type of ID. Now
when you run the report it will ask you for a start and end ID. Enter a
start ID when asked. Then an end ID when asked. This will then print the
data for the ID numbers between the starting and ending ones you enter,
including those IDs. The Nz([End ID:], [Start ID:]) part of the between
criteria says: when End ID: is null (nothing entered) to use the Start ID: as
the End ID:. So if you only want one record, you can just leave the End ID:
blank.

That should get you to your goal. If further questions or troubles,
post back.

Clifford Bass