Mismatched data

[BigAl.NZ]

Hi Folks,

I am trying to make a database for the classic scenario of customers,
products, orders. Its been a wile since i played around with
databases.

I have four tables:

Customers(customer_id, address,first_name......
Products(product_id,distributor_code,manufacturer_ code......
Orders(order_id,date_customer_id)
Orders_Product(order_id, product_id,qty.....

I have two problems though, I get my list of products from about 6
distributors in csv/excel files. Each of these excel files are
slightly different, some have short_desc, long_desc, some just have
long_desc.

Whats the best way to go about importing this data? I tried with the
wizard, but it doesnt let you specify which column should map to which
field in the table.

The other problem, is when you go to update the prices, next week say,
how do you then import the file, and update the right items, adding
new ones where required etc, rather than just creating a set of
duplicates?

Finally would a invoice(report) for a given order just be the
foramtted result of a query?

Thanks,

-Al

[ARC]

I have the same setup. I used an excel spreadsheet file as an importing
template, then also had the column headings in the spreadsheet correspond to
fields in a temporary importing table. I then do the docmd.transfer
spreadsheet command, and reference the spreadsheet to import, and the
teporary holding table to store the values in. This works quite well. Then
you need some vba code that will cycle through all records in the temporary
import table. Search your permanent products table in this code, and if the
product number is found (or other unique field to the orders table), then
you update the price and description only. Otherwise, if the unique
identifier is not found in your orders table, you do an add.

If you don't want to reinvent the wheel, I have just such an application
that I sell on my website, that is for quote/invoices/products. If
interested, let me know and I'll leave a link so you can see screenshots,
etc.

Good luck!

Andy
<Bi******@gmail.comwrote in message
news:11**********************@n76g2000hsh.googlegr oups.com...

Hi Folks,

I am trying to make a database for the classic scenario of customers,
products, orders. Its been a wile since i played around with
databases.

I have four tables:

Customers(customer_id, address,first_name......
Products(product_id,distributor_code,manufacturer_ code......
Orders(order_id,date_customer_id)
Orders_Product(order_id, product_id,qty.....

I have two problems though, I get my list of products from about 6
distributors in csv/excel files. Each of these excel files are
slightly different, some have short_desc, long_desc, some just have
long_desc.

Whats the best way to go about importing this data? I tried with the
wizard, but it doesnt let you specify which column should map to which
field in the table.

The other problem, is when you go to update the prices, next week say,
how do you then import the file, and update the right items, adding
new ones where required etc, rather than just creating a set of
duplicates?

Finally would a invoice(report) for a given order just be the
foramtted result of a query?

Thanks,

-Al