Multiple reports on one

[jez_s]

Hi all you Access gurus!

I have two unrelated tables and I'd like to generate a report that
shows info about both of them.

For example, let's say Table 1 is about meetings with partners and has
fields like PartnerID & MeetingDate.

Table 2 is about customers and has fields like CustName
DateFirstOrder.

I'd like the report to look something like this:

PartnerID MeetingDate
1234 01/01/01
5678 02/02/02

*******************************

CustName DateFirstOrder
J. Smith 03/03/03
R. Brown 04/04/04

I combined the two tables in a query and based the report on that
query. I was able to get it to work, but only if I listed all the
fields in one line across the top:

PartnerID MeetingDate CustName DateFirstOrder

But I don't want it like that because it doesn't make sense to have
them right next to each other since they're totally unrelated. And
also I have way too many fields to fit nicely across on one row.

I don't know too much about Access and I don't know VBA at all so I'm
working off the point-and-click design view and the report wizard.

If anyone has any advice on how to get this to work I would be very
grateful.

Thanks

-SJ

 

[Golfinray]

You might try using a mainform and subform. Table 1 would be the mainform and
table 2 the subform. They would need to be linked on something common. You
could put a common field in your tables, maybe an ID of some sort.

 

[John W. Vinson/MVP]

Hi all you Access gurus!

I have two unrelated tables and I'd like to generate a report that
shows info about both of them.

Try using a Report (unbound) with two Subreports (one bound to each
table).

 

[jez_s]

Hi to both of you. Thank you so much for your prompt feedback! The
Subreports are working great for the most part. I still have one
issue though.

The report is meant to be used monthly or quarterly, so have a
switchboard item that opens a little form asking the user to enter the
start and end dates they want. Clicking a command button from this
form opens the report.

Each Subreport is based on an unrelated table - as I mentioned earlier
- but both have date fields. I made a separate query for each
Subreport and set the criteria for the date field in each to limit
records shown to whatever the user specified for start and end dates.

The Subreports work nicely on their own...but when combined on the
same main form, it asks for the start and end dates twice.

Any thoughts on how to overcome this problem?

Thanks again

-SJ

 

[John W. Vinson/MVP]

Hi to both of you. Thank you so much for your prompt feedback! The
Subreports are working great for the most part. I still have one
issue though.

The report is meant to be used monthly or quarterly, so have a
switchboard item that opens a little form asking the user to enter the
start and end dates they want. Clicking a command button from this
form opens the report.

Each Subreport is based on an unrelated table - as I mentioned earlier
- but both have date fields. I made a separate query for each
Subreport and set the criteria for the date field in each to limit
records shown to whatever the user specified for start and end dates.

The Subreports work nicely on their own...but when combined on the
same main form, it asks for the start and end dates twice.

Any thoughts on how to overcome this problem?

Thanks again

-SJ

Simplest is to use a little Form to collect the date information. Call
it frmCrit, say, with textboxes txtFrom and txtTo.

Use a criterion like

= Forms!frmCrit!txtFrom AND < DateAdd("d", 1, Forms!frmCrit!txtTo)

as criteria (the dateadd bit catches records with a time portion on
the last day of the range). Put a command button on frmCrit to open
the report and you won't get any prompts at all.

 

[jez_s]

Hi John,

Where do I put the criterion

= Forms!frmCrit!txtFrom AND < DateAdd("d", 1, Forms!frmCrit!txtTo)

I do have form as you described with text boses and a command button
that opens the report...but I didn't see where to put criteria like
that.

Thanks again for your help

-SJ

 

[jez_s]

Thanks again for the feedback! I did use this method and I put the
criteria in the filter section of each subreport. That seemed to do
the trick. I appreciate all the help as I'm finding Access to be
quite a steep learning curve!

-SJ

 

[John W. Vinson/MVP]

Thanks again for the feedback! I did use this method and I put the
criteria in the filter section of each subreport. That seemed to do
the trick. I appreciate all the help as I'm finding Access to be
quite a steep learning curve!

-SJ

The filter is one way, but a more efficient one would be to base the
Report on a Query using the form reference (in brackets) as a
criterion.