 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / General 1 / February 2005A very specific problem involving duplicates
Our company has just merged with another and as a result we've merged our customer databases. The data was stored in each database slightly different so there were some issues with the merge. Namely, one person may be listed several times under different addresses or slightly different spellings of the same address or a new surname etc etc. My goal is to, without actually making any hard deletions to the original tables, create a query that will display all our customers, identified and sorted by their social security number, and listing them only once. The SSN is *not* a primary key field in any of the source tables, but it is the best way I can think of to make sure every customer in the resulting query is unique. It seems like this should be as easy as putting some kind of Unique modifier on the SSN record, but I'm not seeing anything of the sort. Can anyone lend some assistance? Note that I am using Access 2000. Thanks! John Hutchinson johnhutch@gmail.com SSN may not be a key in your existing table, but you can make the record key of a new table that has the same layout (copy structure only, then make SSN the key). Then use append queries to add records from both tables. Only those records with unique SSNs will be added to the new table. unfortunately, I don't believe this is an option for two reasons: 1.) It will eliminate customers completely, as opposed to just eliminating extra rows. If there are 6 rows concerning a Mr. John Doe with a SSN of 111-11-1111, the end result should give me one row. Any of the six rows will do. 2.) I don't believe creating a new table is an option, though I could be wrong. This query needs to be a one-click solution run on a daily feed of data (it will eventually be used to spit out a nicely formatted report for laymen users). I wouldn't building a new table create an extra step in the process every day for whomever wanted to view the final report? It will not eliminate customers completely, since it will append 1 record for each SSN. But I was assuming you wanted to get rid of double records; if you want to keep all your records but display only one for each person you could create a query, select SSN and any other field you want to see, and make it a "totals" query. Under SSN in the totals field, select "First", and under the other fields select "Group by". This *almost* worked. It definitely cut down on the number of results I got, however, there are still some duplicates in the cases where one customer has two different addresses listed, or one record has a birthdate and the other does not. Am I stuck? OK, sorry, do it the other way 'round. Group by SSN, and ask for "First" of the other fields you need to see. I tried it out, it works for me! yes, it worked! thank you! |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.