Cartesian Product I Thought I Have Prevented

Hi All,

This post is a bit long, but it is actually really simple. I have two tables(DEDPARM1 and DEDETAIL1). I ran a “CREATE TABLE” query using these two tables, to create a NEW table called “Biweekly_Temp_Table.”

DEDPARM1 sample data
SSN |Name |Type |Amount
555-99-8888 |Joe |FAE |2000
555-99-8888 |Joe |FAR |20


DEDETAIL1 sample data
SSN | Name | Type | Amount
555-99-8888 | Joe | FAE | 1000
555-99-8888 | Joe | FAR | 20

BIWEEKLY_TEMP_TABLE results
SSN | Name | Type| faeAmount | farAmount
555-99-8888 | Joe | FAE | 1000 | Null
555-99-8888 | Joe | FAR | Null | 20
This result makes sense, b/c it is what I wanted.

Now here is the problem. I ran another query(qrySingleRecord) against the BIWEEKLY_TEMP_TABLE to combine the TWO records into ONE. It works, BUT the faeAmount will equal 2000 and the farAmount equals 40. Why is Access doing this? I know something called Cartesian Product will occur if I query against both DEDPARM1 and DEDETAIL1. However, in this case I am NOT querying against these two tables; the whole purpose of creating a new table(BIWEEKLY_TEMP_TABLE) is to try to prevent Cartesian Product. Please help.

Thank you!

Joe