467,879 Members | 1,301 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 467,879 developers. It's quick & easy.

Using an EXISTS function to determine if a column exists in a table query

Jerry911
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?
Sep 27 '11 #1

✓ answered by jimatqsi

You may not realize that you can specify the column IDs for a cross-tab query. Assuming you know all of the possible UNIT_STATUS values you should specify them in the properties of the cross-tab query. That way every record set for that query will always have the same columns.

In the query builder just right-click in the upper half, go to properties and look for column headings.

Jim

  • viewed: 5162
Share:
4 Replies
Expert 1GB
You may not realize that you can specify the column IDs for a cross-tab query. Assuming you know all of the possible UNIT_STATUS values you should specify them in the properties of the cross-tab query. That way every record set for that query will always have the same columns.

In the query builder just right-click in the upper half, go to properties and look for column headings.

Jim
Sep 27 '11 #2
Jerry911
Even if there is no associated time stamp for that value? I will have to explore that.

Thanks.
Sep 27 '11 #3
Jerry911
Thanks Jim!

That fixed my issue and will actually help in several other reports I run regularly. Thanks a million!

Simple solutions are my favorite kind.

-Jerry
Sep 27 '11 #4
Expert 1GB
So glad to be helpful. An online forum is a wonderful thing. :)
Sep 27 '11 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Heist | last post: by
2 posts views Thread by ree32 | last post: by
4 posts views Thread by ken | last post: by
reply views Thread by jack112 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.