Using MS Access/Finding the earliest Date field in an access record
My problem is determining the earliest date from a number of fields (5) within a record to display in another column in the Table.
I currently have a table called “tblElectricalMaintenance” that is designed to record dates that different types of electrical maintenance have been carried out.
The fields are as follows: Alternator, HourMeter, 900Enclosure, PJBSwitch and ReverseAlarm. These maintenance activities are carried out on different dates. However I have a need to know when the first Electrical Maintenance activity (whichever of the 5), occurred and then report on it
Can I use a query to determine the required outcome or should I use VBA? I do no know what criteria to put in the query and also if I use VBA what would the code look like.
I have a little experience with VBA however; I have no clue as how to start this code. I thought of using a nested “IIf Function”, but that got too complicated.
Can you help me?
Well your problem is basically that you have a denormalized structure. If your database was designed properly this would be easy.
You have made a common mistake for novices in that you created a horizontal structure using field names to define data rather than a vertical structure. You should have two tables here. A lookup table for maintenance types:
MaintenanceTypeID (PK Autonumber)
Then your tblElectricalMaintenance should look like this:
ElectricalMaintenanceID (PK Autonumber)
ParentID (Foreign Key to parent record
From there it would be very easy to create a query to return the earliest date. Or you could simply use a DMin() function
And, by the way, you shouldn't store this value.
Hope this helps,
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA