February 10th, 2012, 03:26 PM
MS Access Query Calculations
Ok, before all you hardcore SQL Guru's shoot me down in flames for using MS Access 2k10, please bear with me as I imagine that the same theory could possibly be applied to any Database Application.
I am working on a simple Order/Invoicing system for my own personal use (downloadable via MailBigFile here: Download ).
Basically what I am trying to achieve is the following:
- ( Done)Calculate Line Total; Achieved via Update Query;
- ( Struggling)Calculate Quote Total - If you look at my qryQuoteTotalCalc, the Sum of the CostPrice, MarkupAmount etc. does not total up and I need these to update the relevant columns in the tabQuote table.
I could achieve this by using individual queries for each column I want to total, then run the Queries on open/dirty or whatever but I think this is inefficient.
Sorry if I am not explaining this too well, but would appreciate any advice that anyone can give on calculating totals in queries and updating the relevant record table columns. If you have a look at the db it will probably make sense what I am trying to do with the tables etc.
Thanks in advance and any recommendations would be appreciated. I'm willing to donate a virtual pint to whoever can help me!
February 11th, 2012, 06:25 PM
Spent my whole weekend so far working on this - getting somewhere!!!
Have the latest db here: Download
Anyway, final problem, I want to be able to "append" or "update" my quotes marked as invoiced into the relevant Invoice tables, preserving the values.
Having problems achieving this, therefore if anyone could have a look I would be grateful.
Will upload the final version also so that others can use the basis for their own projects.
The db is quite self explanatory, but if you want me to elaborate I will happily do so.
99 views and no input - how about two virtual pints!!?