How can I? customers, products and prices on form

  • Thread starter Thread starter Ssale
  • Start date Start date
S

Ssale

I have a customers table, products table and prices table because every
customers must have a different price for each products. Problem is, I need
form where I can create new or select existing customers (thats no problem)
and get a whole list of products where I can take a look on price or input
price for that customer.
 
Use a Form bound to the tblCustomer (or a Query based on the tblCustomer)
and a linked Subform based on tblPrice (or a Query based on tblPrice).

Check Access Help on Subform and also Many-to-Many relationship since you
actually have a M-to-M relationship between tblCustomer and tblProduct and
the tblPrice is a Link / Resolver Table".
 
Use a Form bound to the tblCustomer (or a Query based on the tblCustomer)
and a linked Subform based on tblPrice (or a Query based on tblPrice).

Check Access Help on Subform and also Many-to-Many relationship since you
actually have a M-to-M relationship between tblCustomer and tblProduct and
the tblPrice is a Link / Resolver Table".
Click to expand...

You wrote "M-to-M relationship between tblCustomer and tblProduct". I
create a tblRelations table (with: idrelations -autonumber, idcustomer
-number, idproduct -number). In relationship window I create relations O-M
between tblCustomer and tblRelations, and O-M between tblProducts and
tblRelations. But when data is change in tblRelations table? Do I need to
change my existing forms and link my id keys on frmCustomer and frmProducts
to the tblRelations table.

I want to create form where I can select or enter a new customers and then
I got on subform a list of produtcs with prices, and then I can set new
price for products or change the existing price.
Is this possible?
Please help thanks in advance.
 
See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)



Ssale said:
On Wed, 29 Oct 2003 23:39:36 +1100, Van T. Dinh wrote:
You wrote "M-to-M relationship between tblCustomer and tblProduct". I
create a tblRelations table (with: idrelations -autonumber, idcustomer
-number, idproduct -number). In relationship window I create relations O-M
between tblCustomer and tblRelations, and O-M between tblProducts and
tblRelations. But when data is change in tblRelations table? Do I need to
change my existing forms and link my id keys on frmCustomer and frmProducts
to the tblRelations table.
Click to expand...
Not sure what you are asking here but you got the right idea in the next
paragraph.

I want to create form where I can select or enter a new customers and then
I got on subform a list of produtcs with prices, and then I can set new
price for products or change the existing price.
Is this possible?
Please help thanks in advance.
Click to expand...
Yes. This is the Customer-focused Form. The Main Form is based on the
tblCustomer and the linked Subform is based the tblRelations (or a Query
based on the tblRelations). On this Form you can enter new Customer and
then set the prices (applicable to this Customer) for your Products

Alternatively (or in addition to the above), you can also set up the
Product-focussed Form. In this case, the Main Form is based on tblProduct
and the Subform is based on tblRelations. On this Form, you can enter new
Product and set the prices of this Product as applicable to each Customer
who buys this product.

Check your books / Access Help on M-to-M relationship and the Form / Subform
combination.
 
Back
Top