Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Crosstab Query (Sort Descending) (Access 2003/SP 2

    This is my first time dabbling in crosstab queries, so bear with me.

    I have a crosstab query that all I need to analyze is the number of orders (I thought I could Count my OrderID field for this) but it doesn't seem to work. It returns a sum of 12,780 when the actual number of orders is 784. The 'tract' column is summing correctly, however I would like to sort descending but can't get that to work. I've looked in my Access 2000 Complete Reference Manual and it has very little about crosstabs. My Access 2 Developer's Guide just goes through a lengthy process of making a form for a user to build a crosstab.

    What I need is this: I need to know the total number of orders between 2 dates and a total number of products between 2 dates.

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

    Re: Crosstab Query (Sort Descending) (Access 2003/SP 2

    I don't understand why you created a crosstab query. You use Category as column heading, but restrict this field to the value "tract", so you don't really use the column headings at all. You might as well use a standard Totals query.

    Because your query includes 'qryTract Worker Order Details', it counts the records for a specific OrderID in this query.

    I'm not sure what exactly you want to accomplish. Your stated goal "I need to know the total number of orders between 2 dates" seems much simpler, so I'm probably missing something.

  3. #3
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query (Sort Descending) (Access 2003/

    Well, that's why I posted. I thought that a crosstab query was mainly for 'statistics' and thus I thought it would be good for a year-end report.

    The restriction to 'tract' is because that's all my boss wanted for his report. After spending several hours on this project, I decided to just quickly do individual queries and got the information I needed and pasted it into Excel. Thanks for your help.

    When does one normally use a crosstab query?

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

    Re: Crosstab Query (Sort Descending) (Access 2003/

    A crosstab query is used if you want to aggregate (sum, count, average) data over two variables - one down the rows, another across the columns. See the screenshot below, using data from the Northwind sample database. Total freight for the orders is shown by employee (row header) and by shipper (column header). The column headers are not field names, but values from the shipper field.

  5. #5
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Moundridge, Kansas, USA
    Posts
    342
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Crosstab Query (Sort Descending) (Access 2003/

    Thanks! Have a happy new year!! <img src=/S/time.gif border=0 alt=time width=32 height=48>

Posting Permissions

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