Crosstab Query with latest date/time criteria (1 Viewer)

IvanM

Registered User.
Local time
Yesterday, 21:52
Joined
Jul 15, 2016
Messages
20
I have a table with data which looks like this -

<<Hist_Date>> <Area> <Jobs>
11/10/16 15:53.... AA....... 12
11/10/16 15:53.... AB....... 15
11/10/16 15:53.... AC....... 20
12/10/16 11:22.... AA....... 10
12/10/16 11:22.... AB....... 12
12/10/16 11:22.... AC....... 14
12/10/16 14:32.... AA....... 20
12/10/16 14:32.... AB....... 16
12/10/16 14:32.... AC....... 10
13/10/16 10:32.... AA....... 10
13/10/16 10:32.... AB....... 18
13/10/16 10:32.... AC....... 14

I've created a crosstab query that sums the jobs by DATE (the row) and by AREAs combined (the column) which works fine except I need to always use the latest time for each day. i.e, above there are 6 records for the 12/10/16, but I only want to use the 14:32 timestamp ones (latest data) and exclude the records with the 11:22 timestamp...

So the crosstab results should be -

<<Date>> <AllAreas>
11/10/16........ 47
12/10/16........ 46 *
13/10/16........ 42

* Currently for the 12/10/16 I get 82 for the 12/10/16 because I haven't found a way to filter the latest timestamp for each day.

Any ideas? All help gratefully received!!
 
Last edited:

plog

Banishment Pending
Local time
Yesterday, 23:52
Joined
May 11, 2011
Messages
11,613
I'm confused by your use of the term 'crosstab query'. Your desired results example is not a cross tab query.

So, throwing out that term and just going by what results you said you want, this is what you should do:

Create a sub-query. This sub-query will determine the time-stamp of the Hist_Date field to use for each day. This is that SQL:

Code:
SELECT DateValue([Hist_Date]) AS JobDate, MAX([Hist_Date]) AS LastTime
FROM YourTableNameHere
GROUP BY DateValue([Hist_Date])

That will produce the last time of each unique day. With it you can create a new query based on it and YourTableNameHere to get just the totals you want.
 

IvanM

Registered User.
Local time
Yesterday, 21:52
Joined
Jul 15, 2016
Messages
20
Thanks Minty, that reply was pretty much instant!
I had genuinely thought about doing it that way, but it always irritates me to use 2 queries when only 1 is needed and hoped there might be a better solution...

Thanks again!
 

Users who are viewing this thread

Top Bottom