lankapala
asked on
Access and SQL query
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
koutny: your are great. great work,
ASKER
great work thx
ASKER
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 % ?
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.
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)
>>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.