grouping detail records for labels

[pampolk]

I have a query that returns the below results (I am using a small sample of data):

levelid levelname bookid title
1 A 974 A zoo
1 A 500 Aaron and Steve
1 A 765 Amazing Animals
1 A 257 Avengers
1 A 430 Avery and me
1 A 789 Boys are smart
1 A 497 Zebras

I need to create labels using the data from the above query.

Specifically I need to print on each label the level name AND four titles, then on the next label the level name AND the NEXT four titles, and so on for all of the titles related to level A.

I need to use Avery 5161 (1x4) 2 across

There are a total of 65 titles that are related to level A.
--
--
So using the above data as an example the labels would need to look like:

LEVEL A LEVEL A
A Zoo Avery and me
Aaron and Steve Boys are smart
Amazing Animals Zebras
Avengers

This is a tough one for me to figure out. Would I be focusing on the Detail section of the labels report and use NextRecord/PrintSection along with incrementing a variable? Should I run this from a form? I am not sure the best way to handle this. I have never had this request before.

Thanks very much.

Pam Polk

 

[randysmid]

Hi,
I haven't thought through this completely yet, but I think I can give you something to think about. Perhaps others on this forum can add their expertise to this problem. So, here is one part of my solution. We will want the final query to show data in the following format:
Level Title1 Title2 Title3 Title4
A A Zoo Aaron... Amazing.. Avengers
A Avery.. Boys... etc.

Do you see how you can create labels with the data coming in this format?

Now that I think about it, a VBA function can be written that is used to create a new table in the format I just outlined. This function would use a query that will pre-sort by level and Title Name. It would loop through the records, moving 4 titles into new table for each record. Of course, the code will have to make sure that the level did not change. What do you think so far?
HTH,
Randy Smith
California Teachers Association

 

[Dalain]

I don't know what size Avery 5161 is So Lets say it's 3.5 Inch by 1.75 Inch.
What do is create a report that will print 1 Label per Page. Make Sure the Report's over all size is 3.5 Inch by 1.75 Inchs.

Do a print Preview, then Select Page Setup. (Now you can select how the Report will Print.)

Here you can define the Number of Columns, Row Spacing, Column Spacing, Column size, Column layout

Edit the values until they match Avery 5161's layout.
Once you have printed the report, these values are saved with the report.


Pierre

 

[pampolk]

Hi Randy and Pierre

Thank you for responding to my post! I am going to take your ideas this weekend and implement them. I did receive a reply on another way to do this via a temp table. It did work out great. If you would like to see the code I can attach to this thread or I can attach the database.

I would like to see other ways to do this - this is a different request - so I will implement your ideas this weekend and post if I have any questions. Thank you.

Pam Polk