Access Date Query

[dashah]

I have a calibration/maintenanence data base with two tables. one being a master inventory reocrd with asset numbers and the other an actual history of the maintenance calibration records.
My problem: When I query the table for past due (<Date()in Date_Due) items, all the records for the items show up (I have Date_Calibrated and Date_Due fields.

How do Iseparte the latest item that is past due and and not show the past history for that item?

I am a new user to Access

Thanks

 

[THoey]

SELECT * from your_table
WHERE date_due > DATE() AND
date_calibrated is NULL; Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[dashah]

Thank you for the prompt reply.

However the date_calibrated already has the previous calibration date. To complicate things, the calibration interval varies (3, 6, 12 months). The database entry is made after the calibration so the date_calibrated and date_due is input at that time. Any suggestions?

Thanks again.

dashah

 

[THoey]

It sounds as if you only have one record per piece of equipment and you just change the date next due and date last completed each time. If this is true, how are you keeping a history? Maybe I am misunderstanding. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[dashah]

The assets are listed by an asset number. So, I have a history of each asset added to the table every time a calibration maintenance action takes place. e.g. asset no. 0555 may have five records detailing the calibration history.

Now, I want to extract the latest record from that history when it is past due. I have no problem making a query on what is due in 30 days or due for the month using the date query. I think what complicates it is the Cal_date and Cal_due fields and different time intervals between them.

How can I extract the latest record (Cal_due field)from say 5 records of asset no. 0555? Can I use a max() query? I tried that but it has not worked. I have about five Access refrence books but none have a solution. I know it can be done because commercial maintennace programs do this sort of query.

 

[THoey]

A little clearer, but it might help if you could show some sample data. The way I see it, when a calibration is done on a piece of equipment, the Cal_Date should be filled in, essentially filling that record, and then a new record should be created for the next calibration date with a blank Cal_Date field. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[dashah]

OK here is the sample data:

Asset# Date_Cal Date_Due
0555 3/12/98 3/12/99
0555 3/12/99 3/12/00
0555 3/12/00 3/12/01
0556 5/6/97 11/6/97
0556 11/6/97 5/6/98
0556 5/6/98 11/6/98

I want to catch the past due data which would be the last record for both asset nos 0555 and 0556.

My query would be: Cal_Due <date()and 556.556.556.556.556.
I want to ignore the other already done calibrations.

 

[braindead2]

If I understand what you are trying to do then the query should look something like

SELECT Max(Tablename.Due_date) AS MaxOfDue_date, Tabletablename.Assetid
FROM Tablename
GROUP BY Tablename.Assetid
HAVING (((Max(Tablename.Due_date))<Date()));

 

[THoey]

Do you perform the calibrations on exactly the due date? Because if you don't that adds to the complexity that takes it out of my range.

I don't know if this is an existing database that you are trying to add a capability to, or if it is one you are designing, but I am going to suggest you re-think how you want to store this data. The way I mentioned before would make this a whole lot easier on you all around. In that way, the Data would look like this:

Asset#      Date_Due        Date_Cal
0555        03/12/99        03/12/99
0555        03/12/00        03/12/00
0555        03/12/01          
0556        05/06/97        11/06/97
0556        11/06/97        05/06/98
0556        05/06/98

Every time a Calibration is done, a query would be used to pull up the Asset record where the Date_Cal is NULL and the date the task was done would be put there. Then, a new record would be created with the next Date_Due entered based on your 3/6/9/12 month formula and the Date_Cal left empty.

Also, to me that next Date_Due would come from a field either in your Master Inventory table or a third table that just had the asset number and the number of months between calibrations.

Sorry I was not able to help you with the way you wanted it done, but I am suree someone else will be able to figure something out. Terry M. Hoey
th3856@txmail.sbc.com
While I don't mind e-mail messages, please post all questions in these forums for the benefit of all members.

 

[dashah]

This is an existing database that I inherited. I appreciate the help Terry. I will keep checking to see if anyone has other ideas as well. I will also try what &quot;Braindead2&quot; suggested.

I am new at Access programming so all the help is appreciated. Thank you all in advance.

p.s. By the way I stumbled on this website bt accident and I think it is great!!!