Access 07 - Confused while trying to create a report

  • Thread starter Thread starter D0MZE
  • Start date Start date
D

D0MZE

Hey guys,
Been a while since i've posted here. Things have changed since! hehe

Well i'm using Access 2007 and i'm trying to do the following

I have a general query that feeds from a table (in that table there's
a uniqueID for every row) - Lets call it Query1
I have 3 other queries that "feed off" that query that is, the
uniqueID is used in the WHERE clause. - Lets call them Query2, Query3,
Query4

How can I create a report so that I have Query1 as my main, and the
uniqueID of that query is passed to Query2, Query3 and Query4 as a
"sublevel" of Query1?

Note: Query2, Query3 and Query4 return more than 1 rows hence why I
cannot process it as JOINS.. i wouldnt want to have Record1 Appearing
X number of times :P

Thanks
 
D0MZE said:
Hey guys,
Been a while since i've posted here. Things have changed since! hehe

Well i'm using Access 2007 and i'm trying to do the following

I have a general query that feeds from a table (in that table there's
a uniqueID for every row) - Lets call it Query1
I have 3 other queries that "feed off" that query that is, the
uniqueID is used in the WHERE clause. - Lets call them Query2, Query3,
Query4

How can I create a report so that I have Query1 as my main, and the
uniqueID of that query is passed to Query2, Query3 and Query4 as a
"sublevel" of Query1?

Note: Query2, Query3 and Query4 return more than 1 rows hence why I
cannot process it as JOINS.. i wouldnt want to have Record1 Appearing
X number of times :P

Thanks
Click to expand...

Seems to me to be a classic Report/Subreport situation (three
subreports) where you have (three) one-to-many relationships.

Create your tables so that each "dependent" table is linked to the
"master" by a key field, and create a query which joins with the master.
(It helps to use the same field name in both tables). Then if you use
the report wizard to build a report based on each joining query, Access
will spot the relationship and offer you exactly what (I think) you want
with almost no work on your part. The "uniqueID" (or join field)
needn't be shown in the subreports.

There are examples of this in the Northwind sample database (on the Help
menu in Acc2003, unless not installed). Worth a look. For a simple
example look at the "categories" form (the principles are the same for
subreports/subforms).

I have an invoicing report which shows a few details of the current
customer, and has a subreport showing their unpaid invoices, another
showing goods & services supplied, another showing "hourly" work done
and a fourth showing payments received, all totalled up into a "sum
outstanding" via controls in the subreport footers.

Phil, London
 
Back
Top