subforms for multiple customers and products

[Stephanie T.]

I know this type of question has been asked, but I have gone through the forum (as well as others) and have not found a workable answer yet.

I have a table with all of our Customer information and a form directly bound to that. I have a table with all of our Product information and a form direclty bound to that.

What I would like to do is have a subform on the customer table that lists all of our products that my data entry person can enter information relating to when that specific customer picked up each individual product, the date it was picked up and when that customer discontiuned carrying it. I have created a "synch" table (an interum table) that can carry the product information that is entered into this subform. The products listed in this table are bound to the products originally entered into the Products table by the Products form.

The problem that happens, is that I don't seem to be able to create something that will store a list of each product per customer, and make it easy for the data entry person to enter. I think my querys may be to blame.

All help would be greatly appreciated,
Stephanie

 

[Alexandre]

If I understodd well, you are facing a many to many relationship (a products can be related to various customers and one customer to various products) requiring 3 tables:

TblCustomer
-CustomerID (primary key)
-CustomerName
...

TblProducts
-ProductID (primary key)
-ProductName
...

TblOrders
-ProductID
-CustomerID
-OrderDate
...
(primay key here is made of the two fields CustomerID and ProductID)


Relationships:
TblCustomer: 1____Many: TblOrders: Many____1: TblProducts


Then you would make a form based on TblCustomers, add to it a subform based on TblOrders, and in this subform use comboboxes based on TblProducts for your users to pick a select products from.

Does this help?

 

[Stephanie T.]

Dear Alex,

Thanks for the help, it doesn't quite get me where I need to go. Here is what is happening. We have multiple customers and multiple products, OK we know that already. Each product needs to have it's own date the customer took it on for their inventory (these aren't orders, but a log of which distributors actively carry which items). Then each product also has a date the distributor picked up each individual item or discontinued each individual item.

I can't seem to get all of the individual information connected with the individual products, and then have those individual products connected with the individual distributors.

I have created three tables:

Distributors:
-DistributorID (Primary Key)
-etc other information not necessary for this part

Products:
-ProductID (Primary Key)
-etc other information not necessary for this part

ProductSynch:
-CustomerID (Primary Key)
-DistributorID
-ProductID
-Picked up (yes/no)
-Date carried
-Date discontinued

I created a seperate Primary Key for this table for simplicity. I then created a form from the Distributor Table and put in a subform from the ProductSynch table (without the DistributorID showing in that form).

I guess what I am looking for is that in the ProductSynch table the information should be logged so that it would read something like this:

Distributor Product Picked up Date carried Date Discontinued
ABC Co. Lemon Yes 06/02/02
ABC Co. Lime Yes 05/02/01 03/22/02
XYZ Co. Lemon Yes 04/02/02
XYZ Co. Lime Yes 05/02/01 06/20/02

You get the idea?? How do I do this? OK we are a cookie company, get me out of this and there are cookies in your future!!

Thanks,
Stephanie

 

[Stephanie T.]

Alex,

OK, well I figured it out myself! It all came down to using a combo box instead of a list box. How simple!!!

Thank you for what you did post, it was helpful in getting me to check my relationships and I think that by re-doing everything it is all much neater.

Best to you,
Stephanie

 

[Alexandre]

Looking forward having other opportunities to win cookies