duplicates in access

  • Thread starter Thread starter Malika Jacob via AccessMonster.com
  • Start date Start date
M

Malika Jacob via AccessMonster.com

having trouble writing a query for the following case. suppose this is my
database:

id cust_name loc phone
1 abc bom 1111
1 abc bom 2222
2 def nwd 1212
3 def nwd 1212
4 xyz maa 3333
5 xyz maa 3331
6 xyz maa 3332

i want a query that will return those rows where:
if for a particular customer and loc there are 2 id's (eg cust"def" has id'
2,3...for customer xyz there are 3 id's 4,5,6)
 
Hi,


That is a LIST aggregate, but unfortunately, it does not exist in the
actual version.


You can mimic the result with the help of a temporary table, on the
other hand, in two steps, like this:


1- (Delete the temporary table, if it exists already, and ) Create the
temporary table tempList

SELECT DISTINCT cust_name, iif(false, ' ', null) As listOfID INTO
tempList FROM myTable


2- Append the data into tempList

UPDATE tempList INNER JOIN cust_name ON tempList.cust_name =
myTable.cust_name
SET listOfID = (listOfID + ", ") & CStr(myTable.id)



The desired data is in tempList. If you don't want duplicated id, you may
try to use a saved query with the SQL code:

SELECT DISTINCT id, cust_name FROM myTable

instead of myTable, directly, in step 2, so the duplicated values of id, for
a given cust_name, are removed, before being appended.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top