Problem with updting forms

[David57]

I am getting stuck with this problem.
I ‘m selling a single product to my customers. The price I quote to each customer, is bound to remain the same for very long periods. For this reason a decided to add “price” as one of the fields of the table TblCustomers.
I have another table, TblSalesDetails , containing, among the others, the fields Date, QuantitySold, SaleAmount.
I have created a form F_SalesDatails, based on customers, with the control “price” on the main form and with a subform, SF_SalesDatails , showing the same fields of TblSalesDetails .
Now my problem. When I put data in the fields Date and QuantitySold of SF_SalesDetails I need the control SaleAmount to be automatically calculated. Unfortunately if I change the price in TblCustomers all previous records of SF_SalesDetails are recalculated at the new price so that I am unable to keep track of the previous transactions at different prices.
How can I get around this problem? Hope someone can help.

[PhilOfWalton]

I think you've answered your own problem.
You need to hold the price in your details table. By all means set the default value from the price in your customer table.

Phil

[David57]

Thank you very much for helping Phil.
Do you mean I need to add a field “price” in my Details Table and set a default value for it drawn from the price in Customers Table? If this is the case, the price would be the same for all customers while each of them has a different price, though a “long term” one.
Perhaps I am misunderstandin g what you mean. Can you please be more specific?
David

[PhilOfWalton]

Precisely what I mean. I assume that there is a different price for each Customer. Those prices will change over the course of time, but when you create another sales record, the default price in the sales record will be the latest (and only) price for that customer.

So assuming that the default price in the sales record is not over-ridden, that will show what price was in the customer table at the date the sales record was created.

If the default price is over-ridden then that is the price the goods were sold at.

A warning should be issued if the sales price is over-ridden that the price in the Customer table may need to be changed

Phil

[David57]

Hi Phil,
I think I understand your point but I don’t know how I can set a default price in the Datails Table
which could apply to just one customer and not, as it happens, to all of them.
I’ve also been trying to link the “price” control in the SF_SalesDetails to the Price in the Customers Table but the result I got is that whenever I change the price in a sales record all previous records are recalculated at the new price.
My real problem is setting the default value for each customer as you suggested which, I think, is an excellent idea.
David

[PhilOfWalton]

Please send details of your customer table and your SalesDetails table, and the Foreign Keys.

Phil

[zmbd]

Usually issues of this nature make be suspect that there is something amiss with the basic design of the database:
Database Normalization and Table Structures

+ Here's my take (PK == Primary Key ; FK == Foreign Key)
- I'm only going to list the pertinent fields here. There would of course be additional fields and or tables related to make everything work correctly.
- all PK are autonumber; PK is named after its table
- all FK are numeric(long); FK is named after the related table
- remaining field(s) typecast I hope is obvious by its name :)

[tbl_customer]
[tbl_customer]![PK_customer]

[tbl_product]
[tbl_product]![PK_product]
[tbl_product]![product_default _price]

[tbl_customer_pr icing]
[tbl_customer_pr icing]![PK_tbl_customer _pricing]
[tbl_customer_pr icing]![Effective_Date]
[tbl_customer_pr icing]![FK_Product]
[tbl_customer_pr icing]![FK_Customer]
[tbl_customer_pr icing]![ProductPriceing]

With this arrangement you have an advantage in that you now have a historical record (by date) of the pricing for any given customer for any given date for any given product as each time you change the price you make a new entry in [tbl_customer_pr icing].

The [tbl_product]![product_default _price] price would be used if there is no price found in [tbl_customer_pr icing]. May be a tad tricky to implement in pure SQL... hmmm. One of our SQL wizards probably has that solution at hand.

[PhilOfWalton]

Hate to disagree with zmbd, but according to David (OP) he is only selling one product. So I would suggest, for simplicity his tables are

[tbl_customer]
[tbl_customer]![PK_customerID]
[tbl_customer]![default_price]

TblSalesDetails
[TblSalesDetails]![PK_TblSalesDetailsID]
[TblSalesDetails]![FK_customerID]
[TblSalesDetails]![QuantitySold]
[TblSalesDetails]![DateSold]
[TblSalesDetails]![Price]

To find the price in the TblSalesDetails use

[TblSalesDetails]![Price].DefaultValue = 
DLookup("default_price", "tbl_customer", "CustomerID = " & CustomerID)

The [TblSalesDetails]![Price] can be changed at will.
The Sales amount wil be the quantity * price both from the TblSalesDetails

Phil

[zmbd]

SF_SalesDetails I need the control SaleAmount to be automatically calculated. Unfortunately if I change the price in TblCustomers all previous records of SF_SalesDetails are recalculated at the new price so that I am unable to keep track of the previous transactions at different prices.

Phil I so see your approach; however, I think you missed this part of David57's OP?

My method keeps the historical records as stipulated.

:)

[PhilOfWalton]

No.

