Counting distinct values as well as giving a sum

[davey4444]

Hello,

I am after some assistance in getting some information out of my database which is causing me some problems.
My data shows each individual journey that a truck has taken and I am trying to show the number of journeys each truck did in a given county on a given day and a given shift. I've given an example of the data below as well as the desired output. My understanding with vba is limited and have mostly used queries in Access so I'm a little stuck here but am more than willing to learn so any help is gratefully received.

Thanks.

Book1
	A	B	C	D	E
1	Data
2
3	County	Date	Shift	Registration	Use
4	Cornwall	01/04/2020	DAY	BCA478	FALSE
5	Cornwall	01/04/2020	DAY	DDD543	TRUE
6	Cornwall	01/04/2020	DAY	XYZ999	TRUE
7	Cornwall	01/04/2020	NIGHT	DDD543	TRUE
8	Cornwall	01/05/2020	DAY	DDD543	TRUE
9	Cornwall	01/05/2020	DAY	DDD543	TRUE
10	Cornwall	01/05/2020	DAY	DDD543	TRUE
11	Cornwall	01/05/2020	NIGHT	AAA111	TRUE
12	Cornwall	01/05/2020	NIGHT	ABC123	TRUE
13	Devon	01/04/2020	DAY	ABC123	TRUE
14	Devon	01/04/2020	DAY	XYZ999	TRUE
15	Devon	01/04/2020	NIGHT	ABC123	FALSE
16	Devon	01/04/2020	NIGHT	ABC123	TRUE
17	Devon	01/04/2020	NIGHT	BCA478	TRUE
18	Devon	01/04/2020	NIGHT	XYZ999	TRUE
19	Devon	01/05/2020	DAY	ABC123	TRUE
20	Devon	01/05/2020	DAY	BCA478	TRUE
21	Devon	01/05/2020	NIGHT	AAA111	TRUE
22	Devon	01/05/2020	NIGHT	AAA111	TRUE
23	Devon	01/05/2020	NIGHT	ABC123	TRUE
24	Devon	01/05/2020	NIGHT	ABC123	TRUE
25	Devon	01/05/2020	NIGHT	ABC123	TRUE
26
27
28	Output
29
30	County	Date	Shift	Registration	Jobs per county, day, shift
31	Cornwall	01/04/2020	DAY	DDD543	1
32	Cornwall	01/04/2020	DAY	XYZ999	1
33	Cornwall	01/04/2020	NIGHT	DDD543	1
34	Cornwall	01/05/2020	DAY	DDD543	3
35	Cornwall	01/05/2020	NIGHT	AAA111	1
36	Cornwall	01/05/2020	NIGHT	ABC123	1
37	Devon	01/04/2020	DAY	ABC123	1
38	Devon	01/04/2020	DAY	XYZ999	1
39	Devon	01/04/2020	NIGHT	ABC123	1
40	Devon	01/04/2020	NIGHT	BCA478	1
41	Devon	01/04/2020	NIGHT	XYZ999	1
42	Devon	01/05/2020	DAY	ABC123	1
43	Devon	01/05/2020	DAY	BCA478	1
44	Devon	01/05/2020	NIGHT	AAA111	2
45	Devon	01/05/2020	NIGHT	ABC123	3
Sheet1

 

[tyija1995]

Hey there, do you have O365?

If so then this can be achieved using FILTER and UNIQUE dynamic array functions.

So with the headers in row 30 (County, Date, Shift, Reg, Jobs per county, day, shift)
Go to cell A31:

=UNIQUE(FILTER(A3:D25,E3:E25=TRUE))

Then in cell E31:
=SUMPRODUCT((A31=$A$4:$A$25)*(B31=$B$4:$B$25)*(C31=$C$4:$C$25)*(D31=$D$4:$D$25)*(TRUE=$E$4:$E$25))

and drag this down to E45.

 

[davey4444]

Hey there, do you have O365?

If so then this can be achieved using FILTER and UNIQUE dynamic array functions.

So with the headers in row 30 (County, Date, Shift, Reg, Jobs per county, day, shift)
Go to cell A31:

=UNIQUE(FILTER(A3:D25,E3:E25=TRUE))

