Conditional formating or similar for access query

[Bob H]

In an Excel spreadsheet there is 'conditional formating' for individual
cells. So fo any given cell in one column you can for example add days
in another.
Col A is todays date, then with conditional formating Col B is or can be
automatically a date in the future.

In Access I have a Tools Table which has ID Tool Item Last Test Date
and Next Test Date.
I can automatically assign the Next Test Date in the query by having
Next Test Date:[Last Test Date] + 180
But different tool items have different time periods between test dates,
as some are yearly and others are 6 monthly.

My question is how can I automaticaly assign either 360 or 180 days for
the given tools when I run the query?

Thanks

 

[Dale Fye]

Bob,

In a tool calibration application I created a number of years ago, I created
a ToolTypes table, that contained the type of tool and the calibration
increment (
"d", "m", "y"), and the calibration frequency.

With this type of setup, you can join the ToolTypes table to the Tools table
on the ToolType field, and use the Increment and Frequency fields from
ToolTypes to identify the the date for the next calibration.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Bob H]

Bob,

In a tool calibration application I created a number of years ago, I created
a ToolTypes table, that contained the type of tool and the calibration
increment (
"d", "m", "y"), and the calibration frequency.

With this type of setup, you can join the ToolTypes table to the Tools table
on the ToolType field, and use the Increment and Frequency fields from
ToolTypes to identify the the date for the next calibration.

Thanks for the help, but in my case I can't see how calibration
increment and calibration frequency work, because for example we have a
lifting cage which needs testing every 6 months then there is a torque
wrench which needs testing/calibrating every 12 months.

So if I put or select say 180 days for one tool/item from a combo box
for the calibration increment, what do I need for calibration frequency?

Thanks again

 

[Dale Fye]

It might look like:

ToolType Increment Frequency
Cage m 6
Torque Wrench m 12
Volt Meter d 90

Then, your query might look like:

SELECT T.Tool#, T.ToolType, T.OwnerID, T.LastTest,
DateAdd(TT.Increment, TT.Frequency, T.LastTest) as NextTest
FROM Tools as T
INNER JOIN ToolTypes as TT
ON T.ToolType = TT.ToolType
WHERE TT.Increment IS NOT NULL

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Klatuu]

How do you know what the interval should be?