Results 1 to 6 of 6

Thread: Newbie Help!

  1. #1
    Join Date
    Jan 2012
    Posts
    12

    Unanswered: Newbie Help!

    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!!!!

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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:
    Code:
    SELECT Sales.Unit_Price, Sales.Quantity, Sales.Unit_Price * Sales.Quantity AS Total_Price
    FROM Sales;
    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.
    Have a nice day!

  3. #3
    Join Date
    Jan 2012
    Posts
    12

    Thanks

    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

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by bchase View Post
    Thanks, so however if I only calculate price with a query how do I store them if I ever look back.
    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.

    Quote Originally Posted by bchase View Post
    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...
    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.).
    Have a nice day!

  5. #5
    Join Date
    Jan 2012
    Posts
    12
    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!!
    Last edited by bchase; 02-09-12 at 11:36.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •