Code strategy (1 Viewer)

[vassa]

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!

 

[vbaInet]

Pulling data from all those tables would be quite a task. I am thinking of two possible solutions here. First would be to create a union between all these tables, and then filter it based on your criteria. Or use a recordset to loop through the Tabledefs collection and filtering adn only saving the relevant records into another recordset as it moves through them. What are your thoughts? Does anyone else have any other ideas?

(I can provide code but lets see what the others think)

 

[vassa]

Pulling data from all those tables would be quite a task. I am thinking of two possible solutions here. First would be to create a union between all these tables, and then filter it based on your criteria. Or use a recordset to loop through the Tabledefs collection and filtering adn only saving the relevant records into another recordset as it moves through them. What are your thoughts? Does anyone else have any other ideas?

(I can provide code but lets see what the others think)

Thank you, vbaInet!
I had the second option in mind, but I don't have enough VBA power to pull this off yet (I don't do access for living). The code could loop through the table collection just looking at tables ending with "CAL", selecting records with the latests date for those unique combos "EquipmentID-CalID" from the other table. Once I have this recordset assembled I just need to add that extra "frequency" value to dates.
I had reply on http://www.utteraccess.com/forums/s...1928608&page=0&view=collapsed&sb=5&o=&fpart=1

I poste reply, my calibration tables contain calibration data which is very different from one type of equipment to the other, that's why I couldn't have just one calibration table with all records.

Thank you again!

 

[JPearson]

Quite a task is right....

Questions.

Each table has multiple CalIDs if I understood you correctly. Does each table contain multiple EquipmentIDs or is it just one ID per table?

Exactly what type of information are you looking for on #2? What sets the interval between calibrations?

 

[vassa]

Quite a task is right....

Questions.

Each table has multiple CalIDs if I understood you correctly. Does each table contain multiple EquipmentIDs or is it just one ID per table?

Exactly what type of information are you looking for on #2? What sets the interval between calibrations?

