Counting multiple fields in a table that contain dates.

[tonycl69]

Please find attached a table in which I would like to count each field if the dates falls in the past and have the count as a total or individual in a popup to show what is out of date, and if the user requires to see any of the results how to create a form to show those results. Or should I split all the date fields into separate tables? Your help would be greatly appreciated.

 

[plog]

Or should I split all the date fields into separate tables?

You should reconfigure the test data so that test type is a field, not data in a field name. Instead of many tables for each type of test, you will have one table that has 4 fields:

Tests
TestID, TestType, TestDate, UnitID

So instead of a field called 'BrakeTest', you would create a new record in this table and that record's TestType's value would be 'Brake'.

 

[tonycl69]

Excellent Plog, never even thought of it like that, doh!, thanks again I will put it into practise immediately.

The original table has 2000 entries any idea how to repopulate the new table as you suggested with the old data?

 

[tonycl69]

Me again! Right I did what you suggested and made some other calculations etc further down the road much easier but it has also thrown up another problem. Before I changed the test table to your suggestion I had one vehicle with many dates similiar to an excell matrix so I was able to create a continuous form and using conditional formatting where a test was out of date go red etc, now that I have the test table in the way you suggest I cannot for the life of me work out how to get the same result in my continuous form, the way the data is now doesn't show the dates from MOT, roadtax etc on the same line as the vehicle, how can I achieve this please.

 

[tonycl69]

Hi, solved the puzzle I discovered crosstab query for the first time and that solved it.