asked on

combine tables into one where 2 fields are equal

I have alot of tables that i would like to combine into 1 big table. most of the tables have different amounts of records and may possibly share some field names.

there are two fields in every table where data should be on the same record. they are [LotID] and [WaferID]. if those fields match then i want the data in the table to be put on that record.

what would be the best way to get ALL records into one table and combine data where those two fields are equal?

lausz

Try this

select a.field1, a.field2, b.field1, b.field2
from table1 a , table2 b
where a.lotid = b.lotid and a.waferid = b.waferid

nico5038

You'll have to start with creating tblBig having all unique fields from the different tables.
Next use an INSERT where you add one table with [LotID] and [WaferID] and the additional fields.
For all next tables you'll need two queries:
1) An UPDATE when the [LotID] and [WaferID] match an entry
2) An INSERT when the [LotID] and [WaferID] don't exist yet.
The latter can be detected using a so-called "outerjoin". In the graphical editor just double-click the koin lines between [LotID] and [WaferID] and select the table to be appended to be the "master" (option 2 or 3).
When setting the [LotID] and [WaferID] from the tblBig to have the criteria "Is Null" (without quotes), then you have the rows that need to be appended.

Getting the idea ?

Nic;o)

lightcross

ASKER

i would like to avoid recreating the unique fields because there are too many.

lightcross

ASKER

In my example the new table created would have a total of 14 records. the id's from original tables are irrelevant because they were all created during import from excel sheets. As I said earlier the two matching id's would be "LotID" and "WaferID". Notice the identical Field names for Average, Lowerlimit and upperlimit. would I have to change this in the tables before doing anything so it wont freak out, because these fields should be recorded individually for each table and I would also have to know what it is related. in this case, either tblWAFBoron or tblWAFPhos.

tblWAFBoron:

ID      Date      LotID      WaferID      Average      Lowerlimit      Upperlimit
1      2/11/2003      TP-020303A      DBJ      4.766      3.7      5.3
2      2/11/2003      TP-020303A      DJS      4.766      3.7      5.3
3      2/11/2003      TP-020303A      DD3      4.766      3.7      5.3
4      2/11/2003      TP-020303A      DEB      4.766      3.7      5.3
5      2/11/2003      TP-020303A      DBP      4.766      3.7      5.3
6      2/11/2003      TP-020303A      DL1      4.766      3.7      5.3
7      2/18/2003      TP-020303B      DJX      4.862      3.7      5.3
8      2/18/2003      TP-020303B      DBN      4.862      3.7      5.3
9      2/18/2003      TP-020303B      DBK      4.862      3.7      5.3
10      2/18/2003      TP-020303B      DBM      4.862      3.7      5.3

tblWAFPhos:
ID      Date      LotID      WaferID      Average      Lowerlimit      Upperlimit
1      2/11/2003      TP-020303A      DBJ      6.61      4.94      6.94
2      2/11/2003      TP-020303A      DJS      6.61      4.94      6.94
3      2/11/2003      TP-020303A      DD3      6.61      4.94      6.94
4      2/11/2003      TP-020303A      DEB      6.61      4.94      6.94
5      2/11/2003      TP-020303A      DBP      6.61      4.94      6.94
6      2/11/2003      TP-020303A      DL1      6.61      4.94      6.94
7      2/18/2003      TP-020303B      EC8      4.55      4.94      6.94
8      2/18/2003      TP-020303B      DDW      4.55      4.94      6.94
9      3/17/2003      TP-030203A      DYO      6.68      4.94      6.94
10      3/17/2003      TP-030203A      DYH      6.68      4.94      6.94

nico5038

For these table you could create just one table with the tablename added like:

TableFrom Date LotID WaferID Average Lowerlimit Upperlimit
tblWAFPhos 2/11/2003 TP-020303A DBJ 6.61 4.94 6.94
tblWAFBoron 2/11/2003 TP-020303A DBJ 4.766 3.7 5.3
etc..

Nic;o)

lightcross

ASKER

Nico,
Good idea, but i need this to be all on one line (record). We are creating a database from what was previously recorded on excel sheets. I just finished fixing the fieldnames in ALL the tables so they are unique with the exception of LotID and WaferID.

Thanks again,
VIC

nico5038

All you needed was to create the tblBig with the fields.
In the append query you can direct the fields from the original table to the proper field of the tblBig.

Nic;o)

lightcross

ASKER

is it possible to give me some code?

ASKER CERTIFIED SOLUTION

nico5038

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

lightcross

ASKER

i cant get the querys to work though. to update the fields where the lotid and waferid are equal. and also the append if there is not a matching lotid and waferid.

nico5038

Then drop it in my mailbox and I'll have a look. (See my profile for the address)

Nic;o)

lightcross

ASKER

message sent to your email. thanks

lightcross

ASKER

sorry all, so far none of these options seem to be working.

Frédéric Métraux

----------------------------------------------------------------------------------------
This question has been abandoned and needs to be finalized.
You can accept an answer, split the points, or get a refund (information at http:/help.jsp#hs5)
If you need a moderator to help you, post a question at Community Support (http:/Community_Support/)

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

ornicar
Cleanup Volunteer

---------------------------------------------------------------------------------------------

jadedata

No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: nico5038 {http:#9798952}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

jadedata
EE Cleanup Volunteer

jadedata

All the information is in there for the solution...