# Change of pricing.

• ### Question

• My business is a car rental business, renting cars on a monthly basis as long as the customer wants to keep it.

I calculate a rental rate against every car in the car table. The rate I charge is calculated, taking book value and management adjustment into account. On average my customers keep cars for 15 months. On return, we revalue the car and when we rent it again the rate will be different. All previous automatically generated invoices are connected to that rate field. If the rate change, I will change all old invoices?? What do I do to have a new rate for new invoices only?

I use the expression =round[(rentalinstallment,-2)] to round to the nearest 100. Where rentalinstallment is the final calculated field that keeps the installment I want to charge from the customer.

Saturday, November 7, 2015 2:26 PM

• 1.  I do not see a process yet, once invoices are connected to values and rates, how do I disconnect them?

2.  ..............to complicate this process more, for if he swap on the 10th of the month and the new car is more expensive, all kinds of pro rata calculations comes into play. We've done that manually until now, but I just know, it can be programmed.

3.  A lot of talking, but I am still looking for the exact answer here
1.  It's a modelling issue.  It is analogous to a unit price in a conventional ordering/invoicing database (the sample Northwind database which ships with Access is an example).  In the same way that the current unit price of a product is functionally determined by the key of a Products table or similar, and the unit price on which the invoice is based is functionally determined by the key of an InvoiceDetails table or similar, in your case the current value of a car is functionally determined by the key of a Vehicles table or similar, and the value on which the invoice is based is functionally determined by the key of the equivalent 'InvoiceDetails' table.

2.  This is a matter of working out the necessary logic and incorporating it into a query.  To give you an example of this sort of thing, the following is a query from my Timesheet demo, which determines employee's pay on the basis of the rates stored in an EmployeePayRates table, which stores the hourly rate payable to the employee in different date ranges:

