How can I compare the column names from 2 tables and output them?

[mls via AccessMonster.com]

I have a requirement to place an alert if the field does not exist in my
table, tblStaging. Can any one guide me to reframe this query with error
handling messages?
I know it is not good practice to use select * but I need to do this as my
columns\field names change each time.


INSERT INTO tblStaging SELECT * FROM tbl_XL,tblDetails;

Thank you

 

[Jeff Boyce]

Access is a relational database. If your "table design" has the fields in
your table(s) changing frequently, you probably are committing spreadsheet
on Access.

If you'll provide a more specific description of your situation and some
example data, folks here may be able to offer you approaches that let you
take full advantage of the relationally-oriented features/functions found in
Access.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[mls via AccessMonster.com]

HI Jeff,. Here is my tbl structure..
1) tblStaging --- has the following columns...pid a_result b_result d_result
f_result run_date final_result
2) tbl_xl
pid a_result b_result d_result
234 2.3 2.4 2.5
345 3.1 2.4 3.3
456 2.5 2.3 2.6
3) tbl_details
pid run_date final_result
234 3/1/2010 positive
345 3/1/2010 negative
456 3/1/2010 positive
If my tbl_xl has all the columns that are in tblStaging then my automatic
system runs successfully. But if there is a new test like g_result then my
process is failing so I need to create an alert message to capture the exact
field name; saying g_result field doesn't exist. Then I can create another
field in my tblStaging table. This happens occationally when a new person run
the test with different field name in excel spreadsheet.
tbl_xl2
pid b_result f_result g_result
234 2.3 2.4 2.5
345 3.1 2.4 3.3
456 2.5 2.3 2.6

Hope this helps..

Access is a relational database. If your "table design" has the fields in
your table(s) changing frequently, you probably are committing spreadsheet
on Access.

If you'll provide a more specific description of your situation and some
example data, folks here may be able to offer you approaches that let you
take full advantage of the relationally-oriented features/functions found in
Access.

Regards

Jeff Boyce
Microsoft Access MVP

I have a requirement to place an alert if the field does not exist in my
table, tblStaging. Can any one guide me to reframe this query with error

[quoted text clipped - 5 lines]

Thank you

 

[Jeff Boyce]

As soon as I see repeating fieldnames ("a_result", "b_result", ...) I think
of ... spreadsheets! Again, you don't want to do that if you want to use
Access the way it's designed.

Consider looking into a relationally-oriented design for surveys ... I'm
guessing that "result" pertains to questions of some kind. You can find one
at:

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=3

And check up on "normalization" ... if your table structure allowed you to
add a new ROW when you had a new result, you wouldn't need to be maintaining
(i.e., messing with) ever-changing numbers of columns.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

HI Jeff,. Here is my tbl structure..
1) tblStaging --- has the following columns...pid a_result b_result
d_result
f_result run_date final_result
2) tbl_xl
pid a_result b_result d_result
234 2.3 2.4 2.5
345 3.1 2.4 3.3
456 2.5 2.3 2.6
3) tbl_details
pid run_date final_result
234 3/1/2010 positive
345 3/1/2010 negative
456 3/1/2010 positive
If my tbl_xl has all the columns that are in tblStaging then my automatic
system runs successfully. But if there is a new test like g_result then my
process is failing so I need to create an alert message to capture the
exact
field name; saying g_result field doesn't exist. Then I can create another
field in my tblStaging table. This happens occationally when a new person
run
the test with different field name in excel spreadsheet.
tbl_xl2
pid b_result f_result g_result
234 2.3 2.4 2.5
345 3.1 2.4 3.3
456 2.5 2.3 2.6

Hope this helps..

Access is a relational database. If your "table design" has the fields in
your table(s) changing frequently, you probably are committing spreadsheet
on Access.

If you'll provide a more specific description of your situation and some
example data, folks here may be able to offer you approaches that let you
take full advantage of the relationally-oriented features/functions found
in
Access.

Regards

Jeff Boyce
Microsoft Access MVP

I have a requirement to place an alert if the field does not exist in my
table, tblStaging. Can any one guide me to reframe this query with error

[quoted text clipped - 5 lines]

Thank you