If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
SQL: How can I create an update query using COUNT to...
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! |
#2
|
|||
|
|||
How can I create an update query using COUNT to...
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" wrote in message ... 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! |
#3
|
|||
|
|||
How can I create an update query using COUNT to...
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" wrote: 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" wrote in message ... 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! |
#4
|
|||
|
|||
How can I create an update query using COUNT to...
Hi again,
I forgot to say thanks for replying; I've fixed the problem thanks to your help! Cheers! "John Spencer" wrote: 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" wrote in message ... 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! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Calculated field in pass through query | Vaughan | Running & Setting Up Queries | 7 | January 12th, 2006 02:26 AM |
Summarizing records in one table that match a different table | TomC | Setting Up & Running Reports | 16 | December 30th, 2005 03:29 AM |
Create a sum of records in an open recordset(Access97) | Reiner Harmgardt | General Discussion | 0 | March 7th, 2005 10:03 AM |
Unable to insert record to Access via SQL from an ASP page | Norman Yuan | General Discussion | 1 | February 2nd, 2005 08:23 PM |
update queries and forms | Mary Pode | Database Design | 16 | February 2nd, 2005 05:25 PM |