Query not working

[JulieAsking]

I have a query showing 3 tables. The first table has an autonumber primary key and the other two tables are separately but both related to the first table on this key.

I have the same field in the 2nd and 3rd tables and when I query this field from both these tables I get no results but if I query the field in only the 2nd or only the 3rd table I get correct results.

The 2nd and 3rd tables exist for different hospitals. The main table exists for PatientID and information. In the query I'm pulling down the patient name from the main table and the BodyArea field from the 2nd and 3rd tables.

Can someone suggest why the query will not incorporate the 2nd and 3rd tables at the same time.

Thank you - Jule

 

[Pat Hartman]

Since related records exist only in tbl2 or only in tbl3 but not both, the join types need to be changed to LEFT.

Are you sure that you need to keep the hospital data in separate tables? What happens if you add a third hospital? Will you then need to add another separate table?

 

[JulieAsking]

Thanks Pat. I'm going to try a Union Query on this one. Lot of fast learning curves in Access!

 

[JulieAsking]

The Union Query worked but now I am faced with how to include the name fields from the main table in this query. I understand a Union Query can only be used to combine fields of the same name and data type in different tables so I assume I cannot add more code to this Union Query in order to incorporate fields from the main table which have a different name. What is the trick here please?

Julie

 

[KKilfoil]

If you wish to add fields with different names (but hopefully same data types) to a union query, use the AS operator.

As an example, the SQL for one of my queries is as follows:

SELECT [MonthDrafted] as [TheMonth], [CountDrafted] as [WOCount]
FROM DraftedbyMonth;

UNION SELECT [MonthIn] as [TheMonth], [CountIn] as [WOCount]
FROM InbyMonth;

This creates a query containing two fields, [TheMonth] and [WOCount].

 

[JulieAsking]

Hey KK - thanks for your reply.

I understand a union query can only involve fields from two tables. My union query was successful in pulling down two fields both named BodyArea from two separate tables. What I now want is also to introduce a third table to the query which has a field for LastName. Maybe I have to do 2 separate queries here (one being the union query and the other being the LastName query) and somehow combine them?

Thanks for your thoughts.

Julie

 

[KKilfoil]

Maybe i'm getting confused by your answer. Does your query ONLY return the single field of BodyArea or are there additional Foreign Key (FK) fields relating back to the Primary Key (PK) of the main table?

You will need a way to find the proper record to get the Lastname.

If you DO have such a field, I would create a new query with the union query and the main table as source data. Create a relationship between the FK and PK if not already defined, and then show whatever fields you need.

p.s. I agree with Pat Hartman wrt table design. If the only difference between your two hospital tables is which hospital the record belongs to, then I would modify the table design so that there is only one hospital table, with a new field identifying which of two (or more!) hospitals this record pertains too. Then anything you do will only have to be done once, rather than once per hospital table. This will also eliminate a lot of fooling around with union querys and the like. You can easily add filters to queries later when you want to look at one hospital at a time.

 

[JulieAsking]

Thanks KK.

I take the point in your post script and have decided finally to go that route. It does indeed seem an easier way to travel.

Thanks for taking the time to give your thoughts to my question.

Julie