Count duplicates as one.

[Jmsteph]

I know this should be simple, but I can’t seem to figure it out. I need to create a query counting the number of clients in each state. The issue is that the clients can be in the table multiple times if they have multiple policies. I have their SSN in the table so I am thinking I could filter it by that. Any ideas would be greatly appreciated.
Thanks,

 

[spoole]

You need 2 queries.

1. Add State and Customer to your query. Group by state then Customer and output to table (this will give you a table with only one record for each customer in each state)
2. Add State and customer again (from above query table) Group by state then do a count of Customers.

 

[Jmsteph]

That worked. Thanks for your help!!!

 

[nguyeda]

I have another challenge similar to this but I can't think it out.

I have 4 important fields.

Order Number (Unique except 1st 5 charectors)
Product Number
Quantity
Customer Number

I need to filter the 8 important order numbers that start with 5 charectors like THOQ or SADJAA. I filtered the data for order numbers with Like "THOQ*" then grouped by product number, order quanity and customer number.

The results show all the order numbers but they CAN apply to different product numbers. So the product numbers duplicate themselves, thus duplicating the quantity.

How do I stop that? I know it sounds like a terrible database but I can't do anything about it.

The end result I want is to know the Order Numbers and the total quanity per product number per customer number.

The actual order number doesn't matter as long as I'm able to filter out and use only the important ones.

 

[nguyeda]

Well, never mind. I did a query of a query of a query to get it. If anyone knows a simplar way, please let me know!