How to Split Related Records in Two if Greater than 15

[doyle60]

I have a report that has, for each page, photographs on top and data
about those photographs below. The report is designed to have as many
as 15 photos (5 wide, 3 down) and 15 lines of data. Each photo and
line represents a style/color. Each page is a Category (Field) of
styles. Once in a while, however, a category of related styles can
have more than 15 styles. When this happens, I want to put the first
15 on one page, the second 15 on another page, and so on.

The data is coming from a mainframe database and there just isn't any
field to grab.

My solution would be to have the data paste into a table that
organizes the data by category so the autonumbering on each category/
style/color would be sequential. Then use queries to count and divide
the ones over 15 by two (using the autonumbers). Etc. Etc. This is
exactly what I want but good enough.

But I was wondering if there is a more elegant solution. I want to
avoid doing ranking queries, which are nasty. I did that awhile ago
and it gives me a headache.

Does anyone have a more elegant solution?

Thanks,

Matt

 

[Marshall Barton]

I have a report that has, for each page, photographs on top and data
about those photographs below. The report is designed to have as many
as 15 photos (5 wide, 3 down) and 15 lines of data. Each photo and
line represents a style/color. Each page is a Category (Field) of
styles. Once in a while, however, a category of related styles can
have more than 15 styles. When this happens, I want to put the first
15 on one page, the second 15 on another page, and so on.

The data is coming from a mainframe database and there just isn't any
field to grab.

My solution would be to have the data paste into a table that
organizes the data by category so the autonumbering on each category/
style/color would be sequential. Then use queries to count and divide
the ones over 15 by two (using the autonumbers). Etc. Etc. This is
exactly what I want but good enough.

But I was wondering if there is a more elegant solution. I want to
avoid doing ranking queries, which are nasty. I did that awhile ago
and it gives me a headache.

With practice, you get used to the ranking headache ;-)

All depends on your table structure. If each detail record
contains one picture and one line of text, then just set the
report to 5 columns and make the detail section tall enough
so only 3 of them use up the space between the top and
bottom of the page.

 

[doyle60]

The report already works (sort of). I have it set to do the 5 by 3 as
you say.

The problem is that when it is greater that 15, it is a mess. I want
to control this mess.

So let's say there are 18 records in a certain category. I want the
first page for this categoy to show 15 photos and to print out the
data that relates to those photos (selling information) to appear
below. Then I want the second page to print out the remaining three
photos and the data below them. Then it should move on to the next
category and a new page.

The report works fine for anything 15 or below. If it is over, the
way it is set up now, the remaining photos and data just don't print
and the report moves on to the next category.

I see that some categories have even more than 45 records in them.

So I'm looking for a query or a VBA that somehow renames the
Category. If the category is called, let's say, "Tunnel Elastic," and
if it has 48 records in it, I'd want it to return "Tunnel Elastic 1"
for the first 15 records, "Tunnel Elastic 2" for the second 15
records, etc. And these should be sorted by Style/Color.

I hope I am making myself clear. This is a situation where I am
limited to what can fit on a page and need to control the data page by
page. It has a 15 record limit.

Like I said, I could build a chain of queries that figures this out,
using autonumbers, finding the low autonumber, counting, subtracting,
etc., etc., but just thought to ask here first for a better solution.

Thanks,

Matt

 

[Marshall Barton]

The report already works (sort of). I have it set to do the 5 by 3 as
you say.

The problem is that when it is greater that 15, it is a mess. I want
to control this mess.

So let's say there are 18 records in a certain category. I want the
first page for this categoy to show 15 photos and to print out the
data that relates to those photos (selling information) to appear
below. Then I want the second page to print out the remaining three
photos and the data below them. Then it should move on to the next
category and a new page.

The report works fine for anything 15 or below. If it is over, the
way it is set up now, the remaining photos and data just don't print
and the report moves on to the next category.

I see that some categories have even more than 45 records in them.

So I'm looking for a query or a VBA that somehow renames the
Category. If the category is called, let's say, "Tunnel Elastic," and
if it has 48 records in it, I'd want it to return "Tunnel Elastic 1"
for the first 15 records, "Tunnel Elastic 2" for the second 15
records, etc. And these should be sorted by Style/Color.

I hope I am making myself clear. This is a situation where I am
limited to what can fit on a page and need to control the data page by
page. It has a 15 record limit.

Like I said, I could build a chain of queries that figures this out,
using autonumbers, finding the low autonumber, counting, subtracting,
etc., etc., but just thought to ask here first for a better solution.

I don't see how fudging a query can help with the "mess"
when there are more than 15 details. I think you need to
deal with the "mess" before worring about creating some
fudge related to 15 details.

If the Height if the detail section is fixed at 1/3 of the
available page space, the next page should take care of
itself automatically without you doing anything else. OTOH,
maybe I don't have the full story and you are doing
something tricky that I haven;t seen yet.

To get the Category group header to appear on each page, set
its RepeatSection property to Yes.

