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

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

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1

0
mls
3/18/2010 7:08:01 PM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
771 Views

Similar Articles

[PageSpeed] 39

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" <u55943@uwe> wrote in message 
news:a53488674b9a0@uwe...
>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
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1
> 


0
Jeff
3/18/2010 7:31:37 PM
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..


Jeff Boyce wrote:
>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

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1

0
mls
3/18/2010 7:58:26 PM
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.

"mls via AccessMonster.com" <u55943@uwe> wrote in message 
news:a534f917914d4@uwe...
> 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..
>
>
> Jeff Boyce wrote:
>>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
>
> -- 
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/201003/1
> 


0
Jeff
3/18/2010 8:19:37 PM
Reply:

Similar Artilces: