Bad to store calc'd values, but....

[torok]

I need some advice.

Ok, I know it's very bad to store calculated values, but I'm writing an app to keep track of orders and invoices. I keep my taxes in a lookup table, and if they change, I don't want to see incorrect values for, say, last year's invoices when I look them up.

So I think I should store the totals for each invoice upon creation, by calculating them upon creation of the invoice. Currently, when I create an invoice, I run an update query that DSums the columns in another query (4 times) that calculates the subtotals, two taxes, and totals for each order that's going on an invoice, and then sticks those numbers into my Invoice table (so at any point in the future I can pull up that invoice, regardless of whether tax rates have changed since then.

This query takes forever.

Problem is, I've got Invoices, Orders, OrderItems, and Taxes. Each order has many items. Each item can have tax1 and/or tax2 - it stores a bool for each. If I want to calculate the value of an invoice, I need to know which orders have that invoice number, then which orderitems have those order numbers, then find out which taxes are applicable to each orderitem and look up their values in the tax table. Then I have to sum the subtotals, tax1, tax2, and totals for every item on every order that's on my invoice.

I have a feeling that although my design is partially correct, I'm doing something very wrong.

Is there a better way?

 

[KevinM]

Can't you just store the tax value in your items table against each item, much like you would store the price (in case of any price changes). Then simply perform any calculations in a query.

I wouldn't of thought you would need to store the actual Invoice total.

 

[torok]

Well, the reason I had to do it this way is I can't make a simple items table that I lookup items in. Virtually every item is different and created at design time.

This is a trucking company. Every "item" is actually different on every order. Sometimes they might charge $20/hour for their truck, sometimes a different amount depending on the client. Sometimes they charge GST on items, sometimes they don't. So upon creation of an "order", they create new items to go on that order.

 

[neileg]

Speaking as an accountant, first, I would agree that this type of calculation should be stored. The invoice is a document that is issued to a third party and you need to recall exactly what was on the document. Changes in tax rates, even the way a rounding is performed can affect these values.

So I would be tempted to create a table to hold all the invoice lines and related tax. I would, as KevinM suggests, sum those lines in a query to create the total, but the other values I would hold. If the invoice is of the form qty x price I might hold these two as values and calculate the line total, or I might not.

I don't claim to be a database programmer, just a dirty hacker, so I don't mind if the answer isn't 'pure'.

 

[torok]

Thanks Pat - that was an incredibly informative reply - some of that stuff I hadn't even thought of. I still have a problem though. My tables are like this: Invoice ->many Orders
Orders->many OrderItems
Orders->many OrderLocations
OrderItems->Many taxes.

If I want to input a new Order and add items to it, and put that capability on a single form, it looks like a nightmare. Right now I've got textboxes for the Order fields, a subdatasheet for OrderItems, and a subdatasheet for OrderLocations. If I want to add taxes to OrderItems, I'm looking at.. *another* subdatasheet somewhere?

Can anyone suggest a better way to enter Order information, that's not so confusing, and implements the idea of a Taxes table such as Pat suggested?

 

[torok]

My apologies, my explanation must have been flawed. Each order can have many items, and each order can also have many locations.

So, first thing you do is create an order - basically a laybill. The truck delivering these items might go to several of the client's locations in the process of delivering these items - sometimes the job requires simply going to one location, picking something up, and taking it to another location. It is very important that we record the different locations that our truck went to. Items that are typically on an order include a truck (sometimes billed by the hour, sometimes by the kilometre, sometimes taxed, sometimes not depending on the contract and how it is paid for), along with several items that are to be picked up or delivered. Items are not products that we are selling - they are trucks billed by the hour/km, assistants billed by the hour, third-party charges from maybe something we've purchased on behalf of our client and are delivering - stuff like that, so we can't just make a product list and choose from it, we have to have a separate orderitems table that we add to for every order. (It has itemNumber, orderNumber, ItemDescription, quantity, unit price, etc etc - sometimes an item is a truck, sometimes it's something we bought and are delivering on behalf of a client). It also needs to somehow keep track of all the taxes on an item. At the moment there are only two possible taxes for an item, so I've made two boolean fields, tax1 and tax2, the values of which I look up in a tax table upon invoice creation. Ideally, I'd have a separate tax table as you suggested, but I've mentioned why this would be difficult.

Once you've got a bunch of orders, you might decide it's time to make an invoice. You pick a client to invoice, and the program shows you all the order for that client that have not been invoiced. At this point, you may choose one or more orders to add to the invoice. The program then calculates the value of the invoice by adding up the values of the orderitems on the orders that you have put on the invoice, (along with the separate taxes so that you know how much tax1 and how much tax2 you have), then creates a record in the invoice table while updating the InvoiceNum field for the chosen Orders in the Orders table.


Does that make sense? It's what my program does right now, and it works, but for obvious reasons I don't like the design very much, and when a get a couple thousand records in it it's going to start slowing down because invoice creation requires a whole lot of queries and summaries.

 

[torok]

Thanks Pat, you're amazing as ever ... but I still don't quite understand.

The orders are added to an invoice just as you suggested - it seemed like the best way. But I don't quite understand what you mean by "A datasheet subform for order items and a subdatasheet for the item taxes". Do you mean two datasheet subforms, one for items and another for the item taxes? If so, then maybe a continuous forms subform for taxes would be best so that I can use comboboxes to select the taxes that apply to an item.

Thoughts?

 

[torok]

?

I think you misunderstood the question (or I misunderstood the answer). I can see why comboboxes would be a problem for adding orders to an invoice, and that is why I didn't use them. I use a multi-selection listbox.

I'm concerned about the other form - the one that adds orderItems to Orders, and Taxes to the orderItems. You had suggested that I use a main Form for Orders, with a "subform consisting of a datasheet" for the OrderItems. This I already do. Then you mentioned that I should use a "subdatasheet" to add taxes to each Item. By "subdatasheet", are you suggesting that I have a main form for Orders, with *two* datasheet subforms on it, one for items and one for each item's taxes? Or is there some other way of integrating forms that I haven't thought of?

Again, thanks very much for your advice on this Pat, I appreciate it more than I can convey.

 

[torok]

Ok, NOW I understand! That is indeed a good idea - I'm using A2K, but I hadn't thought of doing it that way. Thanks Pat! The only problem I have left is explaining to my clients how subdatasheets work I see that there's an "expanded" property - I assume I can set the subdatasheets to be automagically expanded so my clients don't forget to select taxes, but if not they'll figure it out.

Again, can't thank you enough for your patience and your help.

Tom