import data

[Li Haibo]

Hi, everybody:

I am now using Access 2007, and I have one table and its fields as
followings:
Product_Num (Text, no duplicate)
Customer_Num (Text)

I finished filling the data in the Product_Num filed, and there are about
500 records.
Now, the filed Customer_Num is empty at the moment.

One of my colleague has a MS Excel file which store all the Product_Num and
Customer_Num data inside.

Is there any way that I don't have to type the Customer_Num one by one
manually, but just import the data from Excel into the table without
destroying the original data I already entered?

Thanks for your help.

Regards

Li Haibo

 

[Guest]

You can either do an update or append query based on how you will store the
information. Your table structure as you have it will only allow you to
have one customer for each product. I would expect you to have more than
one.
What I think you need is three tables set up in one-to-many relationships.
Product and Customer tables having a primary key field in the one relation to
the Prod_Cust table having foreign key fields matching the Product and
Customer tables.

 

[Li Haibo]

Hi£¬Karl,

Thanks for reply.

At the moment, in this project, it is simple: one customer for each product.
It is really so simple.
What I really know is that is it necessary to write any Macro or VBA code to
make the update or append query.
I am totally new to Access, and sorry for this question if it is quite
naive.

Thanks

Li Haibo
China

 

[Guest]

Hi 'Hi' [sorry :-D],

If the DB is just that simple I would import or link? the Excel file as an
Access table and use it for a source tbl in a new update query.

1. Import/Link the Excel file as a table
2. Create a new update qry with the new Access/Excel tbl and the existing
Access tbl as the sources and create a one-to-one relationship between the
"Product_Num" fields in both tbls.
3. Make the criteria on the destination Access tbl "Is Null".

Preview the results before running the qry live but, that should get you
what you're looking for...