Ok. I have created a report and this is what THE FORMAT looks like (or should look like) thus far:

Report Header:
Plans Data From (date() ) to dateadd("yyyy",1,date())

Page Header: NONE

Table Header:
Sub-Title (this should pop up with the three different types, when there are no more records for this sub-title group this will reprint the new sub-title group (next sub-title group) and then the next line This Sub-title should print out QUERY 1, Query 2, or Query 3 depending upon the records coming out from the individual queries. Then,

Name Address Phone Date Expires


This is what I have on the back-end:
I created Query 1 to compile the data for sub-title A
I created a second Query (query 2) to select the data for sub-title B

I created a third Query (query 3) to select the data for sub-title C

Note: There are only 3 sub-titles (no more than 3).
Each one of these individual queries will only grab those records earmarked with a Date Expires from todays date to one year from today. Also, each of these queries were set up with an additional expression called "table" This was to mark those certain records as 1, 2 or 3 (based on the query number above). So for example, all records in each query would look like it had an additional column with a 1 if the record was in query 1, and a 2 if it was from query 2 or a 3 if it was coming from query 3.

I created a fourth query (query 4 - manually written UNION query) to join all three into one place and have all the records collected from each query in ONE query which can be pushed to the report. The select statement for this looks like this:

SELECT [Business Name], [Contact], [Address1], [Address2], [City], [State], [Zip], [Home Phone], [Work Phone], [Date Expires], [Table]
FROM [Certifications Expiring]
UNION SELECT [Business Name], [Contact], [Address1], [Address2], [City], [State], [Zip], [Home Phone], [Work Phone], [Date Expires], [Table]
FROM [Farm Permit Applications Query]
UNION SELECT [Business Name], [Contact], [Address1], [Address2], [City], [State], [Zip], [Home Phone], [Work Phone], [Date Expires], [Table]
FROM [Farms NMP Expiring]
ORDER BY [Date Expires];

(table above is the Query name = 1, 2, 3)


What my report is doing:

Problem 1:

When I go to Layout Preview, it automatically asks me for the Table parameter 1, 2, or 3.

What my report needs to be fixed to do:

When I go to Layout Preview, it automatically just needs to print the report with all the records coming from the Fourth Query (which is collecting data from the first three queries).

Problem 2:

Problem 2 is part of problem 1 I think. The parameter is stopping the next step of grouping the detail records by Query 1, 2, 3. Basically the Table Header is not printing out with the next group of records. It stops. If I type in a parameter like for example "1" (I actually typed on the parameter line without quotes) - it will only type out a Table Header for that Query 1. I want it to type out 1, then the detail records, then another Table Header for Query 2, then the detail records, then the last Table Header for Query 3. If no records match, it will print out no records match in the detail. Stop and go to the next Table Header & Detail lines.

Problem 3:

I have not set it up yet for any records matching. I haven't gotten that far.

Problem 4:

It's currently only printing Query 1 based on the parameter I entered, stops and ends the report. Problem 4 is that it's not even printing out the right set of records based on the date expired as planned.


I know there is an easier way of doing this. I just can't think of what. I thought of creating macros and using some VB scripting to bring it all together. At least that's what I was asked to do in this example. So I am probably taking the long way around.

Any ideas from people on what I can do to make this report come together?

Thanks in advance,
Mainegirl 2009