Results 1 to 13 of 13
  1. #1
    Join Date
    Mar 2004
    Posts
    31

    Unanswered: MS Access question, count distinct numbers and assign each a value

    I have a small Access database that is used internally for the creation of Bills of Lading (BOL's) for shipping.

    The report that is used for printing contains the header info (who it's for, where it is going) and the detail (the actual amount on each pallet, lot #'s etc.)

    The detail can go up to 52 records, and to fit that on one page we had to make the font pretty small.

    What I would like to do is the following, split these into multipage documents, but retain the header info per page. 26 max records per page

    Count the distinct lot #'s (ser_lot_no) as each of them is unique. Add a number to them, in a different column of course, which is numbered between 1 thru 52. Based on that number, 1-26 or 27-52, give them yet another number, 1 or 2 (this is used for the grouping/sorting)

    I have done the above without the distinct count, I just created a new row for them in the detail to add what page they want it on, and it works just fine, but I am trying to make it so they (the shipping staff) don't need to worry about it.

    If we have to use SQL or VBA that is ok, but be gentle, I am not as comfortable with those.

    Thanks in advance of course. If you require more information, or more explanation, let me know.

    DBase is MS Access 2000 format.

  2. #2
    Join Date
    Feb 2004
    Posts
    126
    You should be able to do this relatively easily without modifying your database at all.

    Just add a Group by the BOL # and put the header information in the BOL header (you may already be doing this). Then set the "Force New Page" property of the BOL Footer to "After Section" and Access should take care of the rest.

    you can then adjust the size of the detail section so that 26 fit on each page.

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I don't think you should handle this through SQL. I think it should be handled in your report formatting.

    Try declaring a counter variable in the VB Module for your report. Then add code to your detail section's OnFormat event to increment the variable by one. If the variable is 26, then create a page break and reset the counter to zero.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Mar 2004
    Posts
    31
    I just realized in my original posts that when I mentioned the detail, I did not ALSO mention that it was in a subreport.

    This was necessary to deal with another issue entirely, but I believe that may make your suggestion not possible.

    Or am I wrong?

    blindman, can you expand on your comments recarding the VB Module & the on format? Thanks.

  5. #5
    Join Date
    Feb 2004
    Posts
    126
    Hmmm, you're right, the subreport does add an additional element of "fun" to the situation .

    But honestly, it should still work. Just create the group by BOL and set "Force New Page" to "After Section" in the BOL footer. One thing I forgot to add is the set "Repeat Section" to "Yes" on the header so that it appears on the second page.

    It should still work evne with the subreport because Access should split the report to fit the page and everything else should...just work.

  6. #6
    Join Date
    Mar 2004
    Posts
    31
    Quote Originally Posted by grrr223
    Hmmm, you're right, the subreport does add an additional element of "fun" to the situation .

    But honestly, it should still work. Just create the group by BOL and set "Force New Page" to "After Section" in the BOL footer. One thing I forgot to add is the set "Repeat Section" to "Yes" on the header so that it appears on the second page.

    It should still work evne with the subreport because Access should split the report to fit the page and everything else should...just work.
    Ok, I think we are close. The only thing that needs to be figured out (again should have mentioned it) is that each BOL also has a suffix, so BOL 12345 also has a -1 at the end of it. The suffix is not constant, and could be a 1,2,3.

    I suspect this is more grouping that I need to play with, so I will work on that on my own. Thanks for the help, if I need more I will reply again.

  7. #7
    Join Date
    Feb 2004
    Posts
    126
    Do you want all of those to be one BOL in which case you need to group them? OR do you want them to be separate BOLs.

    If you want to group them, unfortunately, you can't create groups on calculations, but what you can do is add a calculated field to the query the report is based on such as LEFT(BOLNUMBER,5) AS BOLGROUP and group on that field.

  8. #8
    Join Date
    Mar 2004
    Posts
    31
    Luckily, the BOL suffix is an actual field that is groupable, it is not based on a computation.

    Since I worry about the Suffix first, and then the BOL, I have my grouping such that BOL Suffix is first, then BOL. The reason is because the Suffix allows us to deal with very large orders that span trucks, but allow us to use the same MASTER Bol #.

    With this grouping, it almost works, the problem is that the report now shows

    26 records for Bol suffix 1, and 26 records for Bol suffix 2, but it is missing the pages that should be in between for the remaning lots on suffix 1.

    Does that make sense?

  9. #9
    Join Date
    Feb 2004
    Posts
    126
    Are the BOLs and the suffixes numbered like this?

    10000-1
    10000-2

    10001-1

    10002-1
    10002-2

    And then would this make up 3 BOLs? OR do you want to start a new document for each suffix so this would make up 5 printed BOLs?

    Either way, I think you want to group first by BOL and THEN by the suffix. And then you would just have to play with where you want the "Force New Page" property to be set (either after the BOL footer or after the suffix footer).
    Last edited by grrr223; 02-03-05 at 15:28.

  10. #10
    Join Date
    Mar 2004
    Posts
    31
    Quote Originally Posted by grrr223
    Are the BOLs and the suffixes numbered like this?

    10000-1
    10000-2

    10001-1

    10002-1
    10002-2

    And then would this make up 3 BOLs? OR do you want to start a new document for each suffix so this would make up 5 printed BOLs?

    Either way, I think you want to group first by BOL and THEN by the suffix. And then you would just have to play with where you want the "Force New Page" property to be set (either after the BOL footer or after the suffix footer).
    If you were to use the above #'s that would make up 3 BOL's (with 2 of them using multiple trucks)

    I will give that a shot. I have been fussing with it, but I may have another parameter set that I am ignoring that is screwing me up.

    Thanks

  11. #11
    Join Date
    Mar 2004
    Posts
    31
    I have tried as many different combinations of the sort that I could think of, but the suffix is always getting lost. I get two pages, the first with suffix of 1, the second with suffix of 2, but both pages only have 1/2 of the records that they should.

    So I believe at this point I will be going back to the above suggestion of a counter variable, and incrementing my report based on that counter reaching 26. Not sure how to do that, but more research will surely tell me.

    Thanks again for the help/suggestions.

  12. #12
    Join Date
    Feb 2004
    Posts
    126
    Here's another possible way of going about this.

    For database design purposes, we have normalization beaten into our heads.

    But for reporting purposes, it can be simpler to just created a "denormalized" view as the recordsource for the report that includes the information for both the BOL header and the detail line items. Then you can just group by BOL (and in your case Suffix) and you're good to go and you don't even need a subreport!

    In your case, you would be adding the header information to the query for the subreport, formatting everything and grouping it as we discussed earlier, and you could be good to go depending on how the data is structured.

    For may situations, this is easier than creating subreports, and at the very least it usually performs much better. For example, I create customer statements this way, no subreports, just a join between the customer table and the order headers and line items table. The details are the order line items, and I just add groups for each customer. I actually have a lot more groups than that for grouping payments and shipment activity, but the point is...no subreports, and it performs much better than the supplied Crystal Reports statements that use subreports.

    Does that make sense? Is it applicable in your situation?

  13. #13
    Join Date
    Mar 2004
    Posts
    31
    It sounds like it would be, of course there is the issue of the multicolumn layout of the subreport to deal with.

    I will have a crack at this using your alternative, but doubt that today will be when I do it (a few fires brewing.)

    Thanks again.

Posting Permissions

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