Query help please – finding the most recent date

I am hoping for some help, i am ok in terms of most queries but I am having issues getting the results I need at the moment. I have never used VB but am happy to learn if someone can point me in the right direction. I am using Access 2013.

I have a number of tables, the 2 i need for this are: Table 1 – main list of all vehicles (vehicle ID, rego, name, make etc) Table 2 – list of services / repairs (service ID, rego – related to table 1, service date, type of service – dropdown from table 3 – inspection/service/repair, details of service, km of next service, date of next service due,

Table 1 has approx 40 vehicles & plant equipment Table 2 has every invoice entered as we receive the invoices, so there are multiple lines for each vehicle, each vehicle also has lines for repairs as well as the services.

I have done a lookup in table 2, so it shows the rego & associated vehicle name, but stores the vehicle ID.

There is a 1:many relationships table 1: table2

Note: difference vehicles have different maintenance schedules so i manually enter the next due date and next due km fields, when i am entering the service detail

I am trying to get a query to show the following details Rego (table 1) Vehicle name(table 1) Date of last service (table 2) Km of next service (table 2) Date of next service due (table 2)

I can get it to only look at the services (not repairs etc) I have tried turning on totals and can get it to work to show the most recent service date, but once I add the next service kn & next service date fields it shows all the lines for each vehicle not just the most recent.

If someone can help me figure this one out I would be very appreciative as it is driving me nuts. As once it is working i will create reports from it to show overdue services and services due in the next 30 days (I can do this part. )


submitted by /u/MJBaddy
[link] [comments]

Get the best Microsoft Access conultant

Get the best Microsoft Access programmer

Posted on