Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2008
    Location
    Suburb of Chicago, Illinois
    Posts
    56

    Unanswered: Access 2003 - Crosstab query prob

    MS Access 2003
    I have a crosstab query that puts data into a table as follows:
    Customer Name Machine Meter Read 2010-2 2010-1 2009-12 2009-11
    ABC Corp 100 A C 4800 4200 4000 3200
    ABC Corp 100 A T 5000 3000
    ABC Corp 200 A C 4800 4200 4000 3200
    ABC Corp 200 B C 5000
    XYZ Co 700 A T 1900
    XYZ Co 700 A C 1200 0 400 50
    XYZ Co 900 A C 250 100

    I need to select the higher of C or T when T is present so the data looks like:
    Customer Name Machine Meter 2010-2 2010-1 2009-12 2009-11
    ABC Corp 100 A 5000 4200 4000 3200
    ABC Corp 200 A 4800 4200 4000 3200
    ABC Corp 200 B 5000
    XYZ Co 700 A 1900 0 400 50
    XYZ Co 900 A 250 100

    Note: I do not need the “Read” field but when I don’t use it in my crosstab I don’t get the correct data.
    How should I structure a new query (or modify the crosstab) to make that happen?
    Last edited by KrazyKasper; 03-02-10 at 18:22. Reason: Format data

  2. #2
    Join Date
    Oct 2009
    Posts
    340
    you need to preprocess your data set before the crosstab query.

    AggregateQuery to group and select max for the key field that needs to be selected - - -

    am no big fan of crosstabs - as one can not go infinitely left to right - and dbs records are stacked....it is just a layout tool.....but the crosstab isn't really your issue here...first you have to get your dataset correctly and then rely on crosstab for layout purposes.....

Posting Permissions

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