If you want it to number the pages in the group, you can do
that be adding a (hidden?) text box (named txtLineNum) to
the detail section. Set its control source expression to =1
and RunningSum to Over Group. Then the group header text
box can use an expression like:
=Category & " " & txtLineNum \ 15 + 1

 

[doyle60]

Perhaps we are not communicating. So let's begin again. I'll tell
you what the report is currently and what happens.

The report has the four sections:

1) Collection Code Header
This contains a logo and a text box for the Collection Name on the top
taking up very little vertical space. Below this there is a large
subform containing the photographs. The subform is set to print 5
columns across and it is about 5 inches high on this report, high
enough for three photos tall.

The property sheet for the Collection Header is:

Keep Together: Yes
Can Grow: Yes
CanShrink: No
Repeat Section: No

The Picture sub form has the Can Grow and Can Shrink set to Yes and No
respectively.

2) Detail
The detail section has a thin line of data relating to the style,
color, quantities available and that sort of thing. (It's header is
actually in the section above.)

The properties are:

Keep Together: Yes
Can Grow: Yes
Can Shrink: Yes

3) Style Footer
This is a very thin section just to put a line under each new style.
It's properties are:

Can Grow: No
Can Shrink: No
Keep Together: Yes

4) Collection Code Footer
This section is used just to do a page break. The properties are set
to:

Force New Page: After Section
Keep Together: No
Can Grow: No
Can Shrink: No

When there are 15 or fewer records in a Collection (which is the same
as a Category), all goes well. When there are 16, the picture subform
grows to four tall, which allows only about 4 lines of detail to show
up on the remainder of the page. The other 11 lines of detail do not
print. They do not show up anywhere. They are lost. The report is
only one page long.

(If I am printing one Collection with 20 photos and another collection
with 6 photos, the first page shows the the 20 photos and four lines
of data and the second page shows the 6 photos for the new Collection
and the 6 lines of detail for them. The missing 14 lines of data for
the first collection never prints. It disappears completely. Where
it is, I haven't a clue. I tried setting the properties to the detail
section to all sorts of things but can't get them to show up.)

I assumed that even if I did get the records to show up that Access
wouldn't actually do it as I want it without some fancy query or VBA
code, thus my first email.

If you can tell me how to set the properties, that would be great.
It's a puzzler to me.

Matt

 

[doyle60]

Thanks Marshall, but I'm solving this by doing a make table with an
autonumber and it seems to be much easier than I thought. When the
user hits the print preview, I will just have it go to a table on
their frontends, etc., etc., which I do often enough. Thanks however.

Matt

 

[Marshall Barton]

Thanks Marshall, but I'm solving this by doing a make table with an
autonumber and it seems to be much easier than I thought. When the
user hits the print preview, I will just have it go to a table on
their frontends, etc., etc., which I do often enough. Thanks however.

I really got lost when you said your report has a sub form.
I have never tried to put a form on a report and wouldn't
have a clue about any complications with that arrangement.

Well, I really hate using a temporary table for anything,
but if it somehow solves your problem, so be it.

 

[doyle60]

I mean subreport.

It took me about a half hour to complete this job and another hour to
complete the testing and permissions. I spent more time talking to
you about it than it took me just to do it!

It only took a simple query to find the min Autonumber, and a simple
line of code in another query (that brings in that query) to split the
Collection into sets of 15:

Split: [SpecPhotoOverDataMTtbl].[CollectionCode] & "-" & ([LineID]-
[MinOfLineID])\15

That places either a "-1" or "-2" or "-3" etc. onto the end of a
CollectionCode (which is text).

I then use this to sort but don't show. It works exactly how I want
it to work, no compromises too.

I thought it was going to be more involved.

I guess we can call this methodology a poor man's Ranking.

Matt

 

[Marshall Barton]

I mean subreport.

It took me about a half hour to complete this job and another hour to
complete the testing and permissions. I spent more time talking to
you about it than it took me just to do it!

It only took a simple query to find the min Autonumber, and a simple
line of code in another query (that brings in that query) to split the
Collection into sets of 15:

Split: [SpecPhotoOverDataMTtbl].[CollectionCode] & "-" & ([LineID]-
[MinOfLineID])\15

That places either a "-1" or "-2" or "-3" etc. onto the end of a
CollectionCode (which is text).

I then use this to sort but don't show. It works exactly how I want
it to work, no compromises too.

I thought it was going to be more involved.

I guess we can call this methodology a poor man's Ranking.

Well, relying on an autonumber value is a compromise. There
are no guarantees that an autonumber primary key field will
be anything other than unique. OTOH, a new table populated
by a query is very likely to sequential.

Using a temporary table is also a compromise because it
causes bloat that will require more frequent Compact
operations, which in turn means you need more than a trivial
backup regime.

As long as you can specify a unique sorting for the records,
except for a potential speed issue, a ranking query is the
uncompromising way to go.