Creating a report

[Trevor Ryhorchuk]

Hello - I have designed my first database and filled it full of useful data -
if I can generate the report I have drawn on paper!

I will describe my database structure then I will describe the report I am
trying to build then I will describe what I am able to and not able to
accomplish:

Household table linked one-to-many to client table linked one-to-many to
accounts table which has a one-to-many link to an order1 table and a
one-to-many link to an order2 table. The order1 and order2 tables are
different because they hold different types of orders.

The report I would like to build is as follows:
Household
Client1
Account1
order1-1
order1-2
order2-1
order2-2
Account2
Client2
Account1

The problem I am running into is that I want the report to show for
households even if the client accounts do not have any corresponding
information in the order1 and order2 tables - what I am finding is that I can
only generate a report that includes those households and clients whose
accounts have information in the order1 or order2 table.

I have tried creating a new table thru a query but again it only generates
rows for households/clients/accounts that have information in both the order1
and order2 tables.

I hope this is understandable - I have never made it this far before so
please be gentle on me but if you need more details I will be happy to
attempt to provide them.

Thanks,

TDR

 

[Douglas J. Steele]

Create a query that UNIONS together a subquery that joins Household to
Accounts to Order1 and one that joins Household to Accounts to Order2. Use
that query as the record source for the report.

 

[Trevor Ryhorchuk]

Thanks for the quick reply Doug - I have scoured the help files to try and
determine what it is you suggested I do - and I believe I have made a good
start - the bad news is my union query only has one field in it so far - the
good news is that it is a start and I will play with it for awhile.

TDR

 

[Jerry Porter]

Trevor,

Another option is to review your structure and see if you really need
to have two separate Orders tables. If you could figure a way to make
one table handle the needs of your different types of orders, it will
make your life easier.

Jerry

 

[Trevor Ryhorchuk]

Thanks Jerry - I've been sitting here scratching my head for about an hour
and the only thing I've managed to create is a bald spot.

I'm afraid what I want to accomplish will not be possible because my problem
is not as simple as what I have previously given. The two 'order' tables I
am talking about are completely different animals, the only similarity is the
account number, so If I were to have the two tables as one the design of my
database would not be efficient - that's just the start of it, I used two
'order' tables just to get my head around what I could do to solve my
problem, but reality is I have 7 different tables with information that is
completely different from each other. I basically want to be able to create
a report for a household that summarizes all the information that I have for
the household in the seven different tables - also, three of the tables
relate at the client level and four of the tables relate at the account
level. - updating the information in the seven different tables through forms
is coming along fine, it's this summary report that I can't seem to move
forward with.

I am open to any other possibilities you may suggest - thanks - tdr

 

[Trias]

Hi Trevor,

you probably need to create query for your report's sourcedata. In the query
you will use sql syntax like "Left (or Right) Join" to force the query to
show all data. Here is what i have i mind.

Let's say i have 5 tables: HH (household), A (Account), C (Client), O1
(Order1) and O2 (Order2). The structure of each table looks like this:

tbl H: H_ID (key), H_description
tbl C: C_ID (key), H_ID, C_description
tbl A: A_ID (key), C_ID, A_description
tbl O1: O1_ID (key), A_ID, O1_description
tbl O2: O2_ID (key), A_ID, O2_description

There 5 querys (q1, q2, q3, q4 and q5) that i create. For each query the sql
syntax looks like this:

Q1: SELECT A.A_ID, A.CID, A_description, O1_description FROM A LEFT JOIN O1
ON A.A_ID = O1.A_ID;

Q2: SELECT A.A_ID, A.CID, A_description, O2_description FROM A LEFT JOIN O2
ON A.A_ID = O2.A_ID;

Q3: SELECT CID, A_description, O1_description AS O_description FROM q1
UNION SELECT CID, A_description, O2_description AS O_description FROM q2;

Q4: SELECT C.C_ID, C.H_ID, C_description, A_description, q3.O_description
FROM C LEFT JOIN q3 ON C.C_ID = q3.C_ID;

Q5: SELECT H.H_ID, H_description, q4.C_description, q4.A_description,
q4.O_description FROM H LEFT JOIN q4 ON H.H_ID = q4.H_ID;

Using "Left Join" in the query will force data to be shown from one table
(parent) event if there is no related data on the other table (child). So,
the results of query Q5 will look like this:

H_ID H_description C_description A_description
O_description
1 h_a h_a_c1 h_a_c1_a1
h_a_c1_a1_o1
1 h_a h_a_c1 h_a_c1_a1
h_a_c1_a1_o2
1 h_a h_a_c1 h_a_c1_a2
h_a_c1_a2_o2
1 h_a h_a_c2 h_a_c2_a1
h_a_c2_a1_o1
1 h_a h_a_c3 h_a_c3_a1
1 h_a h_a_c4 h_a_c4_a1
1 h_a h_a_c5
2 h2_a h2_a_c3
2 h2_a h2_a_c4
3 h3_a
4 h4_a

As last step i used Query Q5 as the sourcedata for the report.

HTH

 

[Trevor Ryhorchuk]

Thank you Trias - I think I understand what you are saying and I'm going to
give it a try.

Regards,

TDR

 

[·¶æÃæÃ]

Thanks Jerry - I've been sitting here scratching my head for about an hour
and the only thing I've managed to create is a bald spot.

I'm afraid what I want to accomplish will not be possible because my problem
is not as simple as what I have previously given. The two 'order' tables I
am talking about are completely different animals, the only similarity is the
account number, so If I were to have the two tables as one the design of my
database would not be efficient - that's just the start of it, I used two
'order' tables just to get my head around what I could do to solve my
problem, but reality is I have 7 different tables with information that is
completely different from each other. I basically want to be able to create
a report for a household that summarizes all the information that I have for
the household in the seven different tables - also, three of the tables
relate at the client level and four of the tables relate at the account
level. - updating the information in the seven different tables through forms
is coming along fine, it's this summary report that I can't seem to move
forward with.

I am open to any other possibilities you may suggest - thanks - tdr