Update query with multiple spellings

  • Thread starter Thread starter subs
  • Start date Start date
S

subs

customer id customer New customer
10021 pg pg
10021 pgr pg
10022 dp dp
10022 dpa dp
10022 dp3 dp


i have two columsn of data which are populated. customer id and
customer. The column New customer has to be populated by an update
query such that new customer has to be equal to column customer when
ever they have equal customer ids. for example the first two entries
in the new colum are pg since they have equal customer ids. Now this
could be populated by either pg or pgr- but pg is repeated more no of
times in my original data . So new customer field will have pg in the
first two fields.

So pls let me know the query such that new customer colmn is populated
by using two crieteria

1) Customer id field values must be equl
2) when ever customer id field are same, then populate the last colum
with the value of the column customer which is repeated max no of
times.

THANKS
 
Not an update query and you did not specify what you want to do in the case of
ties?

SELECT CustomerID, Customer
FROM SomeTable
WHERE Customer in (
SELECT TOP 1 Customer
FROM SomeTable as TEMP
WHERE Temp.CustomerID = SomeTable.CustomerID
GROUP BY Customer
ORDER BY Count(Customer) DESC)


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Not an update query and you did not specify what you want to do in the case of
ties?

SELECT CustomerID, Customer
FROM SomeTable
WHERE Customer in (
SELECT TOP 1 Customer
FROM SomeTable as TEMP
WHERE Temp.CustomerID = SomeTable.CustomerID
GROUP BY Customer
ORDER BY Count(Customer) DESC)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County








- Show quoted text -
Click to expand...


No i am not getting what i need --- if below is the sample data

customerid customer New Customer
10025 rajesh
10025 raj
10025 rajesh
12356 prg
12356 prg1
12356 prg1
108578 pg


then the output should be
customerid customer New Customer
10025 rajesh rajesh (because rajesh is repeated twice in col
customer)
10025 raj rajesh
10025 rajesh rajesh
12356 prg prg1
12356 prg1 prg1
12356 prg1 prg1
108578 pg pb

Your o'p is as follows

CustomerIDCustomer
10025 rajesh
10025 rajesh
12356 prg1
12356 prg1
108578 pg

Your output is missing a couple of rows. Pls help with any
modifications
 
I'm not sure this will work, but I would try

SELECT CustomerID, Customer,
, ( SELECT First(Customer)
FROM SomeTable as X
WHERE X.Customer in (
SELECT TOP 1 Customer
FROM SomeTable as TEMP
WHERE Temp.CustomerID = SomeTable.CustomerID
GROUP BY Customer
ORDER BY Count(Customer) DESC)) As NewCustomer
FROM SomeTable

That cannot be used in an update query due to restrictions that Access has.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top