Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    13

    Unanswered: Doing Calculations on a REPORT OR FORM?

    Hi everyone,

    I have a bit of a problem that I am trying to solve.

    I have 2 queries; one gives year to date total of sales for 2005 and the other for 2004.

    Every few weeks I need to compare the sales from present year to previous, and show the variance. No big deal.

    Both of these numbers come from a query and I figured that I could simply have this done on a report...

    BUT... it hasnt been working for me.

    I cant seem to get the information from both of the queries to even show up on the form so I can start to work with them and try and do some calculations.

    has anyone done something like this before?

    thanks for the help,

    Chad

  2. #2
    Join Date
    Nov 2004
    Posts
    13
    I am constantly messing around with this one with limited results.

    I can think of the concept in my head, but I cant seem to put that into action.

    I have two queries that are limited to 2004 and 2005 year to date sales...

    I want to show both values in a report, subtract them and show the difference, and calculate the percentage difference.

    I cant seem to get started... when I try and add both of the queries to the report I get an error barked back at me saying that cant be done.

    They use the same fields from the same tables and just calculate for different time ranges, I am wondering if this is part of my problem?

    Thanks again,

    Chad

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if the data is coming from the same table, then you could generate a query with the year as a derived column in the data source query.
    group your report on that year value
    create a column in the report detail (or use running totals derived formt eh forms detail event) which contains the sales value for the year and subtract that from the current year.

    HTH

  4. #4
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    WHat about Doing A Union Query Joining data then a Make Table then a Crosstab on the make(made) table then a make table again then show the it in a report

    1 DoCmd.SetWarnings False
    2 DoCmd.RunSQL "MK on the Union query"
    3 Docmd.RunSQL "bla"
    4 DoCmd.RunSQL
    5 DoCmd.SetWarnings True
    6 OPen report of form
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

Posting Permissions

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