10
I have found multiple examples of VBA functions to test for the existance of a column in a table and getting a message return. My need is a bit different so I better try and give the sequence of events that I need to try and capture.
1. I use 1 query to pull a dataset with a EVENT_ID, UNIT_ID, a UNIT_STATUS and TIME stamp for that status for a given date/time range. Call it UnitHistory.
2. I use a cross-tab query against UnitHistory to create a structure that puts the EVENT_ID and UNIT_ID as the row elements and the UNIT_STATUS as the column headers with the TIME value as the value, giving me all of the unit status times for a given unit on an event in a single row.
3. Since a cross-tab cannot make or append another table, I have a third query that uses the cross-tab query as its data source to insert the values into a table. It also performs a function to turn the text time stamps into actual date/time fields for calculations later in the process.
The problem...there are certain UNIT_STATUS fields that are not always present, but I have to account for them in case they are. The simple solution would be a If/Then/Else scenario, If the value (column) of "AD" exists in the cross-tab table, insert the time stamp into the apprpriate column in the third query. If the "AD" value does not exist insert a NULL value in the column, a place holder in essence. Does that make sense?
