Insert fields dynamically into database from a staging table

[mls via AccessMonster.com]

Hi all,
In our lab when ever a test is performed results are stored in test_results
table ( 10 records at maximum) and with click of a button these results
should be loaded to database, but I have any issue here, each time the
test_result table will have different field names ( one time it will have
test1 , test2 , test3 with labid, next time it might have test1, test4, test5
and labid) so how can I load these fields from test_results table into
database auomatically. Datebase has all these fields so how can I map the
test_results tables fields to database fields to run my dynamic insert
process.

Thanks in advance

 

[golfinray]

I would use an update query. You simply need to add an ID to the database
table, like your labid. On the query grid pick the table and the table you
want to update. Join them on the labid and then add the fields to update. Or
use update in a sql query.

 

[mls via AccessMonster.com]

What if my labid are new? Because of that I have to use INSERT instead of
UPDATE. Also my column names are little different in test_results table and
database table.


We will have new labid most of the times. I created a composite key with
labid and testno so my table allows upto 4 recods for each labid.

Thanks

I would use an update query. You simply need to add an ID to the database
table, like your labid. On the query grid pick the table and the table you
want to update. Join them on the labid and then add the fields to update. Or
use update in a sql query.

Hi all,
In our lab when ever a test is performed results are stored in test_results

[quoted text clipped - 8 lines]

Thanks in advance

 

[John W. Vinson]

I would use an update query. You simply need to add an ID to the database
table, like your labid. On the query grid pick the table and the table you
want to update. Join them on the labid and then add the fields to update. Or
use update in a sql query.

I'll have to disagree with Milton here. Your table structure IS WRONG.

"Fields are expensive, records are cheap" - if one test_result can contain
many results, you need *two tables*, in a one to many relationship. Rather
than a *field* for test1, test4, test5 you would add new *records* for each
test.

 

[mls via AccessMonster.com]

Never mind I am going to create dummy columns for all the test not performed
and dump all the field values into database.

I don't think my table structure is wrong. Each specimen can be tested 2 - 3
times for same group/set of test ( test1 or test2 or test3 ) or different .
So I need to store all the values for each test.
Ex: test1 can be glucose, test2 cholestral etc. Next time( TESTNO : 2) lab
tech may repeat the test for same sample but this time they might check for
test3:Anemic along with test2:holestral. So I need to save all these TESTNO1
and TESTNO2 group values for the same person in database.
My test_results( staging table) will have TESTNO1 first time and when lab
tech uploads values my test_result table will have second set of test values.

I would use an update query. You simply need to add an ID to the database
table, like your labid. On the query grid pick the table and the table you

[quoted text clipped - 11 lines]
I'll have to disagree with Milton here. Your table structure IS WRONG.

"Fields are expensive, records are cheap" - if one test_result can contain
many results, you need *two tables*, in a one to many relationship. Rather
than a *field* for test1, test4, test5 you would add new *records* for each
test.

 

[John W. Vinson]

Never mind I am going to create dummy columns for all the test not performed
and dump all the field values into database.

I don't think my table structure is wrong.

It is indeed wrong.

Each specimen can be tested 2 - 3
times for same group/set of test ( test1 or test2 or test3 ) or different .

Exactly. A many (specimens) to many (tests) relationship.

So I need to store all the values for each test.

Sure. That's what a database is for!

Ex: test1 can be glucose, test2 cholestral etc. Next time( TESTNO : 2) lab
tech may repeat the test for same sample but this time they might check for
test3:Anemic along with test2:holestral. So I need to save all these TESTNO1
and TESTNO2 group values for the same person in database.

You need to model this data with three tables: Specimens (SpecimenID, related
to a table of Patients I presume); Tests (TestNo, TestName - e.g. Cholesterol,
or Glucose, or whatever); and Results, with a field for SpecimenID, for
TestNo, and one or more fields for the result of the test. If a specimen is
tested for three values, there will be three records in this table; if it's
tested for nine, you'll have nine records.

My test_results( staging table) will have TESTNO1 first time and when lab
tech uploads values my test_result table will have second set of test values.

You can append to the Results table from the technician's upload.