Is it possible to report by table record number?

[Jon]

I have a table of temporary employee data, it is created by a 'make table
query' and is alphabetized. Every time an employee changes it is run to
produce a new alpha list.

I need to produce a 3 page report where each page has a different layout and
the
employee record printed is different on each page as below:

Each report is 1 page with different layout from the other reports.
report #1 report #2 report #3

empoyee #1 employee #6 employee #16
#2... to #5. #7...#15 #17....#25

For Example:
The first 5 The next 10 records the last 10 records
records in the table in the
table

Adams Hammond Stevens
Buchanan Ortega Thomas
Ellis Quincy Weller
2 more 7 more to the end of table


The order of employee must be alphabetical and my make table is for that
purpose. The problem I think I have is that the table has no index or
numerical key.

I have managed to cludge something together with a series of queries using a
combination of 'top' and the Access 'Unmatched Query Wizard'. It is a bit
'Rube Goldberg' and would be difficult for someone else to come along at a
later
time and figure out how it works.

I figure there must be a better solution and hope you can help point me in
the right direction.

I am wondering if I could merely add an index field to the alphabetized table
I created with the make table query. Then I could do a query using, for
example, "between 6 and 15" but I can't figure out how to add the field to
the table which would become the index to query.

Is adding a field like this possible? Is there a reporting solution that
will allow me to use records #1-5, then records #6-15, and finally record #16
to the end?

Thanks, jon

 

[Allen Browne]

It's easy enough to number the records in your report.
In report design view, just add a text box, with these properties:
Control Source =1
Running Sum Over Group
Explanation:
Numbering Entries in a Report or Form
at:
http://allenbrowne.com/casu-10.html

Querying is a different issue. If they are alphabetically arranged, it would
be possible to use DCount() to count the number of preceeding records, and
add 1. The problem here is defining "preceeding records" when 2 people have
the same name. An alternative would be to use something like ECount(), where
you can include the primary key at the end of the sorting argument so the
count is correct:
http://allenbrowne.com/ser-66.html

Of couse, it would be much easier to search by names instead of numbers.

 

[Marshall Barton]

I have a table of temporary employee data, it is created by a 'make table
query' and is alphabetized. Every time an employee changes it is run to
produce a new alpha list.

I need to produce a 3 page report where each page has a different layout and
the
employee record printed is different on each page as below:

Each report is 1 page with different layout from the other reports.
report #1 report #2 report #3

empoyee #1 employee #6 employee #16
#2... to #5. #7...#15 #17....#25

For Example:
The first 5 The next 10 records the last 10 records
records in the table in the
table

Adams Hammond Stevens
Buchanan Ortega Thomas
Ellis Quincy Weller
2 more 7 more to the end of table


The order of employee must be alphabetical and my make table is for that
purpose. The problem I think I have is that the table has no index or
numerical key.

As Jeff explained in your other thread, this is a report
problem. First, you have no need for a temp table and
without another **good** reason you should eliminate it from
your solution. All you need is a query that retrieves the
correct records and fields (without sorting).

Then the use the query as the report's record source. Use
the report's Sorting and Grouping window to specify how you
want the report sorted.

Now to your 5 records on the first page and 10 records on
the remaining pages question. First add the running sum
text box (name it txtLineNum) that Allen described to the
detail section. Next, add a PageBreak control (name it
pgEject) to the bottom of the detail section. Finally, add
a line of code to the detail section's Format event
procedure:

Me.pgEject.Visible = ((Me.txtLineNum + 5) Mod 10) = 0

 

[Jon]

It's easy enough to number the records in your report.

Allen & Marshall:

Thanks for the input and ideas. It's a bit more advanced than my current
knowledge and it will take some time digesting your recommendations and
hopefully I will come away with a better understanding.

Rgds, Jon