Solved

Access and SQL query

Posted on 2009-04-13
6
229 Views
Last Modified: 2013-11-29
i wrote a programme clear all the uncleare address to clear addresses. But i stuck one SQL problem. The data is below
IdLocation      Name      Address                      PC                                      newLocation-ID
45678      Dr.Jeffery      "61,Staffordshare"      NE87RT      
45899      Stevan       "61,Staffordshare"      NE87RT      
56789      Jeffery      "45,Stanton Street"      RT68Y      
67899      Henry      "45,Stanton Street"      RT68Y      
36789      Kornar      "45,Stanton Street"      SE23RT      
46789      Rutherford      "78,Virtusa"                          FER234TY      
47890      Segal      "78,Virtusa Road"                           FER234TY      
88845      Peter      "79,Sommerfield"      RT5TYS      

i have more than one million reccords like that. i need get if 1 ,2,3.. address are eqaul , the first address id location going to be insert into  newlocation-ID field.

can u help me to solve this above issue. i can't do manually becuase huge database with more tahn 1 million records.
Any sQL ideas?
Iam doing with MS Access 2003
0
Comment
Question by:lankapala
  • 3
  • 2
6 Comments
 
LVL 12

Accepted Solution

by:
koutny earned 500 total points
ID: 24128938
A million of records in an Access db is quite a lot. I wonder what the performance must be like...

But to answer your immediate question, a query like the one below should work. Even though again, it will take a long time to execute such a query. Do make a back up of the db before you run this.

To get the query working you would need to replace tbl_name with the actual name of the table.
Also, the query looks only for total matches between records. Ie. all 3 fields must be equal: Name, Address, and PC. I am sure you can modify it easily if you only want to match the address and postcode for example.


UPDATE tbl_name T1 SET [newlocation-ID] = DMin("IdLocation","tbl_name","Name = '" & Name & "' AND Address = '" & Address & "' AND PC = '" & PC & "'")
WHERE EXISTS (SELECT T2.IdLocation FROM tbl_name T2 WHERE T1.Name = T2.Name AND T1.Address = T2.Address AND T1.PC = T2.PC AND T1.IdLocation <> T2.IdLocation)

Open in new window

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 24129986
koutny said:
>>A million of records in an Access db is quite a lot. I wonder what the performance must be like...

Not sure I would agree with you there.  A well-designed Access database can run just fine with millions of
records, and a poorly-designed Access database can perform like an arthritic tortoise with just a few tens
of thousands of rows.

The size of the database has less to do with the performance than other, more important factors such as
fitness of design, proper indexing, and network speed.
0
 

Author Comment

by:lankapala
ID: 24132151
koutny: your are great. great work,
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

Author Closing Comment

by:lankapala
ID: 31569438
great work thx
0
 

Author Comment

by:lankapala
ID: 24132175
hi, most of the coding is working properly , if address are some time equal , but some are partly equal on this time its possible tio use % ?
0
 
LVL 12

Expert Comment

by:koutny
ID: 24140324
Hi lankapala,
It would be certainly possible to use a LIKE condition (for which the wildcard is the * character in Access).
But it gets a bit tricky in terms of how to do the comparison. For example should it be a match if eg. the address in one record is fully contained within the address of another record?
Such as
"78,Virtusa" and
"78,Virtusa Road"  

That could be handled using the * wildcard at the end. I suppose that you wouldn't use it at the beginning because "178, Virtusa" and "78, Virtusa" are two different addresses.

So try the modified query below. I have only updated it for the address field, I think you should be able to play with it some more on your own.
However, I have to say that getting rid of duplicates might be tricky and you might have to write some VBA code for this - maybe a simple query won't be enough.
When I cleared up a database which I was given the task of maintaining, I created some code with forms which showed me the potential matches when the match was not clear cut.
UPDATE tbl_name T1 
  SET [newlocation-ID] = DMin("IdLocation","tbl_name","Name = '" & Name & "' AND (Address LIKE '" & Address & "*' OR '" & Address  & "' LIKE (Address + '*') AND PC = '" & PC & "'")
WHERE EXISTS (SELECT T2.IdLocation FROM tbl_name T2 WHERE T1.Name = T2.Name AND 
(T1.Address LIKE (T2.Address + '*') OR T2.Address LIKE (T1.Address + '*'))
AND T1.PC = T2.PC AND T1.IdLocation <> T2.IdLocation)

Open in new window

0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Union 2 queries to a cte (temp table perhaps) 9 36
How to use DLookup with IsNull Function 4 26
performance query 4 24
SQL Query assistance 16 26
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

777 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