Page 1 of 2 12 LastLast
Results 1 to 15 of 20

Thread: Report Grouping

  1. #1
    Join Date
    Sep 2003
    Posts
    16

    Unanswered: Report Grouping

    I am trying to make a report for a baking list, that needs to group the bakeing items into groups of 20, cause thats all that can be baked at one time.

    So I have a table of items and their quantity for the day
    Brownies - 5
    Cakes - 8
    Cupcakes- 28
    Cookies - 19

    I want the report to show
    START REPORT
    ----1st BAKE LIST
    5 - Brownies
    8 - Cakes
    7 - Cupcake
    ----2ND BAKE LIST
    20 -Cupcakes
    ----3rd BAKE LIST
    1 - Cupcakes
    19 - Cookies
    END REPORT

    Not sure if I can even do this or not. I would prefer the groups of 20 be just report breaks, NOT break and start a whole new page for the next batch of 20, cause can waste a lot of space if I have 20 of the same kind on a page, there would be 1 line on the page and thats all, Do not want to do that. Please let me know if this is can be done, and any hints on how I can do this??

    Thanks,
    Kevin

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Kevin,

    I thought this one was already hashed out ...

  3. #3
    Join Date
    Sep 2003
    Posts
    16
    No not hashed out, still have no solution for what I am trying to do here.

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile Re: Report Grouping

    Originally posted by pilotkk22
    I am trying to make a report for a baking list, that needs to group the bakeing items into groups of 20, cause thats all that can be baked at one time.

    So I have a table of items and their quantity for the day
    Brownies - 5
    Cakes - 8
    Cupcakes- 28
    Cookies - 19

    I want the report to show
    START REPORT
    ----1st BAKE LIST
    5 - Brownies
    8 - Cakes
    7 - Cupcake
    ----2ND BAKE LIST
    20 -Cupcakes
    ----3rd BAKE LIST
    1 - Cupcakes
    19 - Cookies
    END REPORT

    Not sure if I can even do this or not. I would prefer the groups of 20 be just report breaks, NOT break and start a whole new page for the next batch of 20, cause can waste a lot of space if I have 20 of the same kind on a page, there would be 1 line on the page and thats all, Do not want to do that. Please let me know if this is can be done, and any hints on how I can do this??

    Thanks,
    Kevin
    You might try setting up another table for the baking runs. Use the Baked good as the foriegn key (Brownie, Cupcake etc.). Include a baking run number and a quantity for each. Ex. Run 1, Brownies 5, Run 1, Cakes 8, Run 1, Cupcakes 7, Run 2 ....

    It would probably be quick to set up and would allow you to use grouping in your report by the baking runs. If the quantities or the items change it would be easy to do change the table values.

    Just trying to keep it simple. Hope not too simple.

    Good Luck,

    Gregg

  5. #5
    Join Date
    Sep 2003
    Posts
    16
    Okay Greg

    I think I am following you a little bit. But what would I use to create the RUN table? Update query I would guess? But not sure how I would go about creating the RUN table. If I can get them divided into RUN 1 as 20 items, and RUN 2 as 20 items, then yes I think that would work, and make it easy to create the report.

    Its just the item type, and quantity are going to change every day, so nothing is every constant.

    So for another example say I have 45 Brownies to make.

    How would I take 45 brownies, and end up creating a table that is:

    Run 1 / Brownies / 20
    Run 2 / Brownies / 20
    Run 3 / Brownies / 5

    If there is a way to do this, thats what I need to know I think, then the rest should be no problem.

    Thanks

  6. #6
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by pilotkk22
    Okay Greg

    I think I am following you a little bit. But what would I use to create the RUN table? Update query I would guess? But not sure how I would go about creating the RUN table. If I can get them divided into RUN 1 as 20 items, and RUN 2 as 20 items, then yes I think that would work, and make it easy to create the report.

    Its just the item type, and quantity are going to change every day, so nothing is every constant.

    So for another example say I have 45 Brownies to make.

    How would I take 45 brownies, and end up creating a table that is:

    Run 1 / Brownies / 20
    Run 2 / Brownies / 20
    Run 3 / Brownies / 5

    If there is a way to do this, thats what I need to know I think, then the rest should be no problem.

    Thanks
    Forgive me if I assume too much about your experience level with Access but here goes...

    OK. If the requirements for this database are as simple as they seem at this point this shouldn't be too difficult.

    If you are simply going to use this database to perform the operation that you have described then you should only need one table to do this.

    If you are using this for more, such as ingredients and recipes etc then I would do more and you should restructure before setting up other objects like reports, forms and queries. For now I'll assume the former.

    If you set up the table with the fields (RunNo) as an integer, the(BakeItem) as text with a field length of say 50 and the (Qty) as an integer that should be enough. You probably have already done this.

    If you want to establish a primary key for the table, use the RunNo and the BakeItem as a composite key. To do this, select both of the fields and right click on them. Select Primary Key from the shortcut menu.

    At this point, if you want to keep it really simple, you could simply open up the table and just edit the information each day as needed. You could then run your report based on the table from the database window.

    If you want to do more we can talk about that also but I'll leave it like that for now. If you are having trouble setting up the report then let me know.

    I'll wait until you respond to this before I mention any other options.

    Let me know how I can help.

    Gregg

  7. #7
    Join Date
    Sep 2003
    Posts
    16
    Gregg,

    I would say my level for access would be intermediate.

    Yes, I see what you are doing, and that I guess that might be the only option to pursue. In the method you have just described, the work that I am trying to remove is the user having to decide which RUN group this item should be in.

    I basically have an entry form, they enter all the order that came in today that need to be baked for tomorrow, this is by persons. At end of day theres is a query that would determine for the day, the item, and total quantity to be baked. Just those 3 items. Then from that I want to divide into groups of 20, or RUN groups as you have mentioned. However I do NOT want the user to have to enter all the orders at one point, then later on take all the orders and enter them again into groups of 20, might as well just take a piece of paper, and write them into groups of 20 from the query that gives them the item, and total quantity. But in todays day and age I figure I am just missing something, and that this could easily be done.

    I think your on the right path, and its something I havnt though of yet. So I think I need to figure out, how to take the query that has today, item, and quantity. Create a table with RUN group, item, quantity. Just not sure how to create that table on the fly, as opposed to having someone enter it manually.

    Thanks Again for any suggestions.
    Kevin

  8. #8
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by pilotkk22
    Gregg,

    I would say my level for access would be intermediate.

    Yes, I see what you are doing, and that I guess that might be the only option to pursue. In the method you have just described, the work that I am trying to remove is the user having to decide which RUN group this item should be in.

    I basically have an entry form, they enter all the order that came in today that need to be baked for tomorrow, this is by persons. At end of day theres is a query that would determine for the day, the item, and total quantity to be baked. Just those 3 items. Then from that I want to divide into groups of 20, or RUN groups as you have mentioned. However I do NOT want the user to have to enter all the orders at one point, then later on take all the orders and enter them again into groups of 20, might as well just take a piece of paper, and write them into groups of 20 from the query that gives them the item, and total quantity. But in todays day and age I figure I am just missing something, and that this could easily be done.

    I think your on the right path, and its something I havnt though of yet. So I think I need to figure out, how to take the query that has today, item, and quantity. Create a table with RUN group, item, quantity. Just not sure how to create that table on the fly, as opposed to having someone enter it manually.

    Thanks Again for any suggestions.
    Kevin
    I guess I'm starting to get a feel for what you are trying to accomplish. Are the items always the same (Brownies, Cakes, etc.)? And how do you determine how the group items are combined into the 20? You know how many of each.

    Keep talking. We'll figure it out.

    Gregg

  9. #9
    Join Date
    Sep 2003
    Posts
    16
    Gregg,

    Yes, the items will be the same, however some days maybe there will be no brownies to bake. Or some days no cakes, etc.

    Basically the group of 20 items, is first determined by the same item, if you have 20 brownies, and 20 cakes. It does not make sense to make 10 brownies, and 10 cakes on a sheet, cause that makes more work for the person doing the baking, as opposed to 20 brownies on one sheet, and 20 cakes on another sheet. If after the same item, there still is not 20 items, then not sure yet, but probbaly just the next thing in alphabetical order say. Where I get into trouble is when the groups cross over. Say theres 25 brownies. How do I know to break this into RUN 1 - with 20 brownies, and start a RUN 2 with 5 brownies.???

    Let me know what else might help.

    Thanks
    Kevin

  10. #10
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by pilotkk22
    Gregg,

    Yes, the items will be the same, however some days maybe there will be no brownies to bake. Or some days no cakes, etc.

    Basically the group of 20 items, is first determined by the same item, if you have 20 brownies, and 20 cakes. It does not make sense to make 10 brownies, and 10 cakes on a sheet, cause that makes more work for the person doing the baking, as opposed to 20 brownies on one sheet, and 20 cakes on another sheet. If after the same item, there still is not 20 items, then not sure yet, but probbaly just the next thing in alphabetical order say. Where I get into trouble is when the groups cross over. Say theres 25 brownies. How do I know to break this into RUN 1 - with 20 brownies, and start a RUN 2 with 5 brownies.???

    Let me know what else might help.

    Thanks
    Kevin


    Kevin:

    I'm starting to get it now. I think I know how to do it but I want to try some experimenting on my own. I think most of the logic to determine what combinations of items in each run, can be determined in the query(s). The data entry should include the current date, the item, the quantity and maybe a sequence number for each time a particular item is added.

    Do you track where or who the orders come from?

    If there is a bigger picture here you might be able to make more use of this setup.

    Gregg

  11. #11
    Join Date
    Sep 2003
    Posts
    16
    Gregg,

    Okay let me know what you come up with. Yes, there is a bigger picture. I do track who ordered the items, in an order table. I have a query to get rid of that information cause its not need for the bakeing list. Just the items, quantity, for the day, and as you mentioned maybe a sequence number, and/or RUN type.

    Kevin

  12. #12
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Question

    Originally posted by pilotkk22
    Gregg,

    Okay let me know what you come up with. Yes, there is a bigger picture. I do track who ordered the items, in an order table. I have a query to get rid of that information cause its not need for the bakeing list. Just the items, quantity, for the day, and as you mentioned maybe a sequence number, and/or RUN type.

    Kevin
    Kevin:

    Is the order table linked to the table that you are using to determine baking requirements? Could you give me more of an idea of what the bigger picture is?

  13. #13
    Join Date
    Sep 2003
    Posts
    16
    I have a table of Customers -
    Name,
    address,
    phone number
    ..
    etc all that good stuff
    key is a Customer_ID.

    I have a table of Items - list all items that can be ordered,
    item Abbreviation( i.e. CK, BR, CC)
    Item Name (i.e Cake, Brownies, Cupcakes)
    ...etc
    key is a unique abbreviation

    I have a table of orders. This has the
    date ordered
    customer_ID(from customer table) of who ordered, Abbreviation of item(from item table) of what was ordered, total quantity.
    The key here is date, customer_id, abbreviation of item

    So I cannot place an order for 1 brownie, and then call the same day and place another order for 1 brownie, it would be 2 brownies for the day as one entry.

    Basically, at end of day I want to create a report, that will run a query on the date entered, group all the items that need to be baked, and get a count of totals for that item for the day. Query I have looks like
    Todays Date -
    Item Abbrevaition
    Sum(Qty)
    Grouped by Item Abbreviation

    But I think I need another query somewhere, or seomthing to create another table that can be used to make the report then way I want it, into groups of 20 like items...

    Let me know if you need anything else?

  14. #14
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Smile

    Originally posted by pilotkk22
    I have a table of Customers -
    Name,
    address,
    phone number
    ..
    etc all that good stuff
    key is a Customer_ID.

    I have a table of Items - list all items that can be ordered,
    item Abbreviation( i.e. CK, BR, CC)
    Item Name (i.e Cake, Brownies, Cupcakes)
    ...etc
    key is a unique abbreviation

    I have a table of orders. This has the
    date ordered
    customer_ID(from customer table) of who ordered, Abbreviation of item(from item table) of what was ordered, total quantity.
    The key here is date, customer_id, abbreviation of item

    So I cannot place an order for 1 brownie, and then call the same day and place another order for 1 brownie, it would be 2 brownies for the day as one entry.

    Basically, at end of day I want to create a report, that will run a query on the date entered, group all the items that need to be baked, and get a count of totals for that item for the day. Query I have looks like
    Todays Date -
    Item Abbrevaition
    Sum(Qty)
    Grouped by Item Abbreviation

    But I think I need another query somewhere, or seomthing to create another table that can be used to make the report then way I want it, into groups of 20 like items...

    Let me know if you need anything else?
    Look at the Northwind Database that ships with Access. I think that an OrderDetails table would help you. This allows you to store multiple line items for each day for a customer. The Orders table will consist of the CustomerID, the OrderID as the Primary Key, and the date at the basic level. The OrderDetails table would consist of the OrderID and a LineNumber as the Primary key, the abbreviation of the item and the qty. This allows you to store multiple ordered items for one order. The totals would come from a query using the amounts in the details table to calculate the individual sums.

    Let me know if this is on track and we can consider more. If not then I will recalibrate.

    Thanks for the additional details.

    Gregg

  15. #15
    Join Date
    Sep 2003
    Posts
    16
    I have all that done already. Order details are all fine. Query that gives me totals is all fine. I just dont know how to write a report that will break on groups of 20s is all. Thats basically what it comes down to. I have a list of items and their quantities, and I need them to be displayed in a report by groups of 20. The groups of 20 are NOT 20 items. Its by groups of 20 for the quantity.

    Easiest examples is 100 Brownies, the report needs to be
    Group 1 - 20 Brownies
    Group 2 - 20 Brownies
    Group 3 - 20 Brownies
    Group 4 - 20 Brownies
    Group 5 - 20 Brownies

    That I think I can do, but the problem comes in when I have left overs and how to carry them over to the next group in a report such as

    49 Brownies
    Group 1 - 20 Brownies
    Group 2 - 20 Brownies
    Group 3 - 9 Brownies

    I need some kind of query I think, that will run through the query of items and quantitys, and adds them up, and when it reaches 20 it writes them to a table with a RUN field. Then keeps going, keeps track of any leftovers, and adds them to the next RUN, and keeps going till its done. Then I can use that table to create the report. But I dont know how to do that with a query, or if its even possible. I am basically trying to get the user to not have to decide which 20 items are going to be baked together. Cause when it gets up to 500 or 1000 items a day, its gonna take them awhile to figure out.

    Kevin

Posting Permissions

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