Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2007
    Posts
    93

    Unanswered: add fields from crosstab query

    hello folks

    i am trying to sum up 3 fields from crosstab query based on the criteria in a query

    i want to sum the 3 fields depending upon the present month

    like in cross tab u have something like this

    2008 jan feb ........oct nov dec
    100 300 600 900

    2009 jan feb
    1000

    the query shuld return to me the result of jst last 3 months i.e 900 +1000=1900

    i am struck and coudnt make out the further steps
    any help would be appreciated

    thanks in advance

  2. #2
    Join Date
    Sep 2005
    Posts
    19
    are you trying to do this within the xtab or in a seperate query?

    so Jan '09 data gets added to Dec '08 data?

  3. #3
    Join Date
    Nov 2007
    Posts
    93

    cross tab query

    i am trying to get the results in the new query

  4. #4
    Join Date
    Sep 2005
    Posts
    19
    is your data stored as shown in the original post - ie, you have a year field, month field and a qty field or are they the result of some other calcs you do?

  5. #5
    Join Date
    Nov 2007
    Posts
    93

    cross tab query

    yes i am getting the data from a table and with respect to that table the cross tab query is made in a query called crosstab query

    the data availabe in the table is the quantity and invoice date and offcourse a primary field , so with respect to that i made a cross tab query

    now i want the query which shows me jst last 3 months quantity sold

    i made the query in 2008 like nz(oct,0)+nz(....)... it was working good bt now the year is changed so my query isnt working and i am trying to automate but hv no clues so as wht to do

    any suggestions would be appreciated

    thanks,
    sam

  6. #6
    Join Date
    Sep 2005
    Posts
    19
    try something like this:

    SELECT Sum(a.qty) AS total
    FROM [SELECT top 3 DateValue([mytable]![month] & "/" & [mytable]![year]) as mon_yr, mytable.qty
    FROM mytable
    GROUP BY DateValue([mytable]![month] & "/" & [mytable]![year]), mytable.qty
    ORDER BY DateValue([mytable]![month] & "/" & [mytable]![year]) DESC]. AS a;

  7. #7
    Join Date
    Nov 2007
    Posts
    93

    cross tab query

    i tried working with the concept u said .. but to vain

    i took the 3 fields Item(primary key),quantity,invoice_date
    but the quantity isnt summing up for last 3 months

    any clues which way to shuld i proceed futher ??

    thanks,
    sam

  8. #8
    Join Date
    Sep 2005
    Posts
    19
    Quote Originally Posted by sam_01
    i tried working with the concept u said .. but to vain

    i took the 3 fields Item(primary key),quantity,invoice_date
    but the quantity isnt summing up for last 3 months

    any clues which way to shuld i proceed futher ??

    thanks,
    sam
    Ok now we're finally getting somewhere. You have an invoice date. Somehow you manage to change that to a month, year combination for your crosstab. How do you do that?

    In fact, could you post the sql for your crosstab? that would help me help you.

Posting Permissions

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