Expand rows for carton numbers

[shank]

What I'm trying to do is expand quantity ordered, per each sku, into a carton number row so I can then issue a serial number and SSCC number to each carton. I attached a zipped database. I don't know where to start with programming. It's going to take a function and I know little about them. If I knew what this process would be called I'd Google for it. Any suggestions?



I want to turn this data:

InvNo	Sku	Qty
700000	Item1	2
700000	Item2	3
700000	Item3	5
700001	Item1	4
700002	Item2	3
700003	Item1	2
700003	Item2	3
700003	Item3	5

Into this data:

InvNo	Sku	CartonNo
700000	Item1	1
700000	Item1	2
700000	Item2	1
700000	Item2	2
700000	Item2	3
700000	Item3	1
700000	Item3	2
700000	Item3	3
700000	Item3	4
700000	Item3	5
700001	Item1	1
700001	Item1	2
700001	Item1	3
700001	Item1	4
700002	Item2	1
700002	Item2	2
700002	Item2	3
700003	Item1	1
700003	Item1	2
700003	Item2	1
700003	Item2	2
700003	Item2	3
700003	Item3	1
700003	Item3	2
700003	Item3	3
700003	Item3	4
700003	Item3	5

Thanks!

[ranman256]

Paste this code into a module, change the 2 tables name constants: kTBL1 & 2 to YOUR TABLE NAMES
then run PARSEOUT

Private Const kTBL1 = "TABLE1"
Private Const kTBL2 = "TABLE2"


Public Sub ParseOut()
Dim rst
Dim sSql, vInv, vSku, vQty
Dim i As Integer


DoCmd.SetWarnings False


Set rst = CurrentDb.OpenRecordset("select * from [" & kTBL1 & "]")
With rst
  While Not .EOF
     vInv = .Fields("InvNo").Value & ""
     vSku = .Fields("Sku").Value & ""
     vQty = .Fields("Qty").Value & ""
     
     PostRecs vInv, vSku, vQty
     
     .MoveNext
  Wend
Wend
MsgBox "done"
DoCmd.SetWarnings True
End Sub




Public Sub PostRecs(ByVal pvInv, ByVal pvSku, ByVal pvQty)
Dim i As Integer
Dim sSql


For i = 1 To pvQty
    sSql = "insert into [" & kTBL2 & "] (INVNO ,SKU,QTY) VALUES ('" & pvInv & "','" & pvSku & "'," & i & ")"
    DoCmd.RunSQL sSql
Next
End Sub

[Micron]

@ranman256 - nice! The only thing is I see an extra Wend, which might be where the missing End With should go. In the sample output posted, the 3rd field is CartonNo, not Qty so maybe that will be an issue?

[shank]

Paste this code into a module, change the 2 tables name constants: kTBL1 & 2 to YOUR TABLE NAMES
then run PARSEOUT

I made a few edits to the code and it worked great in the VB window. Thank you! Showing my ignorance working with VB, how do I get it to work in the APP query? I tried using ParseOut(). as you might guess, it failed. Using the "build" option in right-click menu, teh parseout() function does not exist.

rivate Const kTBL1 = "Orders"
Private Const kTBL2 = "Carton Numbers"




Public Sub ParseOut()
Dim rst
Dim sSql, vInv, vSku, vQty
Dim i As Integer


DoCmd.SetWarnings False


Set rst = CurrentDb.OpenRecordset("select * from [" & kTBL1 & "]")
With rst
  While Not .EOF
     vInv = .Fields("InvNo").Value & ""
     vSku = .Fields("Sku").Value & ""
     vQty = .Fields("Qty").Value & ""
     
     PostRecs vInv, vSku, vQty
     
     .MoveNext
  Wend
End With
MsgBox "done"
DoCmd.SetWarnings True
End Sub


Public Sub PostRecs(ByVal pvInv, ByVal pvSku, ByVal pvQty)
Dim i As Integer
Dim sSql


