Here's a real challenge. moving dates in a table when new date is add 1

[Phideaux]

I am currently working on a feature that tracks task performance dates on a Preventative Maintenence program. My goal is to have a table that has performance dates for 5 different categories, covering the last 5 years. I need to enter a new performance date in to the table and as the predetermined number of years pass the furthest out date would "Fall Off" after 5 years. The Date table is tied to the equipment to be worked on by an EquipControl #. this field is present in both tables. Is there a way to record dates and alpha designations such as "A" , "B" ect. along with the date related to the last 5 years?

I am in WAY over My head Here and 2 different reference books haven't helped.

( the format might be something like "A - 01/01/2000; A - 01/01/2001; B - 01/01/2000; B - 01/01/2002" ) or any something like that. I'm fully open to ANY suggestions. thanks for looking and considering this quandry I'm facing.

The mind is the best toy.
Play with someone else's often

 

[PWise]

please explain some more

 

[LogicalmanUS]

Phideaux,
From what I understand of the problem, you have many equipment ID,s, and each ID has many dates assigned to it.
Once a date is further than 5 years old, you want it not to display any longer.

If this is the case, then you can simply set a flag column on the table (boolean), and set the flag to 1 as default, then to 0 when the 5 year mark is reached.
For display purposes, you simply select those records having the flag set.

I'm not sure of the Alpha designators' use.

Logicalman

 

[Phideaux]

You are correct that there are many pieces of equipment. Each one does have several dates afixed for performing various task operations. I'm attempting to build a mechanism that will generate a 5 year history of what task was done on which date. Tasks are designated by a number. for instance, p1 instructs the mechanic to paint a certain piece of equipment. g1 means general inspection. I'm trying to come up with a way of tracking when tasks were performed on a piece of equipment. it's a preventative maintenance history for that particular piece of equipment. At present the only method I have is shuffling through paper and locating the records from reports of P/Ms performed for all of the equipment month by month.

thanks for the help in getting the problem solved

The mind is the best toy.
Play with someone else's often

 

[LogicalmanUS]

Phideaux,

Thanks for the confirmation.
From your description this looks like a three tables schema:

1. Equipment, having Equipment ID, Description, etc
2. Work Type, having WorkTypeID, Description, etc
3. Dates, having Date, EquipmentID, WorkTypeID

For any given piece of equipment, an entry is made to the Dates table, which includes the Equipment ID and the Work Type ID, together with the date the work was done.

Using this three way setup, I constructed the tables:
1. tblEQUIPMENT e
2. tblWORKTYPE w
3. tblDATES d

Having populated them with various data, the following SQL statement for a query will produce results as follows:
Select All equipment records with dates in the past 5 years. Just open a new query in design view, close the Select Table dialogue box, select SQL View from menu bar, copy and paste this code into the Query.

SELECT
d.EQUIPID,
e.EQUIPNAME,
d.DATE_,
d.WT_ID,
w.WT_PREFIX,
w.WT_DESC
FROM (
tblDATES d LEFT JOIN
tblEQUIPMENT e ON d.EQUIPID = e.EQUIPID) INNER JOIN tblWORKTYPE w ON d.WT_ID = w.WT_ID
WHERE (((d.DATE_)>Now()-1820))
ORDER BY d.EQUIPID, d.DATE_ DESC;

This code uses alias's for each table as designated above (e w d). Simply change the table names for those that match your table names, and the column names to match those in your tables.


Logicalman