comparing tables

yoonie_85
10-13-2004, 11:49 AM
hello

i'm in desperate need of some help, as it is urgent...i have taken a lot of different approaches to this problem, but nothing seems to be working.

here's what i'm trying to do...

I am trying to write a query in access in which I am comparing two tables.

tabel1 has 2 id numbers in the first 2 fields, and a phone number in the 3rd field
table 2 has 2 id numbers, and 3 emtpy fields for phone number, Yes column and No Column.
I have to take each pair of ID numbers fromthe 1st table and check to see if the same pair is in the second table. If so, I want to add the phone number to the 2nd table and inclue yes or no in the corresponding column.

any help would be greatly appreciated. Thank you.

so far i've got this:


UPDATE Original
SET WH = CASE
WHEN EXISTS (SELECT Original.PremCode FROM Original, Data
WHERE Original.PremCode = Data.PREM_CODE AND
Original.CustCode = Data.CUST_CODE)
THEN 'Y'
END,

WH_Rental = CASE
WHEN EXISTS (SELECT Original.PremCode FROM Original, Data
WHERE Original.PremCode = Data.PREM_CODE AND
Original.CustCode = Data.CUST_CODE)
THEN 'N'
END,

WH_Constant = CASE
WHEN EXISTS (SELECT Original.PremCode FROM Original, Data
WHERE Original.PremCode = Data.PREM_CODE AND
Original.CustCode = Data.CUST_CODE)
THEN 'N'
END

couch612
10-13-2004, 11:52 PM
you're using the wrong SQL statement for what you want to do. here's what you should do:

1. go into your Access database and create a query that is based on the first table and the second table. go into Design view of the query and create a join between the first field in the first table and the second table. also create a join between the second field in the first table and the second table.

2. using the menu, change the query type to an Update query.

3. add the phone field from the second table that you want to update to the query output fields, and in the Update To space, type in Table1.[Phone Number]

4. go into SQL view and this will show you the required SQL statement to perform the update.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum