M
Matt P
Hello,
I have a query made to find all duplicates and here is what I need it
to do now:
1. The database I have is from someone who didn't know how to set it
up very well and there are too many records to update manually by
hand. Instead of just one table holding all the records there will be
two tables.
tblContacts and tblHistory <--- new table
in tblContacts there will only be the following fields:
fldAddress <--- multiple matches found, usually at least two to four
matches
fldCity <---second field required
fldContactsID <---- Primary Key
What I need it to do is to manipulate the fldContactsID (Primary Key)
for every repeat to be the same as the first (the reason I am doing
this is so that I can copy and paste it into the new table). So
instead of having all unique values like this:
651
783
798
It would change them all to the first value
651 stays the same ------> 651
783 display as ------------> 651
798 display as ------------> 651
Then when the next duplicates are found it does the same thing
383 stays the same-------> 383
574 display as --------------> 383
356 display as --------------> 383
and so on... (Like I said I am not trying to actually change the
primary key because I realize thats not possible, all I want to do is
manipulate the data so that I can copy and paste it into the other
table)
The sql code is:
SELECT tblContacts.fldAddress, tblContacts.fldCity,
tblContacts.fldContactsID, tblContacts.fldName,
tblContacts.fldCompanyName, tblContacts.fldLastMeetingDate,
tblContacts.fldNotes
FROM tblContacts
WHERE (((tblContacts.fldAddress) In (SELECT [fldAddress] FROM
[tblContacts] As Tmp GROUP BY [fldAddress],[fldCity] HAVING Count(*)
Thanks so much for the time and input of everyone reading this!
-Matt P
I have a query made to find all duplicates and here is what I need it
to do now:
1. The database I have is from someone who didn't know how to set it
up very well and there are too many records to update manually by
hand. Instead of just one table holding all the records there will be
two tables.
tblContacts and tblHistory <--- new table
in tblContacts there will only be the following fields:
fldAddress <--- multiple matches found, usually at least two to four
matches
fldCity <---second field required
fldContactsID <---- Primary Key
What I need it to do is to manipulate the fldContactsID (Primary Key)
for every repeat to be the same as the first (the reason I am doing
this is so that I can copy and paste it into the new table). So
instead of having all unique values like this:
651
783
798
It would change them all to the first value
651 stays the same ------> 651
783 display as ------------> 651
798 display as ------------> 651
Then when the next duplicates are found it does the same thing
383 stays the same-------> 383
574 display as --------------> 383
356 display as --------------> 383
and so on... (Like I said I am not trying to actually change the
primary key because I realize thats not possible, all I want to do is
manipulate the data so that I can copy and paste it into the other
table)
The sql code is:
SELECT tblContacts.fldAddress, tblContacts.fldCity,
tblContacts.fldContactsID, tblContacts.fldName,
tblContacts.fldCompanyName, tblContacts.fldLastMeetingDate,
tblContacts.fldNotes
FROM tblContacts
WHERE (((tblContacts.fldAddress) In (SELECT [fldAddress] FROM
[tblContacts] As Tmp GROUP BY [fldAddress],[fldCity] HAVING Count(*)
ORDER BY tblContacts.fldAddress, tblContacts.fldCity;
Thanks so much for the time and input of everyone reading this!
-Matt P