Yes, each table has multiple CalID's and mulptiple EquipmentID's . BUT all EquipmentID's of one EquipmentType. Example: Cement Consistometer (EquipmentType) Calibration Table has 7 different CalID's (PressureCal, TemperatureCal, TimerCal, etc), and there are 5 consistometers units in the lab (5 EquipmentID's). All these calibrations have different frequencies.

Interval for calibration is set by "EquipmentType-CalID combo", example: Consistometer Timer must be calibrated every 6 months. So it would be the same for all EquipmentID's (different units in the lab). But technician may choose today perform timer calibration, tomorrow pressure calibration on the same unit, hence different dates..

I need to know when this EquipmentID (unit) is due for the next CalID (Timer, Pressure, Temperature) calibration. Thank you for your help.

 

[JPearson]

VBaInet has the best idea I can think of for gathering all that informatoin into one place.

I think you would have to compare dates and concatenated equipid/calid for comparisons.

As far as the intervals, once that data is collected it could be compared to another table that contains the equipmenttype/caltype required interval to get the due dates.

 

[vassa]

VBaInet has the best idea I can think of for gathering all that informatoin into one place.

I think you would have to compare dates and concatenated equipid/calid for comparisons.

As far as the intervals, once that data is collected it could be compared to another table that contains the equipmenttype/caltype required interval to get the due dates.

OK. Here is the simplified structure of my database for the sample code:

EquipmentType: 1 and 2
EquipmentType 1 has Calibration Table "tblEquipType1Cal" with 2 different types of calibrations CalID1, CalID2
In the lab there 2 pieces of EquipmentType1: EquipmentID1, EquipementID2

EquipmentType 2 has its own very different (structure-wise) calibration table "tblEquipType2Cal", with 2 calibration types CalID3, CalID4.
In the lab there are 2 pieces of EquipmentType2: EquipmentID3, EquipmentID4

Table "tblIntervals" with time intervals contain this data:
EquipmentType1 - CalID1 should be performed every A months
EquipmenType1 - CalID2 should be performed every B months
EquipmentType2 - CalID3 should be performed every C months
EquipmentType2 - CalID4 should be performed every D months

 

[vbaInet]

For you to have 12 tables with the same structure makes me think your database hasn't been properly normalized. Is there any particular reason why you have 12 different tables? It would have been alot more logical (and efficient) to have four tables to handle them nicely. Like below:

tblEquipTypes
--------------------
EquipTypeID (PK) - Autonumber or Number - Values: 1, 3, 4
EquipType - String - Values: "EquipType 1", "EquipType 3", "EquipType 4"

tblEquipments - To hold all variations of equipments
--------------
EquiID (PK) - Number - Values: 1, 2
EquipType_FK (FK) - Linked to tblEquipID in tblEquipTypes
EquipDescription - String (Optional - just for your notes)

tblCalTypes
----------------
CalTypeID (PK) - Number or Autonumber - Values: 1, 2, 3, 4 (If Number)
CalType - String - Values: "Cal 1", "Cal 2", "Cal 3", "Cal 4"

tblCalibrations - Inventory of all the calibrations done per equipment
-----------------
CalID (PK) - Autonumber
CalTypeID_FK (FK) linked to CalTypeID in tblCalType
EquipID_FK (FK) linked to EquipID in tblEquipments
CalDate - String or Date - Default to CurrentDate (if you wish)
CalNotes - String - (Optional - again, just for your notes)

With the above setup it would have been a breeze getting what you want. Consider it for future. Any thoughts?

In the meantime, we have to work with what you have. Give me some sample data from from one your 12 tables. Four or five records will do. Then also give some sample data from your calibrations table(s).

 

[vassa]

People at some other forums pointed out that I shouldn't have 12 tables with similar structurre, but my problem is that structure is not that similar, because in my tables technicians enter all calibration data, so instead of just one or few CalNotes I have 50 different "CalValues" and they differ from one EquipmentType to the other.

For example one piece of equipment has 8 point pressure calibration: it requires to input 8 calibration pressures (8columns) then 8 gauge readings observed (another 8 columns), 8 readings on the recorder and 8 readings on some other screen. The point of calibration for all of them to match across the point. As techs enter them in form they all highlight red or green. So just this one type of calibration required me to add 32 columns to the table.

Some other EquipmentType may not even have pressure calibration but something else as extensive, so the columns are totally different.

Compiling all variations under one table will create an enormous table. That's why I couldn't quite figure out how to normalize this data to fit it under one "roof" instead of having 12 tables.

I'll post example data in the next post. Thanks for the advice though, I figured normalization pretty much on my own, but I know understand how important it is.

 

[JPearson]

I see what your saying.

Its closer to each table represents a specific type of equipment and those related Calibration types and points.

Still looking to see if I cant find something to pull the data from each table quickly tho..

 

[gemma-the-husky]

you ought to rethink this

if you have 12 identical/similar tables - then you should just have one table, with the addition of a suitable identifier to distinguish the records

 

[vassa]

tblCalSchedule
____________
ID (PK)
Category (FK) (this is my Equipment Type ID)
CalID - Text
Frequency - Number (Number of days)

ID----------- Category ----------------CalID----------- Frequency
1 ----Atmospheric Consistometer ----CheckTimer ----------60
2 ----Atmospheric Consistometer ----CheckTemperature ---90
3 ----Atmospheric Consistometer ----CheckCupSpeed -----120

tblAtmConsistometerCal (table for Category "Atmospheric Consistometer")
___________________
ID (PK)
CalibrationDate Date/Time
MakeModel (FK to tblAssets) (This is my Equipment ID)
CheckTimer Yes/No
CheckTemperature Yes/No
CheckCupSpeed Yes/No
I removed all numeric columns, DoneBy,notes, etc

tblAssets
_________

ID (PK)
MakeModel - Text
Category (FK to table tblCategories with all possible Categories aka Equipment Types)

I hope this is enough information. Please, note that user checks a box for any calibration he wants to do, when he/she checks the box on the form, data entry fields for that type of calibration become available, otherwise they are hidden. So one record line in the table tblAtmConsistometerCal can have 1, 2 or 3 different calibrations done, of course they will be on the same day, but the code should see this one date for all different calibrations separately.

 

[vassa]

you ought to rethink this

if you have 12 identical/similar tables - then you should just have one table, with the addition of a suitable identifier to distinguish the records

Gemma-The-Husky, please see my post #9

 

[vbaInet]

Gemma-The-Husky, please see my post #9

I'm positive gemma-the-husky would have read your comments before making those suggestions

I will try out a possibe solution later.

 

[vassa]

I'm positive gemma-the-husky would have read your comments before making those suggestions

I will try out a possibe solution later.

I just counted the number of columns for my biggect Category calibration table - 126 columns! Most of those columns are irrelevant to other categories. I don't know what Access limit for the max number of columns per table, but I am sure I will be pushing it if I try to drop all 12 tables into 1.

I appreciate everyones help and attention to my problem.

 

[vassa]

I'm positive gemma-the-husky would have read your comments before making those suggestions

I will try out a possibe solution later.

I just counted the number of columns for my biggect Category calibration table - 126 columns! Most of those columns are irrelevant to other categories. I don't know what Access limit for the max number of columns per table, but I am sure I will be pushing it if I try to drop all 12 tables into 1.

I appreciate everyones help and attention to my problem.

 

[dcb]

I just counted the number of columns for my biggect Category calibration table - 126 columns! Most of those columns are irrelevant to other categories. I don't know what Access limit for the max number of columns per table, but I am sure I will be pushing it if I try to drop all 12 tables into 1.

I appreciate everyones help and attention to my problem.

Dave was suggesting you store these as rows not columns......

 

[vassa]

Dave was suggesting you store these as rows not columns......

Not sure what you mean since rows constitute different records, please elaborate a bit more.

 

[RainLover]

Gemma The Husky is correct.

This database will run into so many problems as it develops.

Normalise your Structure.

Just my opinion

 

[c_smithwick]

' Create two tables, one containing frequency data with the following fields named "tblFreq"
' Equip_ID (Long Integer - equipment ID that uniquely identifies a piece of equipment and relates to one or more tables with other equipment data)
' Cal_ID (Long integer - calibration ID that uniquely identifies a calibration operation and relates to one or more tables with other calibration data)
' Freq_Number (Integer - number of intervals between calibrations)
' Freq_Interval (Text - string data containing calibration interval, e.g. "m" for months, "d" for days, etc ...)
'
' Another table, named "tblData" will contain the calibration date data and have three fields
' Equip_ID
' Cal_ID
' Date (Date last calibration of type Cal_ID was performed on Equip_ID)
'
' Collect your data in tblData
'
' To determine the next due date use the following function
'
Public Function dueDate(lngEquipID As Long, lngCalID As Long) As Date
Dim rstData As DAO.Recordset
Dim rstFreq As DAO.Recordset
Dim intFreq As Integer
Dim strFreq As String

Set rstFreq = CurrentDb.OpenRecordset("SELECT tblFreq.* FROM tblFreq WHERE (((tblFreq.Equip_ID)=" & lngEquipID & ") AND ((tblFreq.Cal_ID)=" & lngCalID & "))")
'This should contain only one record
intFreq = rstFreq![Freq_Number]
strFreq = rstFreq![Freq_Interval]
rstFreq.Close
Set rstFreq = Nothing
Set rstData = CurrentDb.OpenRecordset("SELECT tblData.* FROM tblData WHERE (((tblData.Equip_ID)=" & lngEquipID & ") AND ((tblData.Cal_ID)=" & lngCalID & ")) ORDER BY tblData.Date DESC")
'This will order your data with the most recent date being the first record
dueDate = DateAdd(strFreq, CDbl(intFreq), rstData![Date])
rtsData.Close
Set rstData = Nothing
End Function