Query to select all records from two tables without dups MS Access

[amannella]

Good Afternoon,

I am trying to create a query to return all records from 2 tables. Here's the scenario:

Table 1 is card Time Cards Table - the PK is Time Cards ID.
Table 2 is called Time card Hours - PK Time Card Hours Id the foreign key is Time Cards ID
Table 3 is called Time Card Expenses - PK is Time Cards Expense ID - the foreign key is Time Cards ID

What I am trying to do is create one query which will give me the fields from Time Card Hours and Time Card Expense where the Time Card ID is the common denominator.



When I try the various joins it give me duplicate records so instead of an exact count of say 2000 recocrds from time card hours and 2000 records from time card expeses (4,00 records in the query as a total) I'm getting say 11,000 or more records which tells me there are many dups.

What I'm looking for is a result set that combines both time card hours and time card expenses by time card id showing all the fields from both tables with 4,000 reocrds instead of say 11,000 records. .

So, one record in the results set would have (or may not have) time card hours and/or time card expenses. Depending upon if hours and/or expenses were associated with that time card id. I know how many reords there are in the time card hours table and how many records there are in the time card expense table, so the sum of reords in this query I'm trying to create should be the total sum of records from both time card hours and time card expense. One line if there was a record for time card hours and one record from time card expense (if there was one)

Any help would be appreciated

[June7]

Can't join all 3 tables in one query unless relationship of TimeCards is 1 to 1 to each of the other two tables.

You can UNION the two tables to result in 4,000 records. There is no wizard or designer for UNION, must type in SQL View window. Example:

SELECT EmpID As Code, TimeCardsID, "Hours" As TransType, Hours As TransValue FROM [Time Card Hours]
UNION ALL SELECT AccountID, TimeCardsID, "Expense", Expense FROM [Time Card Expenses];

[amannella]

Thanks, I thought it would have to be a Union All query, but wanted to make sure I wasen't missing anything.