Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    crosstab query (and report) (2000)

    Hi, I am struggling with creating a complicated crosstab query. I have data with "jobs" (which relate to the sale of products), which are themselves within categories (of products). I have the forms working fine, and a report that I can generate summarising: first by category, then by product, then grouped by month, the total number of products, total cost, and total sales. This leads to column headings of number, cost and sales. And row headings for the months. The problem is that the customer wants the report with month column headings and number, cost and sales as rows. I posted this question a week ago, and Charlotte said to look at the crosstab query. I have given this a go, but can't get my head around solving my issue. I need to be able to group first by category, then by product. Then for each product sum for each month the units, sale and cost data. And end up with the report. Can I actually achieve this level of detail with a crosstab query? If so, could you please give me some more detailed information on the settings in the wizard, or what I need to do to the design after creating the query initially in the wizard?

    Thanks for any assistance you may be able to offer.

    Roger

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

    Re: crosstab query (and report) (2000)

    Could you post a mockup of what you want to achieve, for example in the form of an Excel spreadsheet, or a stripped down version of the database (see <post#=378681>post 378681</post#> for instructions), or both?

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: crosstab query (and report) (2000)

    I could send a stripped down copy, but it is secured. How can I remove the security? Or can I also send the .mdw file too, with a username and password?

    Roger

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

    Re: crosstab query (and report) (2000)

    You shouldn't give us passwords etc. Can you copy the tables and queries we need to see into a new database? (We probably don't need any forms, reports etc.)

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Christchurch
    Posts
    111
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: crosstab query (and report) (2000)

    Hi Hans, I've converted it to Access 97 version, which has removed the security. There is one report remaining in the sample, Product Sales. You will see on the first page, the "Contracting" section, immediately followed by the first "Category" (Contracting), and only one "Product" within this category (Pass Throughs). It is the section below this, the number, cost, sell and GP data, that I need to turn into row headings, with the months becoming column headings.

    (I have just realised that this is an additional level of grouping than I had previously mentioned. CostCentre, then Category, then Product, and finally by Date/month).
    Attached Files Attached Files

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

    Re: crosstab query (and report) (2000)

    I don't know if I took the most efficient approach; the solution I came up with is a lot of work. It consists of three separate crosstab queries (one each for quantity, cost and sell), plus a select query to combine their results. The report is based on the select query.

    See the attached database.
    Attached Files Attached Files

Posting Permissions

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