Linking tables in a query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Radhika - 17 Jan 2008 19:23 GMT

I have a database that contains information on a certain number of
individuals during different session. Table 1 'tbl_Tracking_Form1' has
information on sessions 2,4 and 6. Table 2 'tbl_Tracking_Form2' has
information on sessions 1,3 and 5. Table 3 'tbl_General_Info' has demographic
information on all the individuals (those who will eventually attentd
sessions 1,2,3,5,5 and 6). I need to create two reports. One (Report1)
should display all the information on the individuals during session 2, 4 and
6 and another(Report2) that displays all the information on the individuals
during session 1,2,3,4,5 and 6. I created Report 1 for sessions 2, 4 and 6
using a query that linked table 1 and table 3 by each individuals unique ID
#. I am having trouble creating Report2. I tried linking all table 1 and
table 2 to table 3 by each individuals unique ID#, but got different
combinations of session numbers, which is not what i need. How can i link the
tables in a query to create a report that includes information on all
sessions. Thankyou!

Reply to this Message

Jerry Whittle - 17 Jan 2008 19:51 GMT

The biggest problem facing you is that the cart is before the horse. It looks
as if you thought about the input forms first then the tables. You shouldn't
have 'tbl_Tracking_Form1' or 'tbl_Tracking_Form2' tables. Rather you should
have one table with therequired data in it. Then you also may need another
table (or more) for the various sessions.

You tried to join 'tbl_Tracking_Form1' and 'tbl_Tracking_Form2' ; however,
there may be some individual ID# in 'tbl_Tracking_Form1' not matching in
'tbl_Tracking_Form2'. In this case you need a Left Join. But then there may
be some individual ID# in 'tbl_Tracking_Form2' not matching in
'tbl_Tracking_Form1'. In that case you would need a Right Join. The big
problem happens when you need both the Right and Left joins in the same
query. This is called a full Outer Join and Access does NOT support them.
Therefore you will need to create a query with the Left join and another
query with the Right join then combine their SQL statements in a UNION query
to 'hopefully' get the results you require.

Signature

Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

> I have a database that contains information on a certain number of
> individuals during different session. Table 1 'tbl_Tracking_Form1' has
[quoted text clipped - 11 lines]
> tables in a query to create a report that includes information on all
> sessions. Thankyou!

Reply to this Message