I am an old hand in the world of Excel but with numerous oversized, unstable excel spreadsheets in our company thought I better get to grips with Access 2007 and evolve!

We are a car logistics company and I am trying to build a database that will manage the movement of each vehicle through certain set processes and then provide invoice backups to allow us to charge out work complete.

I have been going through online tututorials and am now pretty sure I have set up correct tables including a master table for each of the car records, customer, valeting, inspection, transport and other similar tables.

Where however I am currently stuck is that when we used Excel I would use certain data to edit others. For example the cost of moving a car would depend on a)the mileage, b)the transport company and c) the number of cars being moved in the batch. In excel I would use If functions or similar to draw on other cells to then calculate the answer. This answer would then fit into a cell in the same row as the vehicle eg. Transport Cost = £100.

I have come across 'action queries' in Access which can update tables using formulas etc. but there also seems to be a lot of info online suggesting queries are a better way to go.

Can anyone point me in the right direction? Ideally getting me to an end result where I can pull information from different tables, put it all into a function and then spit out a financail result which gets stored against the record.

Many thanks!!!!

As a general principle, you don't store computed values into tables. Those values are computed when you need them, in a query, a form or a report.

Imagine that we have a table "Sales" having one column named "Unit_Price" and one column names "Quantity". If, on a form or a report, you want to have the unit price, the quantity and the total price, you can create a query:
You can then use this query to "feed" the form or the report, using their RecordSource property. You can also perform the computations directly into the form or the report.

Just a piece of advice: Try to refrain from using spaces or other non alpha-numeric characters (the underscore "_" is OK) in the names of the objects (Tables, Columns, Forms, Reports, etc.) in your databases, as well as using words that are reserved in Access (Date, Type, Sum, etc. for a full list see: Access 2007 reserved words and symbols - Access - Office.com). Otherwise you'll have problems, sooner or later.

Thanks, so however if I only calculate price with a query how do I store them if I ever look back. So in my case against each vehicle I want to invoice certain jobs which are predefined. In excel I would have stored them as Price for Job A, Date for Job A, Price for Job B...

Then I can look back and see this information at any date in the future.

Ben

You don't store the computed values at all. Every time you need the computed values you reopen the query. This is because Access lacks of triggers (Triggers in SQL SERVER.) or computed columns (Computed Columns). In more powerful database systems, a trigger is a "rule" that forces an action or an operation to be done every time data are added, modified or deleted from a table. Computed columns are very similar to what you have in Excel: the value in a column depends on an expression applied to one or several other columns. Access missing both means that you cannot garantee the integrity of a computed value: if the value is changed directly into a table, there will be no automatic recalculation of the dependent computed value.

Invoices represent a special case because once an invoice is issued you're not supposed to ever change the related data. Personally, this is the only case where I store computed values, sort of. When an invoice is issued, it is stored (as a whole or as columns in a distinct table). Be aware that depending on the place where you live there can be legal obligations concerning invoices (Sarbanes–Oxley Act, etc.).

Thank you that's great. I now have the form working well (attached) The next stage is to try and create a charge when ASL Inspection, Valet Complete etc. are ticked. Presumably I would use a query for this. Could anyone help me build the query?

Once that's done I want to run an invoice against each vehicle inclding:
Collection Price
Valet
ASL Inspection
Exit Price

Where once an invoice is run it can't be run again. Could anyone point me in the right direction?
Thanks!!

if you are invoicing then there is a speacil case on storing derived values. if your invoices are to have any meaning (ie worth storing) you MUST break the rules about storing derived values

you need to store various bits of informatio so that when you refer back to the invoice its identical to teh one sent out to the customer
detail of the product (the product description could change over time even if its a minor change it must be the same as described on the original invoice
the price charged whether thats the proice charged, the original price less discount, agreed price whatever
either the sales tax levied or the sales tax applicable at the time of invoicing.

although that may look like you are breaking normalisation, its perfectly arguable that the invoice is a new entity and therefore requires its own data.

what I've done in the past is to dump invoices into PDF files, not because PDF's are 'great' but they are a good enough reprsentation of the original invocie that circumvents any of the customer games such as I've lost the invoice or this is smudged I can't read it. any cusotmer queries or delays bang 'em a PDF by email, send a copy by post. no need for an invoicing rn on special paper.


you