Hi,
I'm new here.
(I have been doing a little bit of Access designing on and off for ~10 years now. Nothing too fancy but some people are very happy with their Access program and use it everyday.)
I have a challenge with a price per product per customer setup.
Have a look at my table layout: attachment.
Relaties: customers (PK customer ID)
Prijzen: prices (PK ID FK customer ID FK Product ID)
Omschrijving: products (PK ID)
The goal is that different customers can have different prices for the SAME product.
But all these prices have to be entered in for all customers (fair enough): so what I would like to have is a form with a combobox for the customers (easy enough) and the subform should show all products. Always, even if there is no price for that product for that customer at that the time because it can be entered right there and then.
When all data has been entered in the price table this is all easy enough. But I need/want a form that gives me all products (per customer) so I can add prices. And that's where I'm stuck. I might need a full join that gives me relations|products|price, but atm I'm kind of lost. I need some pointers in the right direction.
I feel like I might be overlooking something here or going completely the wrong way at it (applying database normalization in the worst possible way) so any pointers would be great.
It seems to me this should be possible; if not then my solution would be just to drop the price table and duplicate all products for every customer in the products table (with an added price column). But that seems so redundant. (Also because future products will have to be added as many times as there are customers).
I'm new here.
(I have been doing a little bit of Access designing on and off for ~10 years now. Nothing too fancy but some people are very happy with their Access program and use it everyday.)
I have a challenge with a price per product per customer setup.
Have a look at my table layout: attachment.
Relaties: customers (PK customer ID)
Prijzen: prices (PK ID FK customer ID FK Product ID)
Omschrijving: products (PK ID)
The goal is that different customers can have different prices for the SAME product.
But all these prices have to be entered in for all customers (fair enough): so what I would like to have is a form with a combobox for the customers (easy enough) and the subform should show all products. Always, even if there is no price for that product for that customer at that the time because it can be entered right there and then.
When all data has been entered in the price table this is all easy enough. But I need/want a form that gives me all products (per customer) so I can add prices. And that's where I'm stuck. I might need a full join that gives me relations|products|price, but atm I'm kind of lost. I need some pointers in the right direction.
I feel like I might be overlooking something here or going completely the wrong way at it (applying database normalization in the worst possible way) so any pointers would be great.
It seems to me this should be possible; if not then my solution would be just to drop the price table and duplicate all products for every customer in the products table (with an added price column). But that seems so redundant. (Also because future products will have to be added as many times as there are customers).