Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Access SQL Query

Posted on 2009-04-23
4
Medium Priority
?
196 Views
Last Modified: 2013-11-28
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

0
Comment
Question by:lankapala
  • 2
4 Comments
 
LVL 6

Expert Comment

by:RPCIT
ID: 24219972
is this what your looking for?
UPDATE TableName SET NewNumber = Number WHERE Number = System_ID
0
 
LVL 2

Author Comment

by:lankapala
ID: 24222135
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
0
 
LVL 2

Author Comment

by:lankapala
ID: 24222212
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?
0
 
LVL 41

Accepted Solution

by:
Sharath earned 2000 total points
ID: 24223107
Do you want to UPDATE the same table? For display purpose, you can try like this.

SELECT * FROM Table1;
 
System_Id	Address		Number	New Number
6789		61 Galle		
7890		61 Galle		
12345		72 Mata		
7890		72 Mata		
2343		Kalaa		
1232		maara		
2343		kalaa		
12345		check		
4321		72 Mata		4321	
8907		61 Galle	8907	
9876		kalaa		9876	
6734		check		9876	
 
 
SELECT t2.[System_Id], 
       t2.[Address], 
       t2.[Number], 
       (SELECT MAX(t1.[Number]) FROM Table1 t1 WHERE t1.[Address] = t2.[Address]) AS [New Number]
  FROM Table1 t2;
 
(or)
 
SELECT t2.[System_Id], 
       t2.[Address], 
       t2.[Number], 
       t1.[New Number]
  FROM Table1 t2,(SELECT [Address],MAX([Number]) as [New Number]FROM Table1 GROUP BY [Address]) as t1
 WHERE t1.[Address] = t2.[Address];
 
System_Id	Address		Number	New Number
6789		61 Galle		8907
7890		61 Galle		8907
8907		61 Galle	8907	8907
12345		72 Mata			4321
4321		72 Mata		4321	4321
7890		72 Mata			4321
2343		Kalaa			9876
1232		maara		
2343		kalaa			9876
9876		kalaa		9876	9876
12345		check			9876
6734		check		9876	9876

Open in new window

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question