Insert fields dynamically into database from a staging table

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

-- 
Message posted via http://www.accessmonster.com

0
mls
12/4/2009 3:20:45 PM
access.forms 6864 articles. 2 followers. Follow

5 Replies
903 Views

Similar Articles

[PageSpeed] 17

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.
-- 
Milton Purdy
ACCESS 
State of Arkansas


"mls via AccessMonster.com" wrote:

> 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
> 
> -- 
> Message posted via http://www.accessmonster.com
> 
> .
> 
0
Utf
12/4/2009 4:36:01 PM
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

golfinray wrote:
>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

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

0
mls
12/4/2009 5:24:19 PM
On Fri, 4 Dec 2009 08:36:01 -0800, golfinray
<golfinray@discussions.microsoft.com> wrote:

>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.
>-- 
>Milton Purdy
>ACCESS 
>State of Arkansas
>
>
>"mls via AccessMonster.com" wrote:
>
>> 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.

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 [MVP]
0
John
12/4/2009 7:48:18 PM
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.


John W. Vinson wrote:
>>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]
>>> test_results tables fields to database fields to run my dynamic insert
>>> process.
>
>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.

-- 
Message posted via http://www.accessmonster.com

0
mls
12/4/2009 8:14:46 PM
On Fri, 04 Dec 2009 20:14:46 GMT, "mls via AccessMonster.com" <u55943@uwe>
wrote:

>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.
-- 

             John W. Vinson [MVP]
0
John
12/4/2009 8:58:42 PM
Reply:

Similar Artilces:

HELP Error: Trying to populate userform fields from access databas
Hi All, I have been struggling with this since long, I would appreciate if anyone can help me with getting this done. Basically I want to pull information from access database and populate my excel userform fields. For eg: If I input a Student Id field I want to populate the Name and Phone number for that student from access database, Here is the code that I have so far, But it gives me errors.. Please guide me through this Private Sub StudentId_AfterUpdate() Dim cnt As ADODB.Connection Dim rst As ADODB.Recordset Dim strSQL As String strSQL = "SELECT Name, Phone FROM...

Price from database does not showup correctly in POS
When I try to ring up items in the POS the sale price of some items does not showup correctly in POS. It shows a totally different sale price which is sometimes less or sometimes more than the sale price in the database. I always bachup my database and also refresh the Sql server but the problem continues. Does anybody has a solution to this. Regards, Nitin Try reindexing the DB in SO Administrator - Database/Reindex Other than that, can you provide more details? Have you set up customers with Discount Percentages or Price Levels? -- Glenn Adams Tiber Creek Consulting http://www.t...

Output intersection of table
Hi, I have TableA of data which needs checking row by row. TableB on a different sheet contains the values which need to be output. In TableA need to check Col A and then the value in Col C and return the intersecting value from TableB into Col D (in TableA). e.g TableA: ColA ColB ColC ColD Blue Man Car Red Auto Truck TableB: Red Blue Black Car 9 5 2 Truck 7 4 3 Therefore: In Row1 in TableA the value in ColD needs to be 5. In Row2 in TableA the value in ColD needs to be 7. thanks in advance, Hamish --- Message posted from http:...

Close Event for the Database (?)
Is there a close event for the database? I have forms referencing other forms, and if the user closes Access without backing out of the forms, an error message pops up. It would be hand to just trap that error in an "On close" event for the entire db. Of course that might be a horrible thing when other errors are happening on close.... -- croy ...

Query fields
Is it possible to write a criteria where the value of an empty field is "0.00"? Background: I have three queries with different customer account groups. Not every salesperson has customer accounts in every accountgroup - so, he will not shown up in that query. But he has accounts included in another query. Now, I would like to get a sum of commission earned by each salesman calculated from all three queries together. Since the salesman has no record in one query the total sum of that specific salesman is not shown. Any idea how to solve that problem? Thanks Klaus On Wed, 29 A...

Creating a table
There's probably an easier way to do it but... I have a series of numbers in column A (150 in all) and a series of letters in column B (22 in all). In total there are 4800 rows. What I am trying to do is create a table with the number down the left hand side and the letters across the top. In each cell within the table I need to count how many times the combination of number/letter appears. Eg: A B C 201 0 2 2 202 3 0 1 203 3 6 4 I tried combining the number/letter into a single text field using the CONCATENATE function then filtering ...

Update one table from another
I am trying to update one table that has one record for each employee(table 1) with available vacation time. The other table records every time off request(table 2) and how much time they want off. I have the update query and it works fine. The problem is that everytime it is ran every requested time off amount(from table2) is subtracted from the available time(table1) again and again. I want the records for requested time(table2) to update the employee available time off(table1) only once, but keep the records on the table as that is the basis for a report. Thank You, Brett ----=...