PARAMETERS Forms!frmWeeklyTimeSheet!cboEmployeeID LONG,
Forms!frmWeeklyTimeSheet!cboWeekStarting DATETIME;
SELECT TimeLog.EmployeeID, TimeLog.WorkDate, TimeStart, TimeEnd,
TimeDurationAsDate([TimeLog].[TimeStart],[TimeLog].[TimeEnd]) AS TimeWorked,
WeekStart(1,[WorkDate]) AS WeekStarting, HourlyPayRate,
TimeDurationAsDate([TimeLog].[TimeStart],
[TimeLog].[TimeEnd])*24*[HourlyPayRate] AS Payment
ON (TimeLog.EmployeeID=EmployeePayRates.EmployeeID) AND (TimeLog.WorkDate
BETWEEN EmployeePayRates.DateFrom AND Nz(EmployeePayRates.DateTo,#1/1/2200#))
WHERE TimeLog.EmployeeID=Forms!frmWeeklyTimeSheet!cboEmployeeID
AND WeekStart(1,[WorkDate])=Forms!frmWeeklyTimeSheet!cboWeekStarting
ORDER BY WorkDate, TimeStart;

3.  Exact answers can't be given.  We can only try and point you in the right direction.  In the MS Community forum for Access I quote Richard Feynman below my signature: "Don't write it down until you understand it!".  While he gave this advice to his students in the context of fundamental physics, it is very pertinent to relational database design.  The database relational model is a formal one, and to develop robust and efficient relational databases requires an understanding of the how the model works, and of the tools which database management systems like MS Access provide to implement the model in real world contexts.  The other option is to commission a professional database developer, and this often makes better business sense than investing time and effort which can be more profitably be spent in other tasks.

Ken Sheridan, Stafford, England

Friday, November 13, 2015 1:10 PM

### All replies

• It depends on how you structure data.  To keep simple: if you have table Contract and table Price List...... and you link the 2 - then with this structure each time you change the price it displays the new price on all the contracts......

that can be either or good or bad depending on what you want; if it is bad - then you have 2 approaches....

a. linked: in Price List have a field which indicates current (or some other conceptual value) and link to that, so that you have multiple entries in the Price List table; thereby differing Contract records link to differing Price List records

b. not linked: write in the Price List value into the Contract value as part of the data entry experience

Saturday, November 7, 2015 3:32 PM
• Before you begin building an interface for your application, the first thing you need to do is ensure that you data structure is sound and properly normalized to the third normal form.  You can research information about normalization on the internet.  There are plenty of resources out there.

Your database is the heart of your application and the most important phase in your development.  If your database isn't right, the rest of your system will suffer.

Your pricing table should not affect past or present invoices directly.  It should only be a reference for how you arrive at the billing cost at the time when you bill your client.

I've prepared a basic table structure that should give you a better idea of how to model your database.  Please let me know if you have questions.

Saturday, November 7, 2015 3:51 PM
• The current value should be stored as a column in both a 'Cars' table and an 'Invoices' table (or InvoiceDetails table if the invoice has multiple invoice lines).  When a row is inserted into the latter the value is looked up from the 'Cars' table and assigned to the current row in the Invoices (or InvoiceDetails) table.

You'll find an example as InvoicePDF.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

This little demo file is primarily intended to illustrate how to generate invoices as PDF files, but happens to be based on a simple hypothetical invoicing application.  In the invoice details subform in the main invoice form the current price of a product is looked up in the AfterUpdate event procedure of the InvoiceID control, and assigned to the current row in InvoiceDetails.  The same is done with the current VAT (the EU Value Added Tax) rate in order to apply the correct VAT to the invoice, based on the rate of tax for the product in question current at the time the invoice is raised.

Ken Sheridan, Stafford, England

Saturday, November 7, 2015 6:06 PM
• Since Ken feels the need to come behind my posts and contradict what I have provided, I guess I will need to explain my model a bit for you.

You have the choice of how you want to manage your pricing.  You may choose to do it by vehicle, or you may choose to do it by vehicle type/class, such as the following:

• Sedan
• Wagon
• Crossover
• Luxury
• Coupe
• Hatchback
• SUV
• Hybrid
• Convertible
• Pickup
• Van/Minivan
• Electric

In the model I have provided for you, I have set up a table called "BillingPrice."  It uses the vehicle class as a basis for your pricing.  This model is merely a suggestion.  You can model your database however you see fit.  But I use the class vs. vehicle because there is a lot less to price out.  You may have well over 100 different cars.  Pricing each one is much more time consuming and administratively taxing than pricing by the vehicle classification.  But that might be your business model.  I don't know.

As far as it goes, you may even want to create a table called something like "AccountBillingPrice."  Such a table would hold special pricing for particular accounts and would have a foreign key relating to the Account table.

What's important to understand, just as I mentioned earlier, your price list should not affect your billing, past or present.  It is always subject to change and therefore is only a reference for each bill.  Today a Minivan may cost \$25/day to rent.  Tomorrow you may change the price to \$28/per day.  If you created 5 bills yesterday, the billing price for a car rental would be referenced from your price list and your invoices would be generated with a line item of \$25/day for car rental.  Anything after yesterday would be billed at \$28/per day.

And actually, I have set up a table called "Rental."  This table stores all the information about the rental agreement between you and your client.  So it this table that your invoice billing will be based off of in the current model.  When creating a record for this table, the application will reference the price list table to create the contractual billing costs for the rental record.

I hope that this makes sense to you.  If you have any questions about this or the information previously provided, please don't hesitate to post back.

Regards,

RM

Saturday, November 7, 2015 7:47 PM
• Thank you so far guys.

I have to put in time thinking about it. I am a developer's rookie, but like what I created so far, its an obsession to fight the battles until the war is won. I simply want the perfect complete database, database and financial package in one, 100%, and doing everything possible, so that users don't have to do a single click if not needed, or ever have to click twice for the same purpose, 100% validation where ever the DBase can.

Been in business 28 years, and dream't of this, but even my wife was a developer, didn't finish the job, she died. All other guys failed, not the right guys but they easily take more than a year and then the relationship fades. I want to be able to do it, for various reasons.

We are planning a new project early in the new year, importing many used cars. This Dbase have to be right by then. If the variables change, the old documents should not be adjusted.

1. RM, yes I have to do pricing on each car by itself, since even two of the exact same cars could have been bought for a different price, or the condition, or mileage could be majorly different.

2. My policy on pricing is to establish the book value, since "Mead & Mcgrouthers values" is also a table in my Dbase and every month there are new trade and retail values on each car, which is added to the table. We use those values for other purposes as well. Adding retail and trade together, divided by two gives "bookvalue". I then have a field, protected by passwords, where management can put in an adjustment "manadj", to get the final field I named "Valueforpricing" will deduct the management adjustment from a field "bookvalue".

3. It is company policy that we should price at 6% per month of "ValueforPricing". Not impossible that the 6% may change as well. But my database is currently set up to use that straight forward, and it will go wrong when I get the car back in 15 months and then I want the dbase to use that specific months book value and "ValueforPricing". Also the Invoice run can not be done now, I must learn how. Once the new customer has signed the deal, the installment stays the same.

4. At the end of each month we do an invoice run, and the Dbase of course must do this right. Must raise the same rent than last month for running customers. Invoice Run cannot use the values that changed every month, but should change when the car is returned and rented again.

Yes Ken its a major and similar concern when Vat Rate could change. I have a VAT Rates table, for when we do VAT returns, there are probably 9 categories of which 4 is no vat but that's the way they want it, and the Dbase must eventually give reports in the way revenue wants it, but still can not change previous docs, when the rate changes.

I just want to change the VAT Rate in the VATRATE table if ever, but again, it should not change docs of the past. Currently my database is set up in that wrong way.

Saturday, November 7, 2015 9:25 PM
• We are planning a new project early in the new year, importing many used cars. This Dbase have to be right by then. If the variables change, the old documents should not be adjusted.

Hi Hans,

My practice is that all variables that can change in time, like VAT rate, ValueForPricing, are stored in the current document. For the documents only these "frozen" values are used. That means that when the VAT rate changes, the documents stay still the same.

The curent value of the VAT rate and ValueForPricing can be stored in a definition table. New documents will pick these values as the default. When the VAT rate changes, just change the value in the definition table, and new document get the new default value. As these values are default values, the can be overwritten manually to account for other circumstances until the document is final.

An alternative could be to store the changes in time of VAT rate, and the other quantities, but I found that too complicated.

Imb.

Sunday, November 8, 2015 10:25 PM
• Imb. Thanks.

I am an accountant an businessman for 30 years, but only two months since I put in every hour I can, on Access. Sorry if I don't know what is a "definition table" yet. How does it work, or what do you mean?

I say again

- I have a "Company" table with my adr, bankacc etc detail, but it also keeps company policy. There I store the 6%. Like the VAT Rate Table.

-The "Mead & Mcrouther" table keeps values that changes every month. From the day we buy it, till we sell it every month the new values are added, and stay there. Most Cars are kept for a few years.

-Of course there is a "Customers" table, and "Vehicle" Table. I just want hem to keep the basic info connected to Customers and Vehicles. I assume I will create a "definition table" or separate table for my specific pricing process, one row for every Vehicle. Maybe you are trying to say in the "definition table" I can store all the variables. VAT, Values, Rate of pricing, whatever else, and maybe my thoughts on using dates. Every Invoice or document can be referred to between two dates when any of the variables changed. In the coming week I have to work out or learn from you clever guys, how. Right now, it seems a little heavy to me, but bring it on! In the past 24 hours just a bit overload on the mind.

-I do know how to create such a pricing QUERY. If it is a definition TABLE, do you mean I put in these rates manually, for I still don't achieve the correct expression to carry forward one text field info on one form to another text field on another form, Rookie!

- I was busy with the database every hour I could since RM and Ken advised me above, have not used time on this yet. Even though it is 01H00 where I am, I will box on. From tomorrow I will have to start doing it right, as Ken said I can not develop on top of wrong foundation. I think mine is OK.

Sunday, November 8, 2015 11:19 PM
• -Of course there is a "Customers" table, and "Vehicle" Table. I just want hem to keep the basic info connected to Customers and Vehicles. I assume I will create a "definition table" or separate table for my specific pricing process, one row for every Vehicle. Maybe you are trying to say in the "definition table" I can store all the variables. VAT, Values, Rate of pricing, whatever else, and maybe my thoughts on using dates. Every Invoice or document can be referred to between two dates when any of the variables changed. In the coming week I have to work out or learn from you clever guys, how. Right now, it seems a little heavy to me, but bring it on! In the past 24 hours just a bit overload on the mind.

Hi Hans,

Your Company table is an example of a definiton table. It is just a regular table that contains some basic values.

In this table you have defined the current VAT rate, or the default value.

When you start a new Vehicle record, you copy this value to the VAT field in the Vehicle record. This VAT rate stays with the record, independant of any further real changes in the VAT rate.

When the VAT rate changes, then you store thet new value in Company table, and from then on all new Vehicle records take that new value, whereas all existing Vehicle record keep their old VAT rate. For every Vehicle record you know which VAT rate was used.

You can do this for all variables that change from time to time their value. So your definition table only contains the default values that are used (conserved) in the Vehicle or Cutomers records.

I can assume that the Pricing of a Vehicle is specific for ech vehicle. That value does not belong in a definition table, but is just a field in the Vehicle record, and must be filled in per record.

I hope I made me clear.

Imb.

Monday, November 9, 2015 1:51 PM
• I think it might help if we go back to some basic principles of the database relational model.  Inevitably space and time available means we have to oversimplify things as the subject is a complex one with approaching 50 years of academic literature since Codd first introduced the model in 1971.  I'm sure you'll be familiar with much of what follows, but I'm going right back to basics for the sake of completeness; we'll get to the real meat as it applies to your database at the end.

1.  A Relational database is a model of that part of the real world with which it is concerned.  As such the model must reproduce the properties of the real world.  It was Codd's breakthrough to realise that this could be done by means of the relational algebra (and later by the relational calculus).  Fortunately we don't have to be mathematicians to work with the model as database management systems like Access provide us with tools for doing so.

2.  The database relational model represents the reality in terms of its entity types and the relationships between them.  Each entity type is modelled by a table, and the attributes of each entity type are re[resented by columns in the table.  A relationship type can in the case of a one-to-many or one-to-one relationship type (unary relationship types) can be, and usually is, represented by the primary key of the referenced (parent) table being referenced by a foreign key in the referencing (child) table.  A many-to-many relationship type (binary, ternary relationship type etc) is represented by a table which resolves the many-to-many relationship type into two or more one-to-many relationship types.  Such a table is sometimes called a 'junction' table colloquially.  A one-to-many relationship type is occasionally modelled by a table in the same way, though the necessity for this is rare.

3.  The tables in a relational database, and structure of each table, are determined by formal rules, particularly by the process of normalization by decomposition, which is designed to eliminate (or at least reduce as far as possible) redundancy in the database and the consequent risk of update anomalies.  In plain English this can be thought of meaning that each fact is stored once only, and there should be no contradictory statements of fact.  A simple example would be if a table of locations included both City and Country columns, which would allow London to be in the UK in one row, but in France in another.  The Country column in this Locations table is redundant and the table is not fully normalized.

4.  The reason our hypothetical Locations table is not fully normalized brings us to another important concept of relational database design, which is that of functional dependency (or functional determination when looked at from the other direction).  Normalization to Third Normal Form (3NF) (we'll not go into the question of higher normal forms right now) requires that all non-key columns in a table de functionally determined solely by the whole of the table's key.  In a table of contacts for instance a row for me might have a ContactID key value of 42.  So wherever the ContactID value of 42 appears in the database we know that the FirstName value is Ken and the LastName value is Sheridan.  In a table of meetings therefore only a ContactID foreign key value needs to be included because from that all other attributes of me are known (functionally determined), so if this table also include FirstName and LastName columns they'd be redundant and I could appear in the table as Keith Sheridan for instance (which, for some reason, the captain of the university rugby team I played for insisted on calling me during all my time in the team!).  I did in fact find three versions of my own name as the author of technical articles in my own field of work in one badly design database.

5.  We are now getting close to how all this applies to a database like yours, because we now have to consider 'time independent functional dependency'.  FirstName and LastName are time independently determined by ContactID in our example because people's names don't usually change, and even if they do, the new name will normally be applied to the person in all contexts.  There can of course be exceptions, and where this is the case these would need to be modelled in the database.

So, when we look at the question of VAT, VAT rates are an entity type with attributes Rate and Category (0.2 and 'Standard Rate' in the UK for instance).  A VATRates table would consequently store the *current* rate of each category.  A Products table can handle this in a number of ways; it could have a VATRate foreign key column, or it could have a VATCategory foreign key column.  This is possible because both Rate and Category are 'candidate keys' of the VATRates table.  A third way would be to classify each product.  For example, in the UK books are zero-rated for VAT, but e-books attract VAT at the standard rate.  So if Amazon sells me a (printed) book I pay no VAT; if I download one to my Kindle I pay VAT.  A ProductCategories table consequently could reference the VATRates table, and a Products table could reference the ProductCategories  table.

When it comes to raising an invoice, the rate of VAT for each item invoiced is not time independently determined by the key of Products as the rate of VAT for the product can change over time, so each invoice must retain the rate of VAT current when the invoice was raised.  In relational-speak this means that the rate of VAT for the invoice is functionally determined by the whole of key of InvoiceDetails.  Consequently the inclusion of a VATRate or similar column in InvoiceDetails is legitimate as the table is correctly normalized to Third Normal Form.

When a row is inserted into InvoiceDetails the current rate of VAT is looked up from wherever it is stored (which depends on which of the approaches described above have been followed) and assigned to the VATRate or similar column of the row being inserted into InvoiceDetails.

Exactly the same principles apply to prices of course, with the exception that the current list price of a product is an attribute of the product, not of the category of product as might be the case with the rate of VAT.

I've made a small amendment to my InvoicePDF file to illustrate what I've described above with regard to VAT.  You'll find it at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Bear in mind that this little demo is only intended to be a simple illustration of some basic methodologies, not a working business tool.

Ken Sheridan, Stafford, England

Monday, November 9, 2015 1:58 PM
• Thanks, Keith! Always informative ...

peter n roth - http://PNR1.com, Maybe some useful stuff

Monday, November 9, 2015 7:26 PM
• Hi Ken.

I've done a quite a bit of focused thinking. My wife achieved developing the "nice to have" 30 months ago before she died. I do not know exactly how she done it in SQL, but the pricing process had the features I would like, and I plan to not stop until I achieve the following in Access.

- I would like not to compute pricing invoices manually. Maybe I do not understand fast enough.

- My inexperienced insight now only see all documents connected to tables and rates, that are ruled by dates.

- I do not see a process yet, once invoices are connected to values and rates, how do I disconnect them? I want to update VAT, Car Values, or rental rates in their appropriate tables easily, and the system must do the pricing correct, automatically. I think I am many weeks away of achieving that, but I wont stop until then.

- Every new car bought is brought into "Vehicle table" by "Creditors Invoice". Customers and cars have status, and customers sometimes swap cars to complicate this process more, for if he swap on the 10th of the month and the new car is more expensive, all kinds of pro rata calculations comes into play. We've done that manually until now, but I just know, it can be programmed.

-Every month Values change, but if a car was returned after 15 months, my new price is on new values, and then a customer pays the same installment until the deal ends.

- Maybe the challenge is bigger than I thought, but I have no doubt it can be done.

- A lot of talking, but I am still looking for the exact answer here.

Tuesday, November 10, 2015 10:38 PM
• I’m afraid I can’t reply in detail right now as I’m in the middle of a complete system rebuild. I’m having to send this from my iPhone! I’ll reply more fully when we are up and running again.

Ken Sheridan, Stafford, England

Wednesday, November 11, 2015 9:11 AM
• 1.  I do not see a process yet, once invoices are connected to values and rates, how do I disconnect them?

2.  ..............to complicate this process more, for if he swap on the 10th of the month and the new car is more expensive, all kinds of pro rata calculations comes into play. We've done that manually until now, but I just know, it can be programmed.

3.  A lot of talking, but I am still looking for the exact answer here
1.  It's a modelling issue.  It is analogous to a unit price in a conventional ordering/invoicing database (the sample Northwind database which ships with Access is an example).  In the same way that the current unit price of a product is functionally determined by the key of a Products table or similar, and the unit price on which the invoice is based is functionally determined by the key of an InvoiceDetails table or similar, in your case the current value of a car is functionally determined by the key of a Vehicles table or similar, and the value on which the invoice is based is functionally determined by the key of the equivalent 'InvoiceDetails' table.

2.  This is a matter of working out the necessary logic and incorporating it into a query.  To give you an example of this sort of thing, the following is a query from my Timesheet demo, which determines employee's pay on the basis of the rates stored in an EmployeePayRates table, which stores the hourly rate payable to the employee in different date ranges:

PARAMETERS Forms!frmWeeklyTimeSheet!cboEmployeeID LONG,
Forms!frmWeeklyTimeSheet!cboWeekStarting DATETIME;
SELECT TimeLog.EmployeeID, TimeLog.WorkDate, TimeStart, TimeEnd,
TimeDurationAsDate([TimeLog].[TimeStart],[TimeLog].[TimeEnd]) AS TimeWorked,
WeekStart(1,[WorkDate]) AS WeekStarting, HourlyPayRate,
TimeDurationAsDate([TimeLog].[TimeStart],
[TimeLog].[TimeEnd])*24*[HourlyPayRate] AS Payment