customers have their own product number

[Herr Li]

Dear all,

I am a new user of Access 2007.

In the samples which were given by the text books or the build-in Northwind
sample, the product number is always the same. It means when the company buy
the products from the suppliers or when they sell the products to their
customers, they use the same product number.

But the in real world, our company has our own product number for each
product, and almost all our customer has their own product number.
When placing the order to us, customer will use their own product number.

What I want to do:
In the order form, I first select the customer name or ID;
Then in the order_detail_subform, I can choose my customer's product number,
and our company's own prouduct number will appear in the next column...

Can anyone tell me how to create table and link tables to have this effect?

Your help is really really appreicated.

Regards

Li Haibo
Guangzhou, China

 

[Guest]

Build a translation table with CustomerID, Cust_ProdID, and ProductID.

 

[Jason Lepack]

Assuming you have a similar structure:

own_items:
own_item_number - PK

customer:
customer_id - PK

cust_items:
cust_item_number
customer_id
(There is a unique index on (cust_item_number, customer_id))


You need a cross-referencing table:

cross_ref:
own_item_number
cust_item_number
customer_id

You will need to determine what the business rules of the cross_ref
table are. In the system that I have been developing, I have two
unique indexes. The first is on (own_item_number, customer_id), the
second is on (customer_id, cust_item_number).

The reason for these indexes is this:
1) An own item can only be referenced by a customer once.
2) Each customer's item can only be referenced by one of our own.

Cheers,
Jason Lepack

 

[John W. Vinson]

Dear all,

I am a new user of Access 2007.

In the samples which were given by the text books or the build-in Northwind
sample, the product number is always the same. It means when the company buy
the products from the suppliers or when they sell the products to their
customers, they use the same product number.

But the in real world, our company has our own product number for each
product, and almost all our customer has their own product number.
When placing the order to us, customer will use their own product number.

What I want to do:
In the order form, I first select the customer name or ID;
Then in the order_detail_subform, I can choose my customer's product number,
and our company's own prouduct number will appear in the next column...

Can anyone tell me how to create table and link tables to have this effect?

You'll need a translation table, linked to your parts table and to the
customer table; it would have three fields, CustomerID (link to Customers),
your product number, and a Text field for the customer's product number. In
the Orders form, you could link the customer's product number and their ID to
this table to look up your product number.

John W. Vinson [MVP]

 

[Herr Li]

Hi, Jason,

Thank you very much for your suggestion.

I am really a green hand in Access.
I really need a sample database to follow.

Could you please make a sample Access database to demonstrate your method to
me?

Thank you very very much in advance.

Regards

Li Haibo
Guangzhou, China

 

[John W. Vinson]

Hi, Jason,

Thank you very much for your suggestion.

I am really a green hand in Access.
I really need a sample database to follow.

Could you please make a sample Access database to demonstrate your method to
me?

Thank you very very much in advance.

Regards

I'm sorry, but that is simply RUDE.

You're asking me to put in one or two hours of unpaid work so you can avoid
the effort of studying and thinking, and on top of that you can't even get my
name right?

Sorry. That's more than is called for in this *volunteer*, *free* newsgroup.

John W. Vinson [MVP]

 

[Herr Li]

Dear All,

Thank you very much for help and advice, and through the help of my
neigbour who was patient enough to teach me how-to for this "unpaid" job, I
have found a way to solve this problem by using VBA.
VBA is new to me, but I will keep learning.

Thank you once again for all the advice Karl, Jason and John gave me.

Regards

Li Haibo
Guangzhou, China

 

[Herr Li]

Hi, Jason,

Thank you for your help!

I am studying your sugestion now!

Regards

Li Haibo
Guangzhou, China

 

[Jason Lepack]

I emailed the database that I created when I originally answered your
question to you.

Please note the relationships between the tables in Tools-

Relationships.

Cheers,
Jason Lepack