Merging Two Data Table

[scgoodman]

Trying to merge two data tables. I have two unique fields. Master Waybill and House Waybill. These will be the same across both files. When I do a simple match, I get back over 100K records. This is 5x the amount of data I have in my tables.

Tbl1 = 21178 records
Tbl2 = 18981 records

How do I get these files to merge?

 

[jal]

Trying to merge two data tables. I have two unique fields. Master Waybill and House Waybill. These will be the same across both files. When I do a simple match, I get back over 100K records. This is 5x the amount of data I have in my tables.

Tbl1 = 21178 records
Tbl2 = 18981 records

How do I get these files to merge?

I don't know that you mean by "simple match".

Can you try this -


SELECT * FROM tbl1
UNION
SELECT * FROM tbl2

 

[MSAccessRookie]

Trying to merge two data tables. I have two unique fields. Master Waybill and House Waybill. These will be the same across both files. When I do a simple match, I get back over 100K records. This is 5x the amount of data I have in my tables.

Tbl1 = 21178 records
Tbl2 = 18981 records

How do I get these files to merge?

How did you do the simple match?

If I do

Select count(*) from
MasterTable LEFT JOIN HouseTable
ON MasterTable.Waybill=HouseTable.Waybill

I get 91,810,999 rows using a sample table that I already had and just added rows until I got the right amount.

It looks like I (and probably you as well) have a classic Cartesian query problem. The join is not specific enough. If you need any help in figuring out where the Cartesian loop may be, then post the structure of your two tables (Columns and types). The Database itself may not be needed

 

[scgoodman]

Reply

Simple match, sorry should have used a better term Simple one-to-one query. When I do this, it pulls back 5x the amount of records. I have two tables that I need the data pulled back for both and the unique fields are MAster waybill and house waybill.

Thoughts?

 

[MSAccessRookie]

I think that Jal's suggestion (see below) should get you started. It will remove duplicates during the merge. If you want to retain the duplicates, then make it UNION ALL instead of UNION


SELECT * FROM tbl1
UNION
SELECT * FROM tbl2

 

[scgoodman]

Attached is the table structure

Two tabs with my data elements in the attached spreadsheet. I need all the information from both files into one table/query. Again, the constants are master waybill and house waybill.

 

[scgoodman]

Question?

Where do I put the select statement?

 

[MSAccessRookie]

Where do I put the select statement?

I am not sure what you mean, but if you want to store the results of the UNION query, something like this might be OK

Insert Into tbl3
SELECT * FROM tbl1 (The Table with 21178 records)
UNION
SELECT * FROM tbl2 (The Table with 18981 records)

Remember that we do not know your table names and you will need to substitute as required

 

[neileg]

You haven't attached the spread sheet!

However, I don't think you have a Cartesian product. When you join two tables in a select query witha join, you get a record returned for every combination valid combination of the join data. So if you have two occurances of 1234 in the master waybill table and three occurances of 1234 in the house waybill table, when you join them you will get 6 records. Is this not what you want?