Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297

    Red face Unanswered: Access Report Woes

    Hi peeps,

    Having a spot of bother with a report.

    It's a packing list which is formatted as follows:

    Code:
    Packing List Report
    ^--> Boxes on that packing list, grouped by [Box Code].
    ^----> Products in each Box, grouped by a count of identical [Product Code]s in
           that box.
    That all works fine, and the query is pulling everything as it should.

    The problem is (that on a separate set of tables), we might also put parts in a particular box on the packing list. The amount of which is dynamic, so the report would need to stretch to accommodate these extra parts, or leave no room at all if they're not present.

    The above hierarchy should therefore look like:

    Code:
    Packing List Report
    ^--> Boxes on that packing list, grouped by [Box Code].
    ^----> Products in each Box, grouped by a count of identical [Product Code]s in
           that box.
    ^----> Parts in each Box. Parts table has a [Quantity] column, so no grouping
           is required.

    I have no idea how to implement this though. If I pull all the data into a single query, then I obviously end up with the products and parts in the same row... When what I really need is two queries that work in harmony and consider each other, so:

    Code:
    - For each [Box Code] contained in the [Packing Code] (code to identify packing
      lists).
    --- List all the [Product Code]s that are in the [Box Code] currently being
        evaluated.
    --- Check to see if any [Part Code]s appear in the [Box Code] being evaluated.
    ----- If they do, add the [Part Code](s) to the report after the 
          [Product Code](s).
    ----- Otherwise, do nothing, leaving no gap.
    --- (Total up the [Weight], [Value], etc., for the [Box Code] being evaluated.)
    - (Total up the [Weight], [Value], etc., for the entire [Packing Code].)
    That's the way I'm looking at what I need to do logically, but neither SQL, or Access Reports especially, are something I'm particularly strong at.


    Any assistance/links/whatever would be greatly appreciated.


    Thanking you all in advance!
    Last edited by kez1304; 11-15-12 at 06:31.
    Looking for the perfect beer...

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    consider using a sub report
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by healdem View Post
    consider using a sub report
    I've got a sub report, but I can't get the two reports to link up.

    All it does is just show every [Part Code], regardless of which [Box Code] it goes in, for every [Box Code] on the packing list.

    Do you happen to know of a good guide?

    All the ones I've read leave a lot to be desired, and I'm still unsure how to link the two reports correctly and which grouping section I should be putting the subreport, or whether it should have its own group, or what's going on really.


    Thanks for the prompt reply!
    Looking for the perfect beer...

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    put the aprtno as a sub report, if neccesary within a sub report
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Also, if I use a sub report, won't it ALWAYS leave a gap?

    Very rarely are parts added into a box, maybe about 2% of the time... So to leave a cm gap for every box, on every packing list, isn't really ideal.
    Looking for the perfect beer...

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so if you can't do it the easy way, then do it the hard way
    put a control in the subreports report footer
    as part of the sub reports footer event add the partnumbers to that control(s)

    Very rarely are parts added into a box, maybe about 2% of the time... So to leave a cm gap for every box, on every packing list, isn't really ideal.
    whats a better spend of your time?
    as an alternative use the sub subreports no data event to scancel printing
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Quote Originally Posted by healdem View Post
    as an alternative use the sub subreports no data event to scancel printing
    I've got it linked and showing the correct data now, but the gap is still there on boxes that don't have spare parts in...

    Are you suggesting I use the no data event to adjust the size of the footer on the master report dynamically? Would that work?

    So if there's no data for its linked box, then I can shrink the size of the group that the sub report resides on the master report, and hide the sub report?

    I think that would be ideal, as that way it should close the gap if there's no data to present, or show it and expand it as necessary when there is data.


    Is that what you mean? And is it possible (I assume it is, although I'm unfamiliar with the No Data event, and haven't looked into it yet).


    Cheers for your time by the way bud.
    Looking for the perfect beer...

  8. #8
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    D'oh!

    I solved it... I didn't realise that a subreport can be resized to take up 1px, and still display its contents properly, thus taking up 1px for boxes that don't contain any spare parts.

    I really hate it when things are just that simple and you try everything else, but that.


    Thanks for all your help healdem. You're an absolute legend.
    Looking for the perfect beer...

Posting Permissions

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