Need unique customer number

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a database of customers, customer addresses and sales figures for a
period. A customer may have up to four records in the database. When a
customer appears multiple times they have a unique identifier (1,2,3, or 4)
so the combination customer # and identifier make a unique record.

Ex:

Cust # Identifier
6789 1
6789 2
6789 3
1357 1
1357 2
9876 3
9876 4
6543 1

I would like to pull out the unique combination of customer number and max
value identifier. For example the result of my query should be:

6789 3
1357 2
9876 4
6543 1

Can Access accomplish this?

Thanks.
 
Hi, chas,

Yes, at least 2 ways, to wit:

SELECT [Cust # ],Max(Identifier) As MaxOfIdentifier FROM YourTableName GROUP
BY [Cust # ];

Or, without even using Identifier,

SELECT [Cust # ],Count([Cust # ]) As [CountOfCust # ] FROM YourTableName
GROUP BY [Cust # ];

Take your pick.

But wait! I see Customer 9876 starts with 3, not 1. Why? (This might affect
which SQL you use above.) Also, what does the highest number for customer
represent

Sam
 
Back
Top