Results 1 to 6 of 6
  1. #1
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188

    Query Break Out Quantities

    Attached are a couple screenshots. What I need to do is take sku numbers and break them out to quantities of one each, even when quantities are multiple. End goal is to set them up for importing and issue tracking numbers per each Qty. Example: ModelNo3 below at Qty=6, needs to break out to 6 rows of Qty=1 each. Where do I start with something like this?

    Table: TempSku
    Sku,Qty


    ModelNo1,1
    ModelNo2,1
    ModelNo3,6
    ModelNo4,4

    Query Results Needed:
    Sku,Qty
    ModelNo1,1
    ModelNo2,1
    ModelNo3,1
    ModelNo3,1
    ModelNo3,1
    ModelNo3,1
    ModelNo3,1
    ModelNo3,1
    ModelNo4,1
    ModelNo4,1
    ModelNo4,1
    ModelNo4,1

    Thanks!
    Attached Thumbnails Attached Thumbnails S1.jpg   S2.jpg  

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    make a a listbox to show the squ & qty
    then this code will cycle thru the list and run an append query N times. (qty)
    there is a text box to hold the value in the Sku value as the list cycles thru.
    Code:
    For i = 0 To lstBox.ListCount - 1
       lstBox = lstBox.ItemData(i)
       txtSQU= lstBox
       iQty = lstBox.Column(1)   'in VB columns start with zero
       for x = 1 to iQty
          docmd.openquery "qaAdd1Squ"
       next
    Next
    the query "qaAdd1Squ", will post the squ in txtSQU into the table:
    insert into table (SKU, Qty) values (forms!myForm!txtSqu, 1)

  3. #3
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    I will try this, but I was hoping for something that would create data on-the-fly for possibly hundreds of records, without manual intervention.

    I'm guessing it would some kind of function.

    Thanks!

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You want to do it with what then, a query? It could be the same sort of function that you call in a query but using a query field to provide the counter rather than a listbox.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Query method without VBA:

    SELECT TempSku.*, 1 AS Unit
    FROM TempSku, (SELECT DISTINCT [Tens]+[Ones] AS Factor, 10*Abs([Deca].[id] Mod 10) AS Tens, Abs([Uno].[id] Mod 10) AS Ones FROM MSysObjects AS Uno, MSysObjects AS Deca) AS F
    WHERE F.Factor<Qty
    ORDER BY Sku;

    This requires a "Counter" dataset. This query generates that dataset with MSysObjects system table because it is certain to have enough records. You can build a Counter table instead, review http://allenbrowne.com/ser-39.html.

    Be aware this type of query can perform slowly with large dataset.

    If you want to save records to table then an INSERT SELECT could do that or use VBA like provided by ranman.

    There must be some human interaction to initiate and provide parameters.

    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    shank is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Sep 2010
    Posts
    188
    June 7 - this is what I was looking for. Thank you to all for your input!

    Greatly appreciated!

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

Similar Threads

  1. Calculating Quantities in a Query?
    By chohan78 in forum Queries
    Replies: 3
    Last Post: 01-11-2019, 08:49 AM
  2. Query with line break divided lines of data?
    By securitywyrm in forum Queries
    Replies: 5
    Last Post: 06-06-2018, 12:48 PM
  3. Replies: 0
    Last Post: 08-29-2017, 02:49 AM
  4. Replies: 7
    Last Post: 01-29-2014, 02:45 PM
  5. How to sum quantities per group
    By crcastilla in forum Access
    Replies: 2
    Last Post: 03-21-2012, 10:05 AM

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