SQL: How can I create an update query using COUNT to...

K

Kamitsukenu

Hi there,

I'm a first time poster and I apologise now for any protocols I may have
accidently trampled over in posting here!

I'm trying to create an update query in MS Access where the query counts the
amount of addresses in a table that are the same, then if there are any
duplicate addresses in the table, it will mark a section field as "Bulk".

I've managed to do it as a select query, but I can't put this into an update
query. I've come up with this:

SELECT Count(tmpDefaultUnsortedTable![2] & tmpDefaultUnsortedTable![9]) AS
Copies
FROM tmpDefaultUnsortedTable
GROUP BY tmpDefaultUnsortedTable.[2], tmpDefaultUnsortedTable.[3],
tmpDefaultUnsortedTable.[4], tmpDefaultUnsortedTable.[5],
tmpDefaultUnsortedTable.[6], tmpDefaultUnsortedTable.[7],
tmpDefaultUnsortedTable.[8], tmpDefaultUnsortedTable.[9],
tmpDefaultUnsortedTable.[10]
HAVING (((Count([tmpDefaultUnsortedTable]![2] &
[tmpDefaultUnsortedTable]![9]))>1));


There is probably a really quick way of doing it, but I haven't a clue.

Please help!
 
J

John Spencer

Perhaps something like the following - hard to even guess with those
wonderfully descriptive field names

UPDATE TmpDefaultUnsortedTable as T
SET T.BulkField = True
WHERE T.[2] & T.[9] IN (
SELECT X.[2] & X.[9]
FROM tmpDefaultUnsortedTable As X
GROUP BY X.[2], X.[3],
X.[4], X.[5],
X.[6], X.[7],
X.[8], X.[9],
X.[10]
HAVING (((Count([X].[2] & [X].[9]))>1));
 
K

Kamitsukenu

Yeah, sorry about that! [1] - [10] are actually just address data fields eg.
name, Address Line 1, Address Line 2, Address Line 3 etc etc.

John Spencer said:
Perhaps something like the following - hard to even guess with those
wonderfully descriptive field names

UPDATE TmpDefaultUnsortedTable as T
SET T.BulkField = True
WHERE T.[2] & T.[9] IN (
SELECT X.[2] & X.[9]
FROM tmpDefaultUnsortedTable As X
GROUP BY X.[2], X.[3],
X.[4], X.[5],
X.[6], X.[7],
X.[8], X.[9],
X.[10]
HAVING (((Count([X].[2] & [X].[9]))>1));

Kamitsukenu said:
Hi there,

I'm a first time poster and I apologise now for any protocols I may have
accidently trampled over in posting here!

I'm trying to create an update query in MS Access where the query counts
the
amount of addresses in a table that are the same, then if there are any
duplicate addresses in the table, it will mark a section field as "Bulk".

I've managed to do it as a select query, but I can't put this into an
update
query. I've come up with this:

SELECT Count(tmpDefaultUnsortedTable![2] & tmpDefaultUnsortedTable![9]) AS
Copies
FROM tmpDefaultUnsortedTable
GROUP BY tmpDefaultUnsortedTable.[2], tmpDefaultUnsortedTable.[3],
tmpDefaultUnsortedTable.[4], tmpDefaultUnsortedTable.[5],
tmpDefaultUnsortedTable.[6], tmpDefaultUnsortedTable.[7],
tmpDefaultUnsortedTable.[8], tmpDefaultUnsortedTable.[9],
tmpDefaultUnsortedTable.[10]
HAVING (((Count([tmpDefaultUnsortedTable]![2] &
[tmpDefaultUnsortedTable]![9]))>1));


There is probably a really quick way of doing it, but I haven't a clue.

Please help!
Click to expand...
Click to expand...
 
K

Kamitsukenu

Hi again,

I forgot to say thanks for replying; I've fixed the problem thanks to your
help!

Cheers!

John Spencer said:
Perhaps something like the following - hard to even guess with those
wonderfully descriptive field names

UPDATE TmpDefaultUnsortedTable as T
SET T.BulkField = True
WHERE T.[2] & T.[9] IN (
SELECT X.[2] & X.[9]
FROM tmpDefaultUnsortedTable As X
GROUP BY X.[2], X.[3],
X.[4], X.[5],
X.[6], X.[7],
X.[8], X.[9],
X.[10]
HAVING (((Count([X].[2] & [X].[9]))>1));

Kamitsukenu said:
Hi there,

I'm a first time poster and I apologise now for any protocols I may have
accidently trampled over in posting here!

I'm trying to create an update query in MS Access where the query counts
the
amount of addresses in a table that are the same, then if there are any
duplicate addresses in the table, it will mark a section field as "Bulk".

I've managed to do it as a select query, but I can't put this into an
update
query. I've come up with this:

SELECT Count(tmpDefaultUnsortedTable![2] & tmpDefaultUnsortedTable![9]) AS
Copies
FROM tmpDefaultUnsortedTable
GROUP BY tmpDefaultUnsortedTable.[2], tmpDefaultUnsortedTable.[3],
tmpDefaultUnsortedTable.[4], tmpDefaultUnsortedTable.[5],
tmpDefaultUnsortedTable.[6], tmpDefaultUnsortedTable.[7],
tmpDefaultUnsortedTable.[8], tmpDefaultUnsortedTable.[9],
tmpDefaultUnsortedTable.[10]
HAVING (((Count([tmpDefaultUnsortedTable]![2] &
[tmpDefaultUnsortedTable]![9]))>1));


There is probably a really quick way of doing it, but I haven't a clue.

Please help!
Click to expand...
Click to expand...
 
Top