Cartesian Product I Thought I have Prevented

[joesmithf1]

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

 

[boblarson]

you must link ALL applicable fields - SSN, Name, and Type.

Now, that being said, I think it would be wise of you to rename a couple of these fields as they are Access reserved words: Name and Type. If you don't, you will suffer because Access will at some point not like it and act all weird (or not act at all).

 

[joesmithf1]

Thank you, Bob!
What i don't understand is, if I have already created a single NEW table with the combine of DEDPARM1 and DEDETAIL1, why am I still having the Cartesian Product issue? Is Access NOT using the BIWEEKLY_TEMP_TABLE table's data? I am confuse.

Thanks.

Joe

 

[boblarson]

If you don't link all shared fields then it will give you a permutation for each that matches but isn't linked.

 

[joesmithf1]

I think I kinda get what you are saying. I should "LINK" the shared field and not just "GROUP" them! Currently I am just linking the SSN and Type field. I'll try to link the rest of them now. There are about 15 'applicable' fields(SSN, Name, JobTitle, Department, Status, etc.) from each table.
Does this mean I'll have to link all 15? What if there is a 'discrepancy' in the Jobtitle or status between the two tables? Then I wouldn't be able to get all the records that I wanted? This is the reason that I am just linking the Namd and SSN and Type field.

Now there is one thing I have forgotten to mention. The amount from DEDPARM1 represent the EMPLOYER's amount and the DEDETAIL amount represent the EMPLOYEE's amount. This is why I need to combine the two tables; I want to have a query result(Make Table Query) that will put the two amount side-by-side of each other. Does this make sense? Ultimately, the result that I'll need to have is something like this:

BIWEEKLY_TEMP_TABLE results
SSN | Name | Type| EmployerfaeAmount | EmployeefarAmount
555-99-8888 | Joe | FAE | 1000 | 20

 

[joesmithf1]

Ok, i have linked all applicable fileds, but the results still coming up with 2 records per each individual per each Type.

 

[joesmithf1]

Strangly, I figured out! Thanks again, Bob!