Creating report using unrelated tables and shared parameters from 1 query

[javajoe128]

I am attempting to create a report that contains 2 subreports from
unrelated tables but that share common field names (can not create
relationships because the data is unrelated). I would like to use
those field names as parameters for the subreports.

Here is an attempt to illustrate:

Subreport A (based on Table A) uses cust# and purchase date to display
information about purchases of certain products.
Subreport B (based on Table B) uses cust# and purchase date to display
information regarding certain types of purchases (different than A
types because information gathered about the purchases are different in
nature)

I would now like to create a report that lists; "Cust#", "date purchase
of A items", "date purchase of B items".

Finally, if possible, I would like to have this information be
available to the user by being prompted for paramters; "Cust#", "Start
purchase date range" and "End Purchase date range"

I have attempted to create a report with 2 subreports and have not been
able to pass the parameters. Attempts to create 2 queries and then
join them on cust# results in a cartesian.

Any help appreciated.
Thanks!

 

[Allen Browne]

If you have a Cartesian Product, your query either:
a) doesn't need the table you added for the critera, or
b) lacks the JOIN between the tables.

Where possible, use the LinkMasterFields/LinkChildFields properties of the
subreport control to limit the subreport. Since [cust#] is present
everywhere, you should be able to name this field in those properties at
least.

If the subreport is per day, you can include the [purchase date] field in
LinkMasterFields/LinkChildFields as well.

If the date is a range, create a little unbound form where you can enter the
dates. Now in each of the queries that needs to be limited by date, enter
criteria such as:
Between [Forms].[Form1].[StartDate] And [Forms].[Form1].[EndDate]
Assuming the form is open, the queries will now read the dates from the
form.

To ensure Access understands these dates correctly, in query desgin view,
choose Parameters on the Query menu, and enter 2 rows in the dialog:
[Forms].[Form1].[StartDate] Date/Time
[Forms].[Form1].[EndDate] Date/Time

The form can have a combo for the [cust#] as well if you wish.

The advantage of the form is that the different queries can read the dates
from the same place, instead of you having to enter them multiple times.