Help with MAX and ACCESS

[yorrick]

I can't get this to work!

Train_No Station Passengers
8000 GER 100
8000 FRA 120
8000 ELA 80
9000 GER 25
9000 FRA 200
9000 ELA 50

All I want is to return the station and the passengers for each train where passenger is highest.
Therefore results are
8000>FRA>120 and 9000>FRA>200

Can this be done using simple Access queries. I tried grouping and using the max function on Passengers but it didn't work.
All help appreciated.

 

[fneily]

First post Access questions in the Access forums. So yours would be in Microsoft: Access Queries and JET SQL
You'll have to do two queries.
I'm assuming your tram table looks like this:
TramID Tram_No Station Passengers
It should have a primary key like I have - TramID
Create a query from this table. Place Tram_No and Passengers in the grid. Click on the Totals button (Sigma). Under Tram_No, it should say GroupBy. Under Passengers, change it to MAX. Run the query. You'll get 8000, 120 and 9000, 200.
Create under query. Use the first query as a source and use the original table as the second source. Manually connect Tram_No to Tram_No and MaxOfPassengers to Passengers. From the query source, bring Tram_No and MaxOfPassengers into the grid and Station from the table into the grid. Then when you run it, it'll show the result you want.

 

[yorrick]

Unfortunately there is no primary key on the table, so this method doesn't work.

 

[fneily]

No. I mentioned a primary key because if your database was created correctly, every table would have a primary key. I am not using the primary key to achieve your answer. The rest of the steps work perfectly.
"Create under query" should read "Create another query". oops.

 

[pbrodsky]

Train_no and Station is probably a valid compound primary key.

 

[fneily]

pbrodsky - correct. Toss up between a single field or multi field primary key.