Results 1 to 15 of 15
  1. #1
    zeeshanahmed456 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Posts
    5

    distributing results and append data in table

    hi guyz.

    I need your help in making a query or code for following problem;





    I am making a database of a warehouse where product is placed on specific locations. I receive the stock as dead pile (in bulk) then my team palletize the whole stock and put away the stock to specific locations. when the stock is received, a document is generated where the detail of the stock is mentioned.For example i have received 520 cartons of ice cream. On pallets every product type has different stacking pattern, suppose 50 cartons of ice cream are placed on 1 pallet, then there will be 10 pallets of 50 cartons each and one pallet of 20 cartons.




    now what i am trying to do is, i have a main form and a subform, on main form i input information in 3 fields


    1. Product (text) : Ice Cream
    2: Qty rcvd (number): 220 (cartons)
    3: Pattern (number): 50


    i need an append query that fill down the subform as below;


    pallet # product quantity
    1 ice cream 50
    2 ice cream 50
    3 ice cream 50
    4 ice cream 50
    5 ice cream 20


    Simply i just want to distribute bulk quantity into pattern pieces and the last position will have remaining. I guess some coding will be required,
    where i am failing.



    appreciate ur help.


    regards

  2. #2
    Pommetje77 is offline Advanced Beginner
    Windows 7 64bit Access 2010 64bit
    Join Date
    Feb 2015
    Location
    the Netherlands
    Posts
    37
    Hi
    Will the subform be bound to a table "Pallet#" ?
    and will this Pallet# Table be relational table with the product table?
    Ruud

  3. #3
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    No coding required, and I don't see the need for complicating things with a subform for showing the number of pallets with quantities.
    If your main form had 4 unbound textboxes:
    qty: integer value input by user
    pattern: integer value input by user
    number of pallets containing pattern: = qty divided by pattern (220/50 = 4)
    number of pallets (always will be 1) containing the remainder: = qty Mod pattern (520 Mod 50 = 20)
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Please describe stacking pattern. A sample or two would be helpful to readers.

  5. #5
    zeeshanahmed456 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Posts
    5
    you can say, pallet# an auto number, and the pallet# is not relational with product table.

  6. #6
    zeeshanahmed456 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Posts
    5
    Dear micron,

    in fact i am unable to figure out how the remainder could automatically come in the last.

    rgds

  7. #7
    zeeshanahmed456 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Posts
    5
    stacking pattern you can say a predefined fixed value. let say, i can only put max 30 eggs in a basket, so 30 is stacking pattern.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    By using a calculated control containing the expression I gave you. Something like this
    Click image for larger version. 

Name:	PalletForm.jpg 
Views:	13 
Size:	21.6 KB 
ID:	26215
    I'd post a little db if you wanted it, but my play version of this form has a validation bug in it.
    You'd have to be prepared to give me some time to fix it.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    FullPallets = Quantity/StackingPattern

    If there is a remainder, then the remainder goes into/on Last Pallet.
    I answered a somewhat similar post --putting things into boxes and determining how many boxes were needed.
    You may see something in that thread that would be helpful.
    Good luck.

  10. #10
    zeeshanahmed456 is offline Novice
    Windows 7 32bit Access 2016
    Join Date
    Oct 2016
    Posts
    5
    Quote Originally Posted by orange View Post
    FullPallets = Quantity/StackingPattern

    If there is a remainder, then the remainder goes into/on Last Pallet.
    I answered a somewhat similar post --putting things into boxes and determining how many boxes were needed.
    You may see something in that thread that would be helpful.
    Good luck.
    Dear Orange,

    I checked the link and downloaded database you made, it's useful, but only difference is i don't have fixed number of boxes.
    let say, if i may receive the quantity of 2500 boxes which are to be placed 50 boxes on each pallet, but number of pallets are not
    confined. however, you work is too good in database.

    rgds

  11. #11
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by zeeshanahmed456 View Post
    Dear Orange,
    ...only difference is i don't have fixed number of boxes.
    let say, if i may receive the quantity of 2500 boxes which are to be placed 50 boxes on each pallet, but number of pallets are not
    confined.
    Pray tell, what am I missing? I thought I posted a solution (albeit not perfect yet) which has gone ignored. I guess I won't bother fixing it.

  12. #12
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with Micron. He gave a solution, but said his implementation was quite ready for posting.
    My comment similar to Micron's mod function was that
    FullPallets = Quantity/StackingPattern
    and If there is a remainder, then the remainder goes into/on Last Pallet.
    I only referred to the older post to show a solution to a problem where there were different boxes, different products and OP was having an issue with logic.

    zeeshanahmed456 - I'm not following your latest post???? You told us that you have a quantity and a stacking pattern. Now you say if you have 2500 boxes and the stacking pattern is 50 box per pallet something doesn't work. Please give us the details of what you mean using the 2500 boxes and the stacking pattern of 50.

  13. #13
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Works now. I couldn't resist fixing it because there was a very bizarre issue that I just had to conquer.
    Maybe I should start a separate thread to see if anyone can guess the cause, because I spent hours trying to figure it out.
    Click image for larger version. 

Name:	PalletForm2.jpg 
Views:	9 
Size:	19.7 KB 
ID:	26239

  14. #14
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I know what you mean--some things are just an interesting challenge.
    Does it involve 45.45454545? and .45 * 55

  15. #15
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737

Please reply to this thread with any new information or opinions.

Similar Threads

  1. How do I Append a table with non-delimited txt file data
    By Gotboost858 in forum Import/Export Data
    Replies: 7
    Last Post: 05-14-2015, 01:37 PM
  2. Replies: 2
    Last Post: 12-17-2012, 03:46 PM
  3. Replies: 5
    Last Post: 12-03-2012, 05:03 PM
  4. Data append to table
    By ali zaib in forum Access
    Replies: 4
    Last Post: 01-13-2012, 11:22 AM
  5. Append to SQL table failing on data type
    By tpcervelo in forum Queries
    Replies: 2
    Last Post: 10-13-2011, 12:07 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums