Basic Q: Having 'total' amount in table updated from subtable?

[Sharky II]

Hi guys,

I'm doing some simple Access db stuff after many years out of the game. Unfortunately, i've forgotten a lot!

I'm making a simple invoicing database with (currently) two tables with a one-to-many relationship: one Invoice can have many InvoiceItems (and each Invoice Item has a price).

I have an Invoice form with an InvoiceItems subform embedded, which is a continuous form.

My question is pretty simple: should i have 'summed' values derived from simple equations from the 'Totalcosts' as a field in the main Invoice table, or is it not required?

Obviously the user needs to see this information but i guess that doesn't mean that it needs to be in the basic invoice table fields. It could just exist as a form item/control?

I can create a text box and calculate/display this information on the main form, eg i just created a 'total number of items' text box in the main form, so i could just do the same with the amount(s).

My current thought is that it is not necessary for things like 'totalcost' to be in the main Invoice table, as it will always be viewed as a form or a report anyway...? Is there any downside to my line of thought? Or should i be trying to dynamically update the main table cell from the child table?

Thanks for any advice you can give - sorry for such a basic question!

Eddie

 

[pbaldy]

Normalization rules would argue against storing the field. I can say from experience that it can cause trouble. An early database I created includes the total field in the main table. I ended up creating a process to check for imbalances every night because users kept finding ways around the code designed to keep that field in balance with the detail table. At least once a month the system emails me that it's found a problem, and I have to research with the users what the correction needs to be. If I created a similar system today, I would not have that field.

 

[Lightwave]

In the majority of instances you would not store any calculated fields or total fields in a table. However in accounting packages it is standard practice to store the net price the tax rate (goods and services/VAT) and the total. Such rates change over time but companies are legally required to store the amount at time charge and not at the latest rate that may change over time.

Accounting packages tend to be unusual in that respect.

I might do a half way house in your case.
Each record in sub form would have a total which would be a calculated stored value
But the main form would not have a total and the total of all the sub records would be calculated..

 

[bparkinson]

I agree with Lightwave on this one. And I'd go further - for an order I'd store the order total (derived from summing the order items prices). I would also store the customer name, ship-to address and so forth in the order table. Customers change their names by marrying, their address changes, and so forth. You can update the customer data in a customer table, and still have accurate order history going forward.

As to the derived order total money remaining accurate, I'd put a trigger behind the order item table to recalculate the total and store it in the order table whenever an order item was added or updated. If your back end is not SQL Server you could do the same thing using the After Insert and After Update events for the order item form.

 

[Sharky II]

Hi guys - thanks for your comments.

I went with not having the total of each Transaction stored in the main table - largely because i wasn't too sure on how to do this dynamically each time!

I didn't think there was any issue, until i recently started working on the search mechanism for the database. It's a Dynamic Query Search.

However as the money totals don't 'really exist', the user can no longer search for e.g. 'all transactions which total between £100 and £1000', because they are just form fields.

Can anyone think of a clever way around this, or have any advice on how i should proceed?

Many thanks!

 

[pbaldy]

One way:

SELECT InvoiceNum, Sum(AmountField) As TotalSale
FROM TableName
GROUP BY InvoiceNum
HAVING Sum(AmountField) Between 100 And 1000

 

[Sharky II]

Hey there,

I'm having a little trouble working out what you mean by the field names. I have a 'Transaction' table, and a TransactionItem table, and one Transaction may have more than one TransactionItem.

Each of these have their respective ID's.

What is InvoiceNum? Is it the TransactionID, the TransactionItemID, or something else?

I assume TotalSale is the name of the sum you're creating on the fly.

Sorry for asking such basic questions, still getting into the swing of things again.

Many thanks!

 

[pbaldy]

Whatever field links those two tables, presumably TransactionID. Yes, TotalSale is called an alias, a name for a field made up on the fly. In design view the same thing would look like:

TotalSale: AmountField

With a Sum on the totals line.

 

[Sharky II]

Hi pbaldy,

Thank you! I can get it to work in a test query, e.g.:

SELECT TransactionItem.TransactionID, Sum(TransactionItem.ItemTotal) AS TotalSale
FROM TransactionItem
GROUP BY TransactionItem.TransactionID
HAVING (((Sum(TransactionItem.ItemTotal)) Between 100 And 7000));

But i'm a little confused as how to integrate this into my Dynamic Query Search code. It searches 'TransactionQuery' (includes all fields of Transaction & TransactionItem except the foreign key of TransactionItem) and creates 'Dynamic_Query' which contains the results of the search.

I was thinking that i should add this as a field to the TransactionQuery query, which the entire form is based off? How would i add the above code to the

Or perhaps this should be generated by the dynamic query VBA code? I can't quite get my head around it. If that's the case, I'm confused as to how to modify my VBA (which i use to search between two ranges):

If Not IsNull(Me![SrchItemTotalTo]) Then
    If Not IsNull(Me![SrchItemTotalFrom]) Then
        where = where & " AND [TotalAmount] between " + _
        Format(Me![SrchItemTotalFrom]) + " AND " & Format(Me![SrchItemTotalTo]) _
        & ""
    Else
        where = where
    End If
End If

Once i understand this i should be able to calculate simpler things such as number of TransactionItems within a Transaction etc...

Thanks for any help/advice that you could give!

Eddie

 

[pbaldy]

What you could do is take the HAVING clause out so that just sums up the items, then join it to your TransactionQuery so you could have a field in that for the total amount. Then you can search that field in your dynamic search code.

 

[Sharky II]

Hey, thanks again - im nearly there but not quite. I have my query which is what the search mechanism uses as a 'base'. I'm adding my alias to that, but I'm not sure how to do the join, with the alias. I have one alias (will be three in finished thing):

Sum(TransactionItem.ItemTotal) AS TotalTransactionCost

I'll have three of these, but obviously they need joining to work properly? Currently it just displays the item totals, not the sum of all item totals given a particular ID (all items with transactionID 7, or ehatever)

Here is my current join line, which obviously joins my two tables:

FROM [Transaction] INNER JOIN TransactionItem ON Transaction.TransactionID=TransactionItem.TransactionID
GROUP BY <all fields other than the aliases>

Could you suggest how I would modify my from/join lineup incorporate these aliases, so I can search them? Thanks!!

 

[Sharky II]

Hi, does anyone have any advice? Perhaps it'd be better if i take this question to a new post in the relevant forum

Thanks!

 

[pbaldy]

Perhaps you could post a sample db here with the results you expect to get.

 

[Sharky II]

Hi pbaldy - i've continued this over in another thread: http://www.access-programmers.co.uk/forums/showthread.php?t=213951

Please let me know if it's still best to post up a sample db. I'm just trying to get a few 'calculated' aliases to be fields that are searchable.

Thanks!