Results 1 to 8 of 8
  1. #1
    Join Date
    May 2008
    Posts
    48

    Unanswered: Build Query to get On Date & MTD figures simultaneously

    I am having a database, in which, one table consists date wise production.

    I wish to have production figure of a paricular date as well as MTD simulatenously, through query, for which, I want to run a form, which asks the user to enter a date, (for which report is required) and that too give the MTD fugure, considering the month of particular date asked.

    Can anybody suggest me how these figures can be derived at a one time through query?

    Thanking you in anticipation of early response.

  2. #2
    Join Date
    May 2009
    Posts
    258
    You should be able to use a UNION to get the information. Assuming the table name is "Production" and has columns "Date" and "Value":

    Code:
    SELECT 'Day' AS Interval, Value FROM Production
    WHERE Date = DateValue([Please enter the date:])
    UNION
    SELECT 'MTD', SUM(Value) FROM Production
    WHERE Month(Date) = Month(DateValue([Please enter the date:]))
    Regards,

    Ax

  3. #3
    Join Date
    May 2008
    Posts
    48
    Thanks for your prompt reply.

    However, I do not want the output on date wise. I require total production of a particular day (say 10th April'09) along with MTD production of April'09. So query needs modification.

    Please help me by modifying the query accordingly.

    Thanks & Regards,

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what is the MTD
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Probably Month-to-date... at a guess.... but that's guessing
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  6. #6
    Join Date
    May 2008
    Posts
    48
    Exactly, MTD means (Month-to-date) i.e. Cumulative figure for the month.

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    sanjaylml, I can't give you the exact Access syntax but the basic SQL could be something like:
    Code:
    SELECT SUM( IIF(date-col = #2009-05-28#, value-col, 0) ) as MTD
         , SUM( value-col) as Total_Month
    FROM table-name
    WHERE date-col between #2009-05-01# and #2009-05-31#
    I hard-coded the date values but you can substitute formula to calculate the First and Last day of month based on the date specified.

  8. #8
    Join Date
    May 2009
    Posts
    258
    However, I do not want the output on date wise.
    This is confusing, because you also said:
    I wish to have production figure of a paricular date as well as MTD simulatenously
    Please clarify what it is you want, if not what you are getting.

    Here's an updated query, using Stealth's as a base, since it figures the totals better:
    Code:
    SELECT SUM(IIf([DateInTable] = DateValue([DateEnteredByUser]), ValueInTable, 0)) AS 'Total_On_Date',
    SUM(ValueInTable) AS Total_Month
    FROM ProductionTable
    WHERE Format([DateInTable], 'yyyymm') = Format([DateEnteredByUser], 'yyyymm')
    Please remember to change table names, column names, and also the reference to the date entered by the user to meet your specific needs.

    Ax

Posting Permissions

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