Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Cross tabs (2000)

    I have a sum query that produces the following data

    Company (group by)
    Year (group by)
    Order Date (first)
    Sales (sum)

    What I need is a cross tab query/report that looks like the following

    Company Year 1 Year 2 ------------- Year n (only 6 years will be shown)
    1st ord Date - sum Sales 1st ord Date - sum Sales 1st ord Date - sum Sales

    In other words, it shows for each company (row headings) the first order date of a given year and the total sales for that year (year is the column heading)

    I can get the sales by company/year easily. The problem lies in getting the date into the query and or the report. Does anyone have any ideas.

  2. #2
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross tabs (2000)

    Peter,
    I don't completely understand how you want your crosstab formatted, so I created a dummy database and a query, hopefully it is *close* to what you hope to achieve. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cross tabs (2000)

    I don't understand what exactly you want to do with the first order date. If you want Year1 ... Year6 as column headers, where does "the first order date of a given year" fit in? You can't put the first order date for each year in a row heading if you have the yeard as column headings (or rather, you can but it makes little sense). Please give a more complete description or example.

    (Jeremy, your demo doesn't have years as column headings, and it doesn't have the first order date in any year)

  4. #4
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross tabs (2000)

    This line confuses me:
    <hr>1st ord Date - sum Sales 1st ord Date - sum Sales 1st ord Date - sum Sales<hr>
    However, since I guess I went off on my own little tangent instead of following his stipulations, here's a second go-around....
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cross tabs (2000)

    Peter will have to tell us what he intended.

    Are there male ones?

  6. #6
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Cross tabs (2000)

    Hans and Jeremy

    I am attaching a word document that hop[efully describes the report a bit better. I am not sure that cross tabs will even work in this case.

    What the user wants is 6 columns across the page representing the years (current year to Current year -5). Each of these columns has 2 sub columns. The first sub column displays the 1st order date for a customer in a given year and the second sub column display the total sales for the given year.

    Hope this describes it better.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cross tabs (2000)

    That was not at all clear from your initial post. You can create two crosstab queries, one for the first date and one for the sales, and combine them in a third query. See the attached modified version of Jeremy's database.

  8. #8
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cross tabs (2000)

    Hans,
    I though of that in the "think tank" this morning...having two columns as you did. I guess I'm just too slow.... ah well, too bad not all of us can have 12 arms like you <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    <img src=/S/hansv.gif border=0 alt=HansV width=27 height=26> - Look at him go!
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What&#39;s more, you deserve to be hacked." -Richard Clarke

Posting Permissions

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