Results 1 to 15 of 15
  1. #1
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    crosstab query reports (97 sr2)

    Can anyone tell me if this is even possible.

    I have a crosstab query as the source for a report. The report (all unbound fields)is a list of all sales reps and their net sales for the month. Works fine, the headings only appear when there is data in the fields, all the fields appear in the detail, it totals all monthly numbers in the footer just the way I need it to.

    Here is the problem, I have to be able to add another row of data in the footer, monthly adjustments. I have these in another table. I can't for the life of me get this to appear in the footer. I then need to add the adjustments to the monthly totals. So the totals in the footer would be all the sales for the month and the adjustments.

    I am going crazy and I am not even sure if this is possible to do.

    Thanks is advance
    Cathy

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

    Re: crosstab query reports (97 sr2)

    I'd create a separate query that calculates the sum of the monthly totals and the adjustments, and use this as record source for a subreport that you place in the main report's footer.

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: crosstab query reports (97 sr2)

    Thanks I will try that

  4. #4
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: crosstab query reports (97 sr2)

    Hans
    I am using dynamic column headings, would you still use the subreport? If not can you suggest any other way you might do this.
    thanks
    Cathy

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

    Re: crosstab query reports (97 sr2)

    Are you using code to manage the dynamic column headings? If so, you could add code to populate text boxes with the sum of the monthly totals and the adjustments (without using a subreport). How to do this exactly would depend on the code you already have.

  6. #6
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: crosstab query reports (97 sr2)

    This is the code I am using and I must admit I copied it from another application, This works perfectly, I need to add adjustments row to the footer and have them become part of the total.
    Attached Files Attached Files

  7. #7
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: crosstab query reports (97 sr2)

    Hans
    Also would the adjustments have to part of the crosstab query.. that seemed to be a stumbling block for me.

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

    Re: crosstab query reports (97 sr2)

    Where do the adjustments come from and how can we link them to the dynamic columns in the report?

  9. #9
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: crosstab query reports (97 sr2)

    The MONTH END SALES FOR REPORT crosstab consists of all the reps and their monthly sales. I am not sure where to put the adjustments. It would be one adjustment per month not one per rep. I am at the stage that I can put them anywhere.
    Thanks

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

    Re: crosstab query reports (97 sr2)

    Please provide more detailed information, or attach a stripped down and zipped copy of the database - it's difficult for me to visualize exactly what you want.

  11. #11
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: crosstab query reports (97 sr2)

    Here is a stripped down version. I need the adjustments to appear in the line above totals and to be included in the total. Again I am not sure where the adjustments should be right now they are in their own table. One adjustment per month.

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

    Re: crosstab query reports (97 sr2)

    Sorry, I don't see an attachment. Make sure that the zip file is not larger than 100 KB and that you don't preview your post after specifying the attachment.

  13. #13
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: crosstab query reports (97 sr2)

    <P ID="nt"><font size=-1>(No Text)</font>
    Attached Files Attached Files

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

    Re: crosstab query reports (97 sr2)

    See the attached version. I added a query qryAdjustments, and I added text boxes and code to the report.

    You may have to correct the references in VBA - I had to convert the database to Access 2002 and back.
    Attached Files Attached Files

  15. #15
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    New Jersey, USA
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: crosstab query reports (97 sr2)

    Thanks for all of your help!!! I would be lost without you.
    Cathy

Posting Permissions

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