Finding all queries which use a table
Hi, Does anyone know of a tool that can scan all queries in a database and find if a certain table is used? I have a table called tblCustomerRollup which is old and outdated. I want to see which of the 500 queries in my database use this table without opeing every single one of them? Thanks, -- Chuck W Chuck Sounds like a variation on Search/Replace. Try searching online for "Database Documenter" as a starting point. A couple of the commercial tools I've used include FMS, Inc.'s Total Access Analyzer and Black Moshannon's Speed Ferret. There are a lot of fr...

Keeping a table in a form editable and checking that fields are filled in before allowing a save
Firstly apologies if this is the incorrect forum but I was looking for a general word forum and could not find one. Please point me to one if one exists. I am trying to create a form where I want to specify what items need to be filled in. (Review minutes from design reviews). I want to make certain fields mandatory like the date, attendees and check list used and want to block saving of the document with a warning until they are filled in. Is there a way of doing this? Also as a part of the review actions are filled in to a table. depending on how many actions there are the table...

Error 2455 Closing Access 2007 database with form open
I have a form with a subform that is requeried when you select a new key for the main form from a combo box. Everything works fine - usually. But sometimes if you have the form open when you close the database down you get the following error message (twice) in a pop up. You say OK (twice) and the database closes OK "2455 you entered an expression that has an invalid reference to the property form/report" If I close the form before the database I never get the error. If I do not touch the form before you close the database I don't get the error. If I update a field by t...

Inserting Excel into Access Reports
Office XP Have a great Access application that produces a nice template (headers & footers) report into which I'd like a spreadsheet inserted before going to the printer. In the past, I'd just print the Access reports, then reload them into the inkjet printer and run the Excel spreadsheets as needed. The heat of the new color laserjet turns the paper grey if it runs through too often, so it's time to get the reports printing on one pass. Any suggestions would be welcome. I've of course also got Word XP, MS Publisher XP, as well as Adobe Acrobat, if anyone thinks it m...

what is the function and name is of the symbol in each table cell.
Under Paragraph I clicked the Show/Hide Symbol icon so I can now see a symbol at the end of each text within a table cell. I wondered what that is so I tried to use Help to find out. I did find help that mapped a word (like paragraph) into a symbol. But I can't find anywhere where if I know the symbol it will tell me the meaning. Can you tell me how to find such info? Or maybe you can tell me what the function and name is of the symbol in each table cell. Thanks I'm sorry, I meant to sent this to the Word group. Of course, I wouldn't mind getting the info...

Beginner
Hi ! I am a beginner to databeses, and particulary to MS Access 2003. (althought I passed the first lessons) I want to make a small database with possibility to grow up. (max. 100 entries for a table) I think that in my case the data must be structured like loop into loop. Like that: Year: 2000 2001 2002 2003 etc. Plant: plant_1 plant_2 plant_3 etc. Generator: gen_1 gen_2 gen_3 etc. So for every Year it must exist some Power Plants, and for every Power Plant it must exist some Generators. I already have ma...

Change of field separator
When I open CSV files in Excel all data is put in one column. Can anyone tell me, where I change my set up, so I get another field separator? Please be specific, because my Excel is a Danish version, and sometimes I have a hard time following the English instructions. Thanks! Jane Hi you specify this in the Windows regional settings. Another idea: - rename your *.csv file to a *.txt file - now open it with excel. The Import wizard should appear and should allow you to specify a different delimiter "Janepige" wrote: > When I open CSV files in Excel all data is put in one co...

Zero filling a number field
I have an auto number field that I want to zero fill to six digits but can't figure out how. Any ideas? You don''t say where you're trying to do this, but basically Format(YourAutoNumber, "000000") pdlginternet@aol.com wrote: >I have an auto number field that I want to zero fill to six digits but >can't figure out how. Any ideas? -- There's ALWAYS more than one way to skin a cat! Answers/posts based on Access 2000/2003 Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200711/1 You can't do that wi...

FP: Couldnt close table
We have been receiving this error on two separate PCs after a recent upgrade to 7.5. Our version is 7.50g43 (service pack 5). At first we thought it was isolated to one PC now a second PC is having the same error. On the first PC, I ran new network cable, installed a different network card with no help. This PC is Win98, 64MB RAM, 600Mhz. The second PC has 128MB RAM also Win98. I don't believe switching to XP is an option right now. This message has appeared in the payables module on both machines. But it has also appeared in receivables as well. Not in GL or Payroll. I...

