Create Query on max (1 Viewer)

Hi,

I am new to access. I seek some help creating a query.

I have a table with field Date, Time, Traffic, cell, ...

I wish to create a query, which gives me one record from this table where Traffic is max, for that cell along with all other fields related to that record.

Date Time Cell Name Traffic TR PR CR DR RK
06-Mar-19 12:00:00 AM XX001A 11.9542 1 12 0 12 2
06-Mar-19 12:00:00 AM XX001B 11.7528 2 11 0 11 2
06-Mar-19 12:00:00 AM XX001C 11 2 11 0 11 2
07-Mar-19 12:00:00 AM XX001A 14 1 12 0 12 2
07-Mar-19 12:00:00 AM XX001B 11.0222 1 13 0 13 2
07-Mar-19 12:00:00 AM XX001C 11 3 11 0 11 2
08-Mar-19 12:00:00 AM XX001A 10.8222 2 11 0 11 2
08-Mar-19 12:00:00 AM XX001B 11 2 11 0 11 2
08-Mar-19 12:00:00 AM XX001C 10.6847 2 12 0 12 2
09-Mar-19 12:00:00 AM XX001A 12 1 12 0 12 2
09-Mar-19 12:00:00 AM XX001B 10.9944 3 11 0 11 2
09-Mar-19 12:00:00 AM XX001C 12.3639 1 12 0 12 2
10-Mar-19 12:00:00 AM XX001A 9.3329 3 10 0 10 2
10-Mar-19 12:00:00 AM XX001B 10.9819 2 11 0 11 2


Appreciate your help.

Thx,

Create an aggregate query by clicking the Totals button on the ribbon.
Add all fields required to the query window. all will say Group By in the Totals row
Change the Total row entry for the Traffic field from Group By to Max.

However, depending n your requirements, you may need to reduce the fields included if you get multiple records for the same cell name. For example, you may need to omit the date & time fields from group by.
If so you then join the aggregate query to the original table to get the field values for date & time where the traffic is a maximum

Hope that helps

BTW - both date & time are reserved words and shouldn't be used for field names. Instead use e.g. TMDate, TMTime where TM stands for traffic measurement

1. create first a total query (qryDateMaxTraffic):
2. create the final query (joining qryDateMaxTraffic to yourTable):

isladogs said:

Create an aggregate query by clicking the Totals button on the ribbon.
Add all fields required to the query window. all will say Group By in the Totals row
...
Hope that helps

Click to expand...

I tried to do this way, but i got stuck with double variable lookup. Because, I will need to lookup two variables from one with two variables from another to get rest of the fields. Ended up messing it.

isladogs said:

BTW - both date & time are reserved words and shouldn't be used for field names. Instead use e.g. TMDate, TMTime where TM stands for traffic measurement

Click to expand...

Thanks for the advice. I will try to change it.

arnelgp said:

1. create first a total query (qryDateMaxTraffic):

Code:Copy to clipboard

SELECT yourTable.date, 
         yourTable.time, Max(yourTable.traffic) AS MaxOftraffic
FROM yourTable
GROUP BY yourTable.date, yourTable.time;

2. create the final query (joining qryDateMaxTraffic to yourTable):

Code:Copy to clipboard

SELECT qryDateMaxTraffic.date, 
qryDateMaxTraffic.time, 
yourTable.cell, 
yourTable.name, 
qryDateMaxTraffic.MaxOftraffic AS Traffic, 
yourTable.tr, 
yourTable.pr, 
yourTable.cr, 
yourTable.dr, 
yourTable.rk
FROM qryDateMaxTraffic 
INNER JOIN yourTable 
ON (qryDateMaxTraffic.MaxOftraffic = yourTable.traffic) 
AND (qryDateMaxTraffic.time = yourTable.time) 
AND (qryDateMaxTraffic.date = yourTable.date);

Click to expand...

Thanks. I will try to implement it this way.