Results 1 to 5 of 5

Thread: Crosstab Query

  1. #1
    Join Date
    Jun 2014
    Posts
    12

    Unanswered: Crosstab Query

    I have created a crosstab query based on a date range specified by the user, I need to create an additional field that takes the last date column and subtract it from the average.

    Example:
    Name Date12 Amount Average Diference
    Test 7/1/2013 $469.00 $500.00 $31.00

    any help would be great.

    Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    do you need to do this within a query or could it be handled in a form or report?
    There may be a way of doing this within a query but I suspect it could be messy, however in the absence of what your query is, what the table design is its kind of hard to say.


    Im surprised you need to use a crosstab query for 2, arguably 3 columns thought.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2014
    Posts
    12

    Crosstab Query

    I am creating a report that actually reflects a 12 month period takes an average and then deducts the last month from the average to determine a variance amount and a percentage. What I need to do is add two fields Variance Amt and percentage to the end of the crosstab query take the results and drop it into another table for historical reason. Unfortunately this is not my original design I am coming in as a second developer since the first one left.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Fair enough
    but in the absence of your table and query design and some sample data (ideally as a MDB but at the very worst as a CSV) its a tough call.......


    if 'all' you are doing is running a 12 month average, then do the average as a separate stand alone update query
    ..failing that use the cross tab as a feed stock into a query which pulls the last months actual which does the update
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2014
    Posts
    12

    Crosstab Query

    Thanks figured it out created headings 1 thru 12 and did the calc in the report.

Posting Permissions

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