Right click in Pivot Table or on Entire Column
I have added items to the right-click menu that popups up when you have a cell or cells selected. But when you are in a Pivot Table or have an entire column selected the right-click popup is different. Is there a way to add an item to the right-click popup menu when you are in a Pivot Table or have an entire column selected? Thank you for your help. Steven Never mind. This one was right in the help section. I should have looked first. Thank you, Steven "Steven" wrote: > I have added items to the right-click menu that popups up when you have a ...

Same Table cannot be the child table in two nexted relations...
I've been setting up a schema with the XML Designer in VS .NET 2003. The designer lets me set up a lot of things including a complex type that contains an unnamed complex type called modified. When I try to preview the dataset, I get an error described as "The same table (modified) cannot be the child table in two nested relations. I've run into this before with a different complex type, and I've changed the name of the instance of the type in the various elements it's used, and the problem goes away. In this case, problem is an unnamed complex type, so it only a...

Pivot tables and Macros
I was looking to be able to manipulate (ie change selections from the drop downs, not change the fields in the table or anything)and print from a pivot table using a macro. So how do I go about doing that? When I set up a macro by recording the actions that I want to do, I always get an error message when I try to run it as a macro. What specific steps are you recording, and what error message do you get? Can you click the Debug button, and see the line of code that is causing the problem? Dust For Eyes wrote: > I was looking to be able to manipulate (ie change selections from the >...

Annoying problem with lookup field
On a client, I have a MOSS 2007 portal with two lists which are not wroking as expected. One contains people and the other one contains a lookup field based on data from the first list. When someone which hasn't got administrative rights logs in and tries to see the properties of any element in the second list (on DispForm.aspx), they can't see the lookup field value, and if they try to edit the element, the dropdown control used to choose a person from the first list appears empty. Administrator don't have this problem. The users having the problem have collaboration rig...

How to copy aQuery to a new Table?
I have a database in a Table, a report based on that same Table and a Query based on that Report. After two months or so I like, after some new data input, to save the Table into a new Object Table. What is the best way for the Report and Query to follow the new Table whitout recreating the original Report & Query? Thankyou for your comments. I use MS Office Access 2007. Joe T >>I have a database in a Table, a report based on that same Table and a Query based on that Report. Your phrasing is wrong when it comes to the elements of an Access database. A dat...

Pivot Table in Excel
hi, I have a problem using the pivot Table in excel 2000. Earlier the location of the pivot table in excel was pointing to say c:\sales.mdb. Now the location has changed to D:\Sales.mdb. someone please tell me the place to change in the excel to reflect the same. So that upon refreshing the document i can see the latest data. Currently i am getting a error message when i try to refresh, but its not prompting to change to alternative location. If i edit the excel file in a notepad...i can see the location pointing to c:\sales.mdb. Thanks in advance for any help regarding this..expect...

how to insert borders on flyer within Microsoft publisher?
Please help, I'm trying to insert a border around a flyer...I'm using Microsoft Publisher. Thanks, Harriet Is it a clipart border, Borderart or a simple rectangle? What problems are you having? What version Publisher? Any border you insert should be sent to the back so it does not interfere with your main design. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Harriet" <Harriet@discussions.microsoft.com> wrote in message news:7E2ED4D8-CC09-497F-A17C-44AE41F951C9@microsoft.com... > Please help, I'm ...

obtaining data in text form from a table
Hi all, I like to be able to obtain the dates in a text format from the table below. ie Test2 8-Feb Test5 4-Feb,8-Feb Test6 4-Feb,5-Feb, 9-Feb Do I need to do this by macros and if so, any help would be appreciated. Table Care Recipient Surname 4-Feb 5-Feb 8-Feb 9-Feb Test1 Test2 8-Feb Test3 Test4 Test5 4-Feb 8-Feb Test6 4-Feb 5-Feb 9-Feb Vlookup should do what you want, as in: =vlookup(a2,Table,2,false) Adjust the ranges t...

How to write a new entry in a combo box to its underlying table
Dear Access 2007 VBA Gurus, I have a assets database (rather uncreatively named "Assets"). I use a form (named "Asset Acquisition Input Form New") to enter new assets. The "Manufacturer" field (combo box name "Manufacturers_ID) on this form is a lookup to a Manufacturers table. What I want to happen is when I enter an item that is not in the lookup list, I want a message box to prompt me to add the new entry to the underlying table, or to cancel and select an item from the list. I have no trouble with the MsgBox command itself. What I don&...