Report - Count Current Location

[xyba]

I need to create a report to count vehicles at a current location but I haven't got a clue how to create this, or if it's possible.

In the attached simple db each vehicle can be based at different locations and I want to count the number at a specific CURRENT location.

For example, records 2, 4 and 5 are currently at Cardiff as that is the last location on their respective record. Therefore the report should show:

Cardiff 3
Manchester 1
Glasgow 1

Any help with this much appreciated.

 

[Ranman256]

your table is designed wrong. Access is a little different from Excel.
you need 2 fields for the table,tVehicle, just VEHICLE , LOCATION.
You only need where it currently is.
Now, IF you want a history , you will need another table, tHistory,
VEHICLE , LOCATION, OUTDATE, RETURNDATE
This would record every time a veh is taken out. The main tVehicle table , will only show where it is now.

Your entry table would need to use queries. Use a combo box to pick a vehicle, Then if the veh changes, execute a query to write a new record to the tHistory table.

 

[JHB]

your table is designed wrong. Access is a little different from Excel.

Definitely.

 

[xyba]

your table is designed wrong. Access is a little different from Excel.
you need 2 fields for the table,tVehicle, just VEHICLE , LOCATION.
You only need where it currently is.
Now, IF you want a history , you will need another table, tHistory,
VEHICLE , LOCATION, OUTDATE, RETURNDATE
This would record every time a veh is taken out. The main tVehicle table , will only show where it is now.

Your entry table would need to use queries. Use a combo box to pick a vehicle, Then if the veh changes, execute a query to write a new record to the tHistory table.

Can I do this with a table that is already in use and records all fields. What I mean is I have a working db that has one particular table that records all fields of a record. Would I have to split that?

Also, it has many fields but adjacent to each location field it has a date field that corresponds to the date it arrived there. Is there a way of looking across the record to find the last location recorded? This wouldn't be for a report but to populate a locked field on a split-form as a visible reference.