The SF_SalesDetails holds the price of the transaction.
It can be changed manually, (but probably won't need to be changed).

After the Customer default price changes, all subsequent sales records created will have that new price.

So each sales transaction will have the date and the corresponding price.

I accept the fact that there is no record of when the Customer Price changes, but it isn't relevant until a sale is put through after the new price is applied.

Phil

[zmbd]

First I missed a field
[tbl_customer_pr icing]![ProductPriceing]
When creating the example tables, it was my intent to have the field there.
I've corrected this oversight.

No.

Easy thing to do though. I've missed a few things myself in other threads and went off on the wrong tangent :)

The SF_SalesDetails holds the price of the transaction... After the Customer default price changes, all subsequent sales records created will have that new price.

What I see here is that you taking the
[tbl_customer]![default_price] * [TblSalesDetails]![QuantitySold]
=[TblSalesDetails]![Price]
So while your pricing is correct for each round of sales it does go against the grain to store a calculated value. You'll find this sentiment in many of the threads here at Bytes.com and in many of the other forums that deal with database. While de-normalization has its uses, in this case, it also may break some Good accounting Practices or at least potentially makes creating the financial statements more difficult.

So each sales transaction will have the date and the corresponding price.

which plays nicely with the layout I have given in that the transaction date can be used against the [Efective_date] to return the value of the pricing at that time.

I accept the fact that there is no record of when the Customer Price changes, but it isn't relevant until a sale is put through after the new price is applied.

IT IS AN ABSOLUTE MUST in Good Accounting Practices (my wife is an Accountant) to have this historical pricing record:
1) In affect you almost have the history by dividing the final price by the quantity shown. Now do this for every transaction detail in the database: a few dozen maybe a few hundred, no problem; however, a few thousand and that becomes a headache. :)

2) If you want to change the default price for all customers, you could of course write an UPDATE query against the customers table, changing how many records (?), or make the change in one place using the slightly more complex approach and the workflow I describe at the end of this post

3) You need to be able to show fair pricing practices in today's business world. You may have to prove in court that pricing changes are fairly applied, once again, you have the reverse calculation; however, but why put yourself thru that?

4) Make your CPA's life easier when tax time comes around, be able to show the original pricing of the goods and the price the goods were sold at for a given period Inventory Errors and Financial Statements so that that the financial statements are correct - without the nightmare of having to pull all of the quarter's records and back calculate the product sales price then go in and find if the price changed during the accounting period, then go back and calculate the price sold against the cost of the product (well, you have to do part of this anyway; however, having the history table should make this much easier - and time is money, especially to a Tax-CPA)

Side note - speaking of missing something in the post I missed this on first read:

...according to David (OP) he is only selling one product. So I would suggest, for simplicity his tables are

One product now; however, this layout is more robust and allows for say an old version and new version of the product which would be needed for the financials and inventory control or, more likely, David57 decides to add an additional product line - retail business are dynamic. Example, I have a relative that started out just sharpening tools, then branched into small engine repair, and then some small item repair, etc... (yes eclectic but I love him anyway). Using the simpler approach would have required a complete re-write of the database, most likely leading to something like this anyway; however, using the slightly more complex approach one need only add the additional service (as a work product :) ) to the [tbl_product] and to [tbl_customer_pr icing] as needed.

From a historical standpoint, one could (and I argue should) use the
[tbl_product]![product_default _price]
to write a new entry into the [tbl_customer_pr icing]
The workflow I see here is
Customer sale
Product lookup to find [tbl_product]![product_default _price]
Customer lookup to find [tbl_customer_pr icing]![ProductPriceing]
If there are no matching records or if the [tbl_product]![product_default _price] and the [tbl_customer_pr icing]![ProductPriceing] do not match then make a new entry in to [tbl_customer_pr icing] with the new default price and prompt the user to accept this new default.
There may be more logic to make things flow correctly.

IMHO, there should be another table that keeps the historical entries for the default product price so that there is an absolute traceability.

[PhilOfWalton]

Totally agree that the accounting principals are very poor, but you have still missed the point.
If you look at an earlier post, I suggested that on the OnEnter of a new sales record, you used

[TblSalesDetails]![Price].DefaultValue = 
DLookup("default_price", "tbl_customer", "CustomerID = " & CustomerID)

The actual selling price is held in the sales table. The SalesAmount Calculated, not stored) is the QuantitySold(st ored in table) * sellingprice(st ored)

Phil

[zmbd]

Phil, sorry, must have mis-read the post. :)

We're after basically the same thing here, it appears then that I have normalized the data one more level than you have; however, between the GAP and the easier means of expanding the product line OP would be better off going with the slightly more complex table. IMHO the CPA doing the taxes will thank him later. :)

[PhilOfWalton]

No problem.

Certainly if it was me I'd go along 100% with your solution, but as I don't think the OP has had thousands of years experience that we have (I only speak only for myself, obviously), I suggested the simple solution.

Phil

[David57]

Hi Phil,
Thank you very much for all your support and valuable suggestions. In the end, at a closer look to the structure of the example-tables attached to your last post, I have been able to get a full understanding of what you were meaning. Now, when changing prices, the previous records are not recalculated (that’s what I wanted) and so I can keep track of all transactions at different default prices. Thank you again.
David