Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167

    Red face Unanswered: How do I collate reports based upon a common field?

    Recently I was asked to create a past due invoice and letter generator. I now have 2 separate working reports, one generating the dunning letters with details about past due invoices, and the other pulling the actual invoices referenced.

    I tried to combine these by including the invoice report as a subreport in the letter generator report. The problem is that the invoice report has a page footer. When I drag it in as a subreport, it gets rid of the invoice page footer. I tried moving the invoice page footer up into the group footer but then there was no way to insure the footer stayed in a fixed position relative to the bottom of the invoice page, this is because the detail lines offset it depending on if more than one line item shows on the invoice.

    Ideally, I'd just like to have it collate the two reports, sorting them by parent account number. There would always be one letter per account but there might be multiple invoices following that letter. I looked through some previous posts but I couldn't find anything on collating by a similar field. I'm sort of a noob with VB and have very minimal knowledge so far. Any advice would be appreciated, Thanks! Joshua

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Just keep them as separate reports and run them both on clicking a button of some kind (normally an OK or Preview button in a reporting dialog). Each of the reports just needs to pick up the specification of the common field and limit it's results to only that.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    I'm not sure if I understand what you mean. It sounds like you're suggesting I go in and limit the parent account number on the queries the 2 reports are pulling from and print off the letter and then the invoice(s). I already have both reports pulling all the desired dunning letters and invoices. If there were only a few this would be no problem but I'm dealing with hundreds.

    What I need sounds ridiculously simple but I've not seen anything in access that will collate by parent account number. What I would like is a single report returning the following:

    Parent account 1 dunning letter (from report 1)
    Parent account 1 invoice(s) (from report 2)
    Parent account 2 dunning letter (from report 1)
    Parent account 2 invoice(s) (from report 2)
    And would continue until final account.

    Thanks

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    create a top report which does nothing except go through the query
    allow grouping ont he customer number
    in the detail section add the two reports as sub report to the new report.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    healdem,
    That's a good idea but I think I'd end up with the problem I had previously, It would either discard the subreport page footer (with the invoice totals), or I could move that subreport page footer into the detail footer but then it would not stay in a fixed position relative to the bottom of that page. Joshua

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what happens if you insert the second report in the first reports report footer with a form feed immediately before it
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    Right now I have the second report in the first report's group footer and I put a page break right before it. This worked for starting the accompanying invoices on the next page, however the bottom of the sub-report (invoice) is still floating depending upon the number of details per page. It would work perfectly if somehow you could fix the sub-report's group footer to the bottom of the invoice pages.

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    theres a limit to what you can do in Access reports.
    it sounds like you are on or near that limit.

    one last fianl thing you could consider is laying out a new top level report as an A3 report, in the left hand side you put the first report, the right hand side you put the second report.

    after that Im out of ideas. you may need to go to your users and tell 'em they can't have what they think they want. in all honesty unless they are seriously anal I suspect its nto a major issue for them. usually if you can present the information they need thats fine. I've only ever found it an issue when you have a prat as 'da man' in charge
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    in all honesty unless they are seriously anal I suspect its nto a major issue for them. usually if you can present the information they need thats fine. I've only ever found it an issue when you have a prat as 'da man' in charge
    Agreed there.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    The problem is that we're going to be sending these to a group of customers who already look for every excuse not to pay their bills. We have a set format we have always used and unfortunately we are bound to that format, especially as my program is intended to generate re-prints of the original invoices, in which that detail is on the bottom. I can't believe there is nothing that can be done to collate two reports. That seems like such a simple task compared with other things Access is capeable of. Argh...

  11. #11
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Don't blame Access,... it's definitely not a normal thing to try to do. You don't get reports and then start trying to "process" or collate them. That's just impossible.

    The normal thing to do is to process your data from your tables and queries into a report structure that gives you the result you want.

    I personally can't see the problem of just leaving your reports as they are (since you say they work perfectly) ; having "dunning letters" and "invoices" as separate reports, then using VBA to go through a recordset, looping through on this "common field", printing each report in turn with the value for the common field and then moving on to the next one.

    IE (Pseudocode)

    Code:
    Get all the values of the common field
    Go to first common field value
    Do
       Print dunning letter for current common field
       Print invoice for current common field
       Move to the next common field value
    Loop Until No More Left
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  12. #12
    Join Date
    Jan 2009
    Location
    Ohio, United States
    Posts
    167
    I don't know about it not a normal thing to do, I'm fairly new to access and use it to run multiple reports for multiple sales reps every period. So far I've been able to get by using sub-reports to combine reports. It seems like Microsoft knew the need to combine reports (as addressed with sub-reports), however, they did not anticipate the need to have a conditional page footer or fixed sub-report detail footer.

    I'd be open to using VB, however, I don't know what I'm doing in it now. If you or someone could provide more detailed sample code I'd be very happy. Thanks!

Posting Permissions

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