Results 1 to 3 of 3
  1. #1
    Join Date
    May 2013

    Unanswered: Plan Vs Actual Date Make Table Query

    I need help on comparing dates to use in either updating a table or making a new table.

    I have 2 tables,

    One contains plan costs by date (mm/dd/yyyy) for a specific project.
    The second one contains actual invoice amounts by date (ddmmyyyy) for a specific project.

    I would like to compare the data summed by project by month by type of transaction Plan and Actuals.

    When I try to compare I receive many errors, because of using format to use only the month and year matching up the records.

    Is there a better way??

  2. #2
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    Quote Originally Posted by dhenderson View Post
    Is there a better way??
    but we'd need to know your table design(s)
    Im guessing once you have a match you then record that match and then elimianate thoise rows from future matches.
    the problem with this sort of process is that unless you always get a one for one match (ie actual costs/invoices always match planned costs there is a limit to what you can do automatically. depending on the project you may be able to match a proportion of invoicess but I doubt you will ever be able to match all. so whats needed is some form of two stage process
    match thiose that you can (where there is one or more pieces of information that allow for a match, do so, then flag them as matched. then the rest get handled by a manual process requiring a user to match up items.
    you need to take a view on how good a match must be before its ruled out/in. ferinstance you could say match on price AND quantity AND within 14 days of planned date. the problem is that an actual invoice may only arrive many days after delivery/supply so ideally you need the supply date
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    May 2013
    In answer to you thought about the table design......The first table for plan costs has the following fields: Project Number, Date (entered in Month/Year); Percentage, Total w/Contingency, Costs, Actuals DateLastUpdated. The Cost is calculated by Percentage * Total w/Contingency.

    The second table is Project Number, Invoice Date (mm/dd/yyyy) and SumofInvoiceAmount.

    What I would really like to do is match the "dates" by Month/Year and update the Actuals field on the first table with the SumofInvoiceAmount from the second table.

    But then the harder part comes when I would like to create a crosstab query that would display the plan by month and then the costs by month underneath (all by Project Number). My table design my not be good for that.

Posting Permissions

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