Please said:
I have to create a report that show children added each month on top and
children dropped each month on bottom. I did this by grouping on status:
works fine. however I need the page to display 10 rows for added and 10 rows
for dropped. Did this with Duane Hookums help. Problem if there are more
than 10 records for either added or dropped I need to a new page identical to
the first with 10 rows for added at top with or with data and 10 rows at
bottom for dropped with or without data. Example if I have 14 dropped I need
a new page with 10 blank rows at top and 10 rows at bottom filled with 4
records.
This is a mandatory form that must look identical to the original
Excuse me, but I think Larry's response in your other post
of this question was very appropriate. This arrangement of
data is a ridiculous requirement and trying to meet it needs
a very messy combination of tables/queries/calculations.
Even if you are an advanced programmer and you go to all
that trouble, the report will be very "delicate" (i.e.
almost any change in the report's requirements will almost
certainly force you to throw away most of that work and
start over).
If you feel that you absolutely must pursue this whimsical
objective, we can not create all this for you. We can
provide some general ideas, but you will have to work
through the details as they tie into your specific data
structure.
First you will need a table with a single field that
contains the number from 0 to more than the maximum children
in the report.
Next create a query that selects the added children data for
the report. This query must include fields that can be used
to uniquely sort the records so you can use a calculated
field that uses a subquery to sequentially number the
records.
Repeat that with a second query for the dropped children.
Then you need to create a UNION query with two SELECT
queries. The first outer joins the number table to the
added children query on the calculated sequential number
field and the second joins with the dropped children query.
Once all that is in place, the report can use Sorting and
Grouping to group on the integer part of the number field
divided by 10. The second level of grouping will be on the
added/dropped field.
The above omits a fair number of details that you will need
to work out or you can come back here with detailed specific
questions that do not require a dissertation to answer.
OTOH, if have no idea what I'm talking about above, you
should consider trying to find an advanced Access programmer
that you can pay to deal with this mess.