For i = 1 To pvQty
    sSql = "insert into [" & kTBL2 & "] (InvNo,Sku,CartonNo) VALUES ('" & pvInv & "','" & pvSku & "'," & i & ")"
    DoCmd.RunSQL sSql
Next
End Sub

Thank you!

[Micron]

That's not a function, it's a sub. You can only call functions from queries, macros, controls, etc.
If you rename it, it might work. However, don't use ParseOut in a query to create a recordset that's basically the same as that query. Use PostRecs and pass the 3 fields from that query as the function parameters. The query field might be
Expr1: PostRecs([InvoNo],[SKU],[QTY]) and the values in those fields will be passed to the function for every record.

However, I don't think I've ever tried to use a SELECT query to execute a function that appends or updates to a table. Seems to me that you'd have the query 'engine' busy with the 1st so the execution of the update might not happen, because we'd be expecting the 2nd to execute upon each record being returned from the 1st. I suspect you will have to stick with the recordset approach. It has been a while since I created a query that calls any function so I might be off base here. I'd have to find one and refresh my tired old memory.

NOTE I am not referring to returning values to SELECT queries from functions. My comments are based on trying to perform an append or edit from that function from within a SELECT query.

[CJ_London]

you can do this just with a query

it requires a 'counter' table or query (called utilCount above) which is simply a single field table populated with numbers from 0 to whatever you feel is a maximum required. There are ways to generate this as a simple query so can be reused.

I use a query based on a table called utilcounter
num
0
1
2
3
4
5
6
7
8
9



and the utilcount query

SELECT CLng([singles].[num]+([tens].[num]*10)+([hundreds].[num]*100)+([thousands].[num]*1000)) AS [Counter]
FROM utilCounter AS singles, utilCounter AS tens, utilCounter AS hundreds, utilCounter AS thousands;

which counts from 0 to 9999 which I've found is generally sufficient but can be modified to show greater (or fewer) numbers if required. I use this functionality for creating records between two values such as dates

If quantities are less that 10 you just use the utilcount table

your sql would then be

INSERT INTO [Carton Numbers] ( InvNo, Sku, CartonNo )
SELECT Orders.InvNo, Orders.Sku, [Counter]+1 AS Expr1
FROM Orders, utilCount
WHERE (((utilCount.Counter)<[Qty]))

you don't need to orderby but you can if required

[shank]

I modified table name and columns, but this works great with a counter table.

INSERT INTO [Carton Numbers] ( InvNo, Sku, CartonNo )
SELECT Orders.InvNo, Orders.Sku, [CtNo]+1 AS CartonNo
FROM Orders, tblCountIndex
WHERE (((tblCountIndex.CtNo)<[Qty]))
ORDER BY Orders.InvNo, Orders.Sku, [CtNo]+1;

However, this code, I'm at a loss what to do with it.

SELECT CLng([singles].[num]+([tens].[num]*10)+([hundreds].[num]*100)+([thousands].[num]*1000)) AS [Counter]
FROM utilCounter AS singles, utilCounter AS tens, utilCounter AS hundreds, utilCounter AS thousands;

I changed it as well for table and column names, but it just runs forever and not into fruition. What am I missing?

SELECT CLng([singles].[CtNo]+([tens].[CtNo]*10)+([hundreds].[CtNo]*100)+([thousands].[CtNo]*1000)) AS [Counter]
FROM tblCountIndex AS singles, tblCountIndex AS tens, tblCountIndex AS hundreds, tblCountIndex AS thousands
ORDER BY CLng([singles].[CtNo]+([tens].[CtNo]*10)+([hundreds].[CtNo]*100)+([thousands].[CtNo]*1000));

Thank you!

[CJ_London]

What am I missing?

take out the order by, it's not required, you are trying to sort 10000 unindexed calculated fields

There is absolutely no need to sort the whole query - just sort in a query that calls this one - so only a few records at a time

[shank]

take out the order by, it's not required, you are trying to sort 10000 unindexed calculated fields

There is absolutely no need to sort the whole query - just sort in a query that calls this one - so only a few records at a time

Well that made a huge difference.

Thanks!