You are here:

Using MS Access/Finding the earliest Date field in an access record

Advertisement


Question
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?
Regards

Charles

Answer
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:

tluMaintenanceType
MaintenanceTypeID (PK Autonumber)
MaintenanceType

Then your tblElectricalMaintenance should look like this:

ElectricalMaintenanceID (PK Autonumber)
ParentID (Foreign Key to parent record
MaintenanceTypeID (FK)
MaintenanceDate

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,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.