asked on

Access SQL Query

i hava table Called Address. i need below data to split
System_ID      Address      Number      NewNumber
6789      61 Galle
7890      61 Galle
8907      61 Galle      8907
12345      72 Mata
4321      72 Mata      4321
7890      72 Mata
2343      kalaa
1232      maara
2343      kalaa
9876      kalaa      9876
1245      check
6734      check      9876
I need to insert if address is euqal new number will change according to NUMBER field. for example under address 61 Galle road has 3 data. i need to insert NEWNUMBER field 8907.
Output should be
System_ID      Address      Number      NewNumber
6789      61 Galle            8907
7890      61 Galle            8907
8907      61 Galle      8907      8907

i can't do it manualy its has more than 60,000 records.thx

RPCIT

is this what your looking for?
UPDATE TableName SET NewNumber = Number WHERE Number = System_ID

lankapala

ASKER

no i need if address is equal then all the new numbers will be equal to like below example
System_ID Address Number NewNumber
6789 61 Galle 8907
7890 61 Galle 8907
8907 61 Galle 8907 8907

lankapala

ASKER

i try to use same copy Table1 and table2 same copy .please find below my coding

UPDATE Table3,Table4 SET Table3.NewNumber = Table4.Number Where Table3.Address=Table4.Address
But its also not working. its working half. its hsowing 32 records update but actualy i have only 12 records to update.confusing. i fell my logic is correct but not working.
CAN ANU ONE HELP ME WHERE I DID MISTAKE?

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

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

Start Free Trial