I have 30 tables in my Access database, 12 of them named ****CAL. Each of those 12 tables contain calibration records for equipment (hence name ending with "CAL"). All 12 tables have columns: Date, EquipmentID and CalID. Also I have a table with different equipment and accosiated calibrations with the same columns: EquipmentID and CalID plus column Frequency for each calibration for each piece of equipment. I'd like the code to do the following:
1. Query all tables named ****CAL for the latest calibration record for each different EquipmentID and CalID. (Basically for each unique combination of EquipmentID and CalID; each EquipmentID can have more than one possible type of calibration "CalID"; I could make a table with all these combintations for the code to refer to)
2. Then make a table with all these different EquipmentID and CalID's and add Frequency to those latest calibration dates so I could have information when all these different pieces of equipment are due for next particular calibrations.
Please, help me with the strategy for doing this. If possible, provide code examples. Hope it's not too confusing. Thank you very much in advance!
1. Query all tables named ****CAL for the latest calibration record for each different EquipmentID and CalID. (Basically for each unique combination of EquipmentID and CalID; each EquipmentID can have more than one possible type of calibration "CalID"; I could make a table with all these combintations for the code to refer to)
2. Then make a table with all these different EquipmentID and CalID's and add Frequency to those latest calibration dates so I could have information when all these different pieces of equipment are due for next particular calibrations.
Please, help me with the strategy for doing this. If possible, provide code examples. Hope it's not too confusing. Thank you very much in advance!