(Msg. 1) Posted: Thu Jul 24, 2008 10:47 am
Post subject: Multiple reports on one Archived from groups: microsoft>public>access>gettingstarted (more info?)
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.
(Msg. 2) Posted: Thu Jul 24, 2008 11:28 am
Post subject: RE: Multiple reports on one [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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.
"jez_s@yahoo.com" wrote:
> 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
>
(Msg. 3) Posted: Thu Jul 24, 2008 1:08 pm
Post subject: Re: Multiple reports on one [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
On Thu, 24 Jul 2008 10:47:43 -0700 (PDT), jez_s.TakeThisOut@yahoo.com wrote:
>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).
(Msg. 4) Posted: Thu Jul 24, 2008 5:09 pm
Post subject: Re: Multiple reports on one [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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.
(Msg. 5) Posted: Thu Jul 24, 2008 7:50 pm
Post subject: Re: Multiple reports on one [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
>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.
(Msg. 6) Posted: Fri Jul 25, 2008 8:48 am
Post subject: Re: Multiple reports on one [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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.
(Msg. 7) Posted: Fri Jul 25, 2008 2:42 pm
Post subject: Re: Multiple reports on one [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
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!
(Msg. 8) Posted: Sat Jul 26, 2008 5:36 pm
Post subject: Re: Multiple reports on one [Login to view extended thread Info.] Archived from groups: per prev. post (more info?)
On Fri, 25 Jul 2008 14:42:18 -0700 (PDT), jez_s.RemoveThis@yahoo.com wrote:
>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.
All times are: Eastern Time (US & Canada) (change)
Page 1 of 1
You can post new topics in this forum You can reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
Home
Forums
Home
FAQ
Profile
Private Messages
Log in
Office Other