Then in cell E31:
=SUMPRODUCT((A31=$A$4:$A$25)*(B31=$B$4:$B$25)*(C31=$C$4:$C$25)*(D31=$D$4:$D$25)*(TRUE=$E$4:$E$25))

and drag this down to E45.

Hi, thanks for the reply. I do have O365 but it seems that the UNIQUE function is only available on the monthly channel and my company uses the semi-annual one so I'm unable to use it. Also, I think that due to the number of records I have, it's necessary to use Access for this which is a shame as I'm far happier in Excel.

 

[tyija1995]

Oh I see! Hmm, the same can be achieved using INDEX AGGREGATE combinations however, but if you're going to do it in Access that's fine. Do you want me to give you a solution with Excel still anyway or are you set on using Access?

 

[davey4444]

Unfortunately I think that I will have to settle with Access as I'm going to be dealing with more than 100k records but thanks so much for replying.

I've realised that my 'desired output' is actually not right and that I'd like a level up from this so just for example just showing the number of unique registrations used in Devon on 01/05 at night and then the number of journeys, so in this case it would be 2 different registrations and 5 journeys.

 

[tyija1995]

OK that's fine, have a great day hope you can figure it out with Access

 

[JonXL]

Can you tell us what the USE field is for and how it plays into getting your expected output?

What you are asking for is quite easy with Access using a SQL aggregate query.

Ignoring the USE field for now, create a new query, switch to SQL view, and paste this (rename [Data] to match your table name and any of the other fields as necessary...):

SELECT
    [County],
    [Date],
    [Shift],
    [Registration],
    Count(*) AS [Jobs per county, day, shift]
FROM
    [Data]
GROUP BY
    [County],
    [Date],
    [Shift],
    [Registration]

 

[xenou]

Note that assuming Use means use only the ones with true in the use column, then just add a where condition:

SELECT
    [County],
    [Date],
    [Shift],
    [Registration],
    Count(*) AS [Jobs per county, day, shift]
FROM
    [Data]
WHERE
    [Use] = -1
GROUP BY
    [County],
    [Date],
    [Shift],
    [Registration]

 

[petertenthije]

Why are you using VBA, when a query would also get the desired results?

SELECT Table1.County, Table1.Date, Table1.Shift, Table1.registration, Count(Table1.Use) AS [Jobs per county, day, shift]
FROM Table1
GROUP BY Table1.County, Table1.Date, Table1.Shift, Table1.registration, Table1.Use
HAVING (((Table1.Use)=True));

 

[petertenthije]

I've realised that my 'desired output' is actually not right and that I'd like a level up from this so just for example just showing the number of unique registrations used in Devon on 01/05 at night and then the number of journeys, so in this case it would be 2 different registrations and 5 journeys.

In that case I would use four queries.

Query 1 makes a list with unique combinations of county, date, shift and registrations
Query 2 uses the result of query 1, but counts the number of unique registrations
Query 3 counts the number of journeys
Query 4 combines the results of query 2 and query 3.


Query1_ registrations
SELECT DISTINCTROW Table1.County, Table1.Date, Table1.Shift, Table1.registration
FROM Table1
GROUP BY Table1.County, Table1.Date, Table1.Shift, Table1.registration, Table1.Use
HAVING (((Table1.Use)=True));

Query2_ registrations
SELECT DISTINCTROW [Query1_ registrations].County, [Query1_ registrations].Date, [Query1_ registrations].Shift, Count([Query1_ registrations].registration) AS Registrations
FROM [Query1_ registrations]
GROUP BY [Query1_ registrations].County, [Query1_ registrations].Date, [Query1_ registrations].Shift;

Query3_journeys
SELECT DISTINCT Table1.County, Table1.Date, Table1.Shift, Count(Table1.registration) AS Journeys
FROM Table1
GROUP BY Table1.County, Table1.Date, Table1.Shift, Table1.use
HAVING (((Table1.use)=True));

Query4_combination
SELECT [Query2_ registrations].County, [Query2_ registrations].Date, [Query2_ registrations].Shift, [Query2_ registrations].Registrations, Query3_journeys.Journeys
FROM [Query2_ registrations] INNER JOIN Query3_journeys ON ([Query2_ registrations].Shift = Query3_journeys.Shift) AND ([Query2_ registrations].Date = Query3_journeys.Date) AND ([Query2_ registrations].County = Query3_journeys.County);