Question about a separating customers into 3 groups

[mmiram]

Hi all:

I have one table with the following fields.

Customer ID
Customer First Name
Customer Last Name
Customer Transaction Number
Customer Transaction Volume
County

There are customers who do transactions in more than one county. In those cases we have multiple records for that customer. I would like to do the following:

1.) Get a list of the top 1000 customers by county sorted by transaction volume. This step is simple enough. However, the next step is what I am unable to figure out.

2.) If a customer has transactions in more than one county, then I want the customer appearing in only the county where he/she has the highest transaction volume. I do not want the customer appearing in the other counties' top 1000 lists even though he/she has transactions in those counties and might be in the top 1000 for that county.

I hope the question is clear. Is this possible? How can I do this. Thanks in advance for any help.

 

[Remou]

Perhaps:
[tt]SELECT TOP 1000
tblTable.[Customer ID],
tblTable.[Customer Transaction Volume],
tblTable.County
FROM tblTable
WHERE ((select top 1 county
from tblTable a
where a.[Customer ID]=tblTable.[Customer ID]
order by [Customer Transaction Volume] desc)
AND ((tblTable.County)=[Enter County No:]))
ORDER BY tblTable.[Customer Transaction Volume] DESC;[/tt]

 

[mmiram]

Hi Remou,

Thanks for the reply. However, I still have the issue.

I have a customer ID "1005" with transaction volumes as below:

County Volume

MC $500
DC $300

When I ran your query, "1005" showed up in the top 1000 lists of both counties. The customer should show up onlu in MC as that is the county in which he had the highest transaction volume.

Thanks,
Ram.

 

[Remou]

Sorry, missing brackets. Try this:
[tt]SELECT TOP 1000 tblTable.[Customer ID], tblTable.[Customer Transaction Volume], tblTable.County
FROM tblTable
WHERE (((tblTable.County)=[Enter County No:]) AND (((select top 1 county
from tblTable a
where a.[Customer ID] = tblTable.[Customer ID]
order by [Customer Transaction Volume] desc))=[Enter County No:]))
ORDER BY tblTable.[Customer Transaction Volume] DESC;[/tt]

 

[mmiram]

Hey Remou:

Sorry for the late reply but it works great...thanks a lot.