Importing Table & Updating column/field

[Guest]

I need to import several tables and need help automating the process of
updating a column/field.

The database structure has an Order ID number for the combination of date
and consumer. So for example Peterson's total order on 3/1/2006 has an id
number of 231. Now the table I need to import has the records that would
make up Petersons total order for that date and several other dates. However
the table only has the consumers name and the date, not the corresponding ID
number. The ID number for all date & consumer combinations have already been
determined because some of the data (records that make up the total order)
has already been entered. Now I need to import that table into the database
and automate the updating of that Order ID field for all the records in my
table to be imported. Not sure how to do that. Please help

Does this question makes sense?

 

[strive4peace]

Hi Coastal,

Import the data into its own table

Use an APPEND query to move the data to the OrderDetail table

in the query, use the fieldlist for your imported table and
the fieldlist from the Order table.

Link the import table to the Order table on customer and date

For the append, get the OrderID from the order table and the
rest of the data from the import table

It would be a good idea to put this field in the
ImportDetail table:

DateCreate, date, DefaultValue --> =now()

when records are created, they will automatically get a
date/time stamp

you can use this field to make sure the number of records
appended matches the number of records in the import table

if it does not, you can use a query to find the unmatched
records and

1. first append orders that aren't there
2. then follow the above steps to append the OrderDetails


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*