On duplicate change value of other field

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(*)
1 And [fldCity] = [tblContacts].[fldCity])))
Click to expand...
ORDER BY tblContacts.fldAddress, tblContacts.fldCity;


Thanks so much for the time and input of everyone reading this!

-Matt P
 
K

KARL DEWEY

Try these two queries --
tblContacts_Grp --
SELECT tblContacts.fldAddress, tblContacts.fldCity,
First(tblContacts.fldContactsID) AS FirstOffldContactsID
FROM tblContacts
GROUP BY tblContacts.fldAddress, tblContacts.fldCity
ORDER BY First(tblContacts.fldContactsID);

SELECT tblContacts.fldContactsID, tblContacts_Grp.FirstOffldContactsID,
tblContacts.fldName, tblContacts.fldAddress, tblContacts.fldCity,
tblContacts.fldCompanyName, tblContacts.fldLastMeetingDate,
tblContacts.fldNotes
FROM tblContacts_Grp INNER JOIN tblContacts ON (tblContacts_Grp.fldCity =
tblContacts.fldCity) AND (tblContacts_Grp.fldAddress =
tblContacts.fldAddress);


Matt P said:
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(*)
1 And [fldCity] = [tblContacts].[fldCity])))
Click to expand...
ORDER BY tblContacts.fldAddress, tblContacts.fldCity;


Thanks so much for the time and input of everyone reading this!

-Matt P
Click to expand...
 
M

Matt P

Try these two queries --
   tblContacts_Grp --
SELECT tblContacts.fldAddress, tblContacts.fldCity,
First(tblContacts.fldContactsID) AS FirstOffldContactsID
FROM tblContacts
GROUP BY tblContacts.fldAddress, tblContacts.fldCity
ORDER BY First(tblContacts.fldContactsID);

SELECT tblContacts.fldContactsID, tblContacts_Grp.FirstOffldContactsID,
tblContacts.fldName, tblContacts.fldAddress, tblContacts.fldCity,
tblContacts.fldCompanyName, tblContacts.fldLastMeetingDate,
tblContacts.fldNotes
FROM tblContacts_Grp INNER JOIN tblContacts ON (tblContacts_Grp.fldCity =
tblContacts.fldCity) AND (tblContacts_Grp.fldAddress =
tblContacts.fldAddress);



Matt P said:
Hello,
I have a query made to find all duplicates and here is what I need it
to do now:
Click to expand...
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.
Click to expand...
tblContacts and tblHistory <--- new table
Click to expand...
in tblContacts there will only be the following fields:
fldAddress  <--- multiple matches found, usually at least two to four
matches
fldCity  <---second field required
Click to expand...
fldContactsID  <---- Primary Key
Click to expand...
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
Click to expand...
It would change them all to the first value
651 stays the same ------> 651
783 display as  ------------> 651
798 display as  ------------> 651
Click to expand...
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
Click to expand...
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)
Click to expand...
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(*)
1  And [fldCity] = [tblContacts].[fldCity])))
Click to expand...
ORDER BY tblContacts.fldAddress, tblContacts.fldCity;
Click to expand...
Thanks so much for the time and input of everyone reading this!
Click to expand...
-Matt P- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks Karl worked great!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Passing statements like "Or" or regular string values to querycriteria field 6
Entry of primary key data 11
Query Me This -- two references to another tables field 7
Query Join on Multiple Field Indexes 3
Change field Value using update query 1
Complicated search set up? 2
Append query to change the value of an autonumber field 1
Formatting lost on part of concatenated field of report 6

Top