Query Unique Count

[MikeEllis]

Hi All,

I have a simple table in a database that looks like this:

Client # Debtor # Extract Month
107 2567 01/2015
107 2567 02/2015
107 2567 03/2015
107 2567 04/2015
107 2575 01/2015
107 2575 02/2015


107 2575 03/2015
107 2575 04/2015
110 2125 01/2015
110 2125 02/2015
110 2125 03/2015
110 2130 01/2015

I need a query that will count the number of different debtor numbers under a given month for each client number

It should look like the table below. Ideally without the requirement for a sub-query.


Client # Debtor # Extract Month
107 2 01/2015
107 2 02/2015
107 2 03/2015
107 2 04/2015
110 2 01/2015
110 1 02/2015
110 1 03/2015

Any help would be much appreciated.

Thanks

Michael

[ItsMe]

Maybe you can use the Query Designer to create a Totals Query. With the designer open, click the Sigma symbol at the top right, within the Ribbon. Now you have a totals query. At the bottom of the designer window is a grid. When you have a totals query, a row will be added to the grid, I believe it says Total. Locate you field you want to Count and change Group By to Count.

The trick to these is to not add too many fields, since you are looking for unique records. The more fields you add, the greater chance a given record will be unique and reduce your Count result.

[MikeEllis]

Thanks,

Having done this I can now see that there are sometimes multiple appearances of the same debtor number within a given month.

Theses are counted individually. I need it to consider if it is a unique debtor for the client and month...only counting it once.

Any thoughts given this additional complication?

Thanks

Michael

[ItsMe]

If it is for the month and year only, you can try applying an alias to your date field. If you have the alias, you do not want the original date field included. With your alias use the DartPart() function. In fact, you will want to use it twice for two alias'. Use DatePart() for the year and also for the month. You will need to concatenate the two together with some literal text & "/" &

https://msdn.microsoft.com/en-us/lib...ffice.14).aspx