Price per Product per Customer (1 Viewer)

janvdberg · Nov 25, 2012

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).

jdraw · Nov 25, 2012

I don't understand your tables.

From what I understand of your post, A Customer can Order/Purchase Many Products, and
A Product could be Ordered/Purchased by Many Customers - A typical Many to Many.
So use a junction table to identify which Customer Ordered/Purchased which Product.

Customers--->CustomerPurchasedProduct<------Products

where CustomerPurchasedProduct

has

PK CustomerPurchasedProductId autonumber
Fk to CustomerId
Fk to ProductId
PurchaseDate
PricePaid

Fields in purple make a unique Composite Index to prevent duplicates. This set up even allows a different Purchase Price for the Product by the same Customer on a different date.

junction table tutorial at
https://www.youtube.com/watch?v=7XstSSyG8fw
Good luck with your project.

janvdberg · Nov 27, 2012

Hi jdraw,

thanks for explaining the junction table.
I think I understand the concept (I didn't know it was called junction though). You learn something everyday

.
This lead me to a link on a foresightsoftware dot com page which also explains it nicely (I can't link URLs because of my newbie status).

But here is my problem. When there is no data yet for customers present, the junction table or query will appear empty of course.

And what I am trying to do is create a form with all products (which are 40 or so) and add prices per relation. This is basically the inventory. (Other forms for ordering will select from this data the price when a product is entered based on the relation.)

So selecting a relation from the combobox should always give all products: and I should be able to add prices (which are specific prices for that customer). Selecting a different relation would have to provide the EXACT same product list but I can enter different prices.

This way all products (the main inventory) will always be available to all users (with or without a price).
Maybe I am just too lazy to enter all data once. But I think this could be possible?

jdraw · Nov 27, 2012

Before getting too far ahead, could youu please post a jpg of your tables and relationships?

Can a Customer can exist without having Purchased a Product? And I believe you are saying a Product can exist independent of an Order/Purchase.

Here are some videos that show many of the concepts related to Tables and Relationships. I think these are relevant to your question and a good source of info.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

Good luck with your project.

janvdberg · Nov 28, 2012

Hi jdraw,

see the first attached file (I recreated the db in English en cleaned it up a bit).
I'd like to know what you think.

To answer your question:
Can a Customer can exist without having Purchased a Product? - YES
And I believe you are saying a Product can exist independent of an Order/Purchase. - YES

So how would I go about creating a form that always shows me all items (from Item) where I can add prices for per item for that particular relation?
See the 2nd attachment. The subform should have all items/descriptions (from Item) and I would add prices.

But like I said maybe I'm going completely at this it the wrong way.
I can just add 2 columns in the item table: relationid and price. But that would be a violation of the NF1 from what I understand.

jdraw · Nov 28, 2012

I suggest you do NOT have Price as a separate entity. See the attached jpg.

You record the Price of the item with the OrderItem. This price is actually what was paid. It could include discounts, clearance special etc.
You could record the Current Retail Price with the Product, but be cautious since it may prevent you from changing a Price, or when changing a Price cause all previous Orders to change.

I don't know what RELATION represents in your diagram.

This video may help
http://www.youtube.com/watch?v=q1GaaGHHAqM and
http://www.youtube.com/watch?v=lXAGQ8vmhCY&feature=relmfu

janvdberg · Nov 28, 2012

That situation is actually where Im coming from

But this was too prone to errors because people that didnt know the right prices (or made mistakes) had to add the orders (mind you there wasn't a current retail price).

So the manager asked me to have an input form that only he could control; where he would add prices per customer. So he wouldn't have to worry about prices again. But the trick is: prices for the same product vary per customers based on contracts etc.

I already watched all of Art Langers video. I learned a lot! Thank you for that.
At this point I think Im going to duplicate all items for all relations. It seems redundant but it also sort of make sense in a way that there is a concatenated key (unique) for: relation+price+item.

jdraw · Nov 28, 2012

I wouldn't do that -- at least not yet.

Do you have a Price for Product for a Customer? Could you still sell that product to that Customer for a different Price?

Please describe the possibilities. The key to this sort of thing is to store the Price that was agreed upon in the OrderItems table.

janvdberg · Nov 28, 2012

The price for a product for a customer is set (that is the point, because of the mistakes

)

Maybe this clarifies things a bit:
Theoretically the price for the product for the customer CAN change. But this is not really a problem because every month all orders are "run"/closed and copied (with a csv file) to the bookkeeping software, that does billing etc.
After this export all orders are deleted from the database (the order system is more or less to keep track of everything in a simple manner without entering it all directly in de bookkeeping software).
So after a run prices could change: this wouldnt really affect the historical orders.

To go into more detail:
This is a garbage disposal company that has around ~100 customers who can "offer" 40 different kinds of garbage (paper, plastic, tempex etc.) They drive up to the window, the guy counts/weighs the garbage adds one or more orderdetail lines (per item kind) to a new or already existing order in the DB, prints a nice receipt from the DB for the customer and off they go.
Some customers come multiple times per week. So their 'orders' stay open till the end of the month. Because some customers offer larger volumes plastic or paper or whatever they get a different price. But the item is still paper or plastic etc. but the price is different.

jdraw · Nov 28, 2012

I would go with something like the attached.

You need to populate the table CustProdPrice
with the current agreed upon data.
Whenever you decide to change the value in the Customer's Price for a Product, this is the table where you would make the change. It would seem reasonable to restrict who had write access to this table.
You could put a Flag in the table to indicate if this has been updated. The Flag would act like a "logical delete indicator". When you add a new record for this Customer/Product/Price , you could set the Flag on the old record to indicate that value has been updated/superseded. This would allow you to do some review on the change/history of Prices by Product by Customer. If this is not needed, you could simply update the record and have only 1 record per Customer per Product ( and it would always be current).

In my view this would be the default Price for that Product for that Customer. However, you could still change that to a specific Price if needed (under some circumstances). The Price on the OrderItemDetail record would still be the AgreedUponPrice. (could be default or default - some discount)

You would probably include an OrderItemDate in the OrderItem table to record the individual OrderItems as they are collected during the month.
Your Order table would have something like OrderOpenedDate or OrderStartedDate.

Just a few thoughts to consider.

janvdberg · Dec 13, 2012

Yes clever approach!

One big 'but' though: "You need to populate the table CustProdPrice"

That was _exactly_ what I was trying to avoid or make at least as easy possible

(from my first post).

All Products are in 'Products': and I want a form with those exact products and be able to add a price for a specific relation.

But I'm gonna stop now (it's probably getting annoying

) I will go with the suggested solution. (this is costing way too much time).

Thank you so much for thinking with me an the links you shared.

Cheers.

jdraw · Dec 13, 2012

Concerning the "but":

It is this relation of Customer and Product to which your Selling Price is an attribute.

Just thinking while typing. To populate the SellingPrice on this Date for this Product and this Customer.

You have a combo of Customers and
a second combo of Products and
a a text field for Price and
a button.

The record source of the first combo is your Customer table ( or a query sorted by name or whatever). The record source of the second combo is you Product table (or a query sorted by Prodname or id or whatever makes sense to you),

once you select a Customer and a Product, you put the Price in the text box, you click the button to add a new record to the CustProdPrice table using the CustomerId, ProductId, Date() and the value placed in the text box.

You could write a log record indicating what you did. You could create a MsgBox with the related info......

janvdberg · Dec 30, 2012

Hi! Im almost there.
I used a somewhat different approach now (still based on the DB scheme from update 10)

1. Orders (and orderitems) can be added all the time, for some items there will be (customer) prices for others there won't (yet).
2. There is a specific form where prices can be added for a product for a customer (dropdown lookup etc.). Works like a charm.

But now I'd like to create a form/query that gives me all orderitems (relationID+ProdID) that don't exist yet in the CustProdPrice table.

So to put it simply; give me all orderitems that don't have a specific price yet.

The manager can then add these prices as we go along, in the specific form (2).
This way orders can be added all the time, and prices can be added/adjusted later.

What is the best way to go about this?
Normally (when it concerns 1 column) I would use an OUTER JOIN. But since I have to match two columns (RelationID + ProdID) I'm not sure what the smartest way is to pull this off. Should I first concatenate (& "" &) the two columns? Or should I use a nested query?

jdraw · Dec 30, 2012

Can you post a jpg showing your table and field names?

I don't know what RelationID represents.

In your OrderItems table does your AgreedUponPrice have a default value of 0?

janvdberg · Dec 30, 2012

Sorry for the confusion in naming.
Please see the screenshot, hopes this explains.
There is never a default price.

So there is only a price when this is explicitly added (in CustProdPrice).
But since order details can be added without them having a price I need an overview of those without a price.

jdraw · Dec 31, 2012

My concern is that you seem to be storing the Price only in the CustProdPrice table and only once (no history), and not a selling price with the OrderItem, so you may be masking an issue. When you want to change the Price, you are really changing the only Price associated with the OrderItem, which means that if you want to print or look at an older Order, you will see the latest prices NOT the price (agreed upon at the date of sale).
I am trying to work with your model as per the latest jpg. I have added a few fields for consideration, and I changed RelationId to CustomerID (since we are referring to the Customer). I left the ItemId and ProdId, but it could get confusing. Idid attach the Relationships I have based on your jpg.

I want to highlight that I made a unique index on fields (CustId + ProdId + DatePriceAssisgned) in CustProdPrice.

After adding a few Customers and Orders and OrderItems, and then a few entries for the CustProdPrice, I tried setting up a query to find OrderItems for a Customer that do NOT YET have an associated Price.

This is the query to show info about such OrderItems [notice that I used (CustId & ProdId) as a means of identifying what was NOT in the CustProdPrice. [PricesNotAssignedForCustomerProductItem]

Code:

SELECT [Order.CustomerID] & [ProdId] AS CustProd, Order.OrderId
, Order.Orderdate, Customer.CustName, Product.Prodname, Product.ProdId
FROM 
(Customer INNER JOIN [Order] ON 
   Customer.CustomerID = Order.[CustomerId])
     INNER JOIN (Product INNER JOIN OrderItem ON
       Product.ProdId = OrderItem.ItemId) ON
          Order.OrderId = OrderItem.OrderID
WHERE 
((([Order.CustomerID] & [ProdId]) Not In
     (select CustId & ProdId from CustProdPrice)));

Here is a query showing the CustProdPrices which have been assigned

Code:

SELECT Customer.CustomerID, Customer.CustName, Product.ProdId
, Product.Prodname, CustProdPrice.CurrentPrice
FROM Product INNER JOIN (Customer INNER JOIN CustProdPrice ON
 Customer.CustomerID = CustProdPrice.CustId) ON
 Product.ProdId = CustProdPrice.ProdId
WHERE (((CustProdPrice.CurrentPrice) Is Not Null))
ORDER BY Customer.CustName, Product.ProdId;

I am attaching my tables with the sample data so you can see the result of the queries.

I hope this is helpful to you.

janvdberg · Jan 6, 2013

Wow!!!

Incredible jdraw! Thank you so much for your very detailed explanation!

With your input I was able to finish the program! I am very happy.

Regarding your remarks about storing the price: I understand what you are saying. However for this program it is not a problem. The customer more or less starts each month fresh (clears the DB) after an export has been made to the bookkeeping software. So price history is not really an issue.

But that said I am very impressed with the amount of effort you put in towards helping me. This really is a great forum.
From this one question I learned a lot _and_ I'm guessing my client will also be happy with the end result

jdraw · Jan 6, 2013

Happy to help. Thanks for replying.

RainLover · Jan 7, 2013

I cannot agree with your approach.

First of all, I would think that several Customers would share the same Price Structure. If you made a Customer a member of a group then you would enter prices only once for many Customers.
You should never delete history. Your Auditor will not be happy to see such a practice.

Not all prices would changes at the same time. You may have 50 items for sale and only 3 or 4 change. No need to change all of them. Add a field of Date Type which would be the effective date of the product. So you can report on sale that span across two months of even a year which would show the different prices for different times.

Your current structure breaks Normalisation rules, has no History and would fail an Audit.

The above is not that difficult for a competent user to create.

I would also suggest that you look at your naming conventions. It could do with a good tidy up which will be beneficial in the long run.
There is an article on naming conventions in the link that appears in my signature.

Price per Product per Customer (1 Viewer)

New member

Attachments

Super Moderator

New member

Super Moderator

New member

Attachments

Super Moderator

Attachments

New member

Super Moderator

New member

Super Moderator

Attachments

New member

Super Moderator

New member

Super Moderator

New member

Attachments

Super Moderator

Attachments

New member

Super Moderator

VIP From a land downunder

Similar threads

Users who are viewing this thread