Appending a record

[LCallesen]

Hi Everyone-
I am very new to Access, so I apologize if this is an obvious question.

I have an Access database set up that imports data from a .txt file to
a table using a macro. This part works fine. However, I now need to
go back and append each record with new columns of information. I have
this information all set up in .txt files as well, but I can't seem to
find information to allow me to import this data without creating a new
record. Also, I do have a field, "Batch ID", in the existing table
that I can add to the .txt file to identify which record to append to.

Any help would be appreciated. Thanks in advance...

Lindsey

 

[Roger Carlson]

Although there are a number of ways to do this, probably the simplest is to
import the text file into a new table (having the Batch ID is essential).
If BatchID is the primary key of your original table, you can Join your new
table to the old table and run an update query to copy the data over.

Hope this helps. If not, please post back, listing your table and field
names.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[LCallesen]

Hi Roger-

Thank you so much for all of your help so far. I did exactly what you
suggested. Everything seemed to work (aka, I did not receive any
errors), but the query did not appear to update the fields. I brought
the new text file into a temporary table as you suggested, which worked
fine.

This is where my knowledge really gets fuzzy. I can't be 100% sure
that I joined the tables correctly, or that my update query is working
as it should.

Here is a little more background on my project:
I am creating a database to keep track of information from a chemical
process (ie. temperatures, weights, etc...) Some of the information
can be automatically read in, while others have to be manually entered.
Some of the automatic information is available as soon as the batch
starts, and is imported to create the new record. Operators can then
enter most of the other manual data while the process is running via a
form. However, toward the end of the process, there is more
information that automatically comes in and has to be added to the
database and associated with that particular batch. This is the second
..txt file. The common field is named "Batch ID" and the fields that I
am updating are "Solv GAL", "PT LB" and "Acid LB". These names are the
same in both tables.

If you need more information, please let me know. Thanks (again) in
advance...

Lindsey

 

[Roger Carlson]

OK. I don't know what you're doing wrong but try this for your update
query:

UPDATE Table1 INNER JOIN Table2 ON Table1.BatchID = Table2.BatchID SET
Table1.[Solv GAL] = [table2].[Solv GAL], Table1.[PT LB] = [table2].[PT LB],
Table1.[Acid LB] = [table2].[Acid LB];

In this example, I'm using "Table1" and "Table2", but you should use your
actual table names.

Give this a try and see what happens.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[LCallesen]

Hi Roger-

I got it! Thanks so much for all of your help...

Lindsey

 

[Roger Carlson]

Great. Glad I could help.

--Roger