Generate multiple records in one table from single record in another table

[JessPedersenSCM]

Hi all,

First of all - great site. I have been reading through the forum for quite some time and have found many threads quite valuable. But it is time to create a thread for my own since I have not been able to find a discussion for my question.

I am building a simplified re-order point system - if inventory position drops below a certain level (the yellow level is this case) one or more purchase order lines has to be created in another table.

I have one table with the following field and data:

ItemId Red Yellow Green Multiple Inventory position
0001 10 30 50 5 45
0002 5 40 47 5 23
0003 11 20 30 10 5

I would like to generate new records (in another table) based on the above fields and three records.

Basically the end result should look as the following:

ItemId Qty Start inv Aggregated inventory Prioritization
0002 5 23 28 Yellow
0002 5 28 33 Yellow
0002 5 33 38 Yellow
0002 5 38 43 Green
0002 5 43 48 Green
0003 10 5 15 Red
0003 10 15 25 Yellow
0003 10 25 35 Green

The logic is quite simple - if inventory position is less than the yellow value new order lines should be created in multiple qty (based on the multiple field) until the aggregated value (in table 2) is above the green value.

The priotization value should be based on the start inv (in tbl 2) compared to the values in red, yellow and green in tbl 1.

It would be much valuable if someone could guide me in the right direction towards an appropriate VBA design. Alternatively some kind of loop query design (but I believe that VBA is required to make the above - particularly on the loops and running sum).

I am looking forward some great answers.

Much appreciated - thank you in advance.

//Jess

 

[DavidAtWork]

The first table that shows inventory position, how is this generated or is it simply a static lookup table where the values can be updated.
From the values you have in your first table, I don't see how the second table corresponds to the first or am I misunderstanding it.
Without seeing the way your data is structured, I can't help thinking there must be an easier way to do this but for that you'll have to do a lot more explaining.

David

 

[Uncle Gizmo]

I read this post shortly after you posted it and shied away because it was so long and complicated, I thought I would let someone else have a go. It appears others have thought the same as me and you have not yet received a response.

I suggest you rethink your problem and posted a much simpler step which may well receive an answer.

Your question alludes to managing inventory, so I think you should also read the following information:-

http://allenbrowne.com/appinventory.html

 

[spikepl]

I read it too:

1. Normally one stores transactions (in/out-operations) and then calculates the inventory - see Allen browne referred to earlier. A transaction would naturally check on the remaing inventory and trigger your reordering scheme.

2. The role of prioritization is not described

3. Why do you need to create many order lines each with the one specified ordering quantity instead of one order line with integer multiples of the ordering quantity?

 

[JessPedersenSCM]

Lets simplify the task

I am not building a transactional processing system I purely would like a table that contains a list of lines (ready to be exported to excel) that is generated based on another table.

To make it really simple - a table containing the following:

ItemId....Value
0001.....10
0002.....5

The new table should have 10 records with itemid 0001 and 5 records with 0002.

It could be really great if someone could guide me to some VBA code that could duplicate records like that

The remaining part I will try to handle myself.

 

[Uncle Gizmo]

You don't appear to show the record number of the invoice or whatever you call it that contains the line items so please account for that.

 

[Libre]

Here is my code. I tested it and it works!
First, here is the source (input) and destination (output)tables:

Input:

ItemID Value
0001....10
0002..... 5


Output:

ItemID Value
0001....10
0001....10
0001....10
0001....10
0001....10
0001....10
0001....10
0001....10
0001....10
0001....10
0002..... 5
0002..... 5
0002..... 5
0002..... 5
0002..... 5

Now here is the VBA code:

    Dim mySQL As String
    Dim cnnl As ADODB.Connection
    Dim myRecordset As New ADODB.Recordset
    Set cnnl = CurrentProject.Connection
    myRecordset.ActiveConnection = cnnl
    myRecordset.CursorType = adOpenDynamic
    myRecordset.LockType = adLockOptimistic
 
    Dim sItemId As String
    Dim iValue As Integer
    Dim iCtr As Integer
 
    DoCmd.SetWarnings False
 
    mySQL = "SELECT tblAaTestAWFSource.*"
    mySQL = mySQL & " FROM  tblAaTestAWFSource"
    myRecordset.Open mySQL
    If myRecordset.EOF Then
        Exit Sub
    Else
        myRecordset.MoveFirst
        Do Until myRecordset.EOF
            sItemId = Nz(myRecordset.Fields("ItemID").Value, "")
            iValue = Nz(myRecordset.Fields("Value").Value, 0)
            iCtr=0
            If iValue > 0 Then
                Set rst = CurrentDb.OpenRecordset("tblAaTestAWFDestination")
                Do Until iCtr = iValue
                    rst.AddNew
                    rst![ItemID] = sItemId
                    rst![Value] = iValue
                    iCtr = iCtr + 1
                    rst.Update
                Loop
            End If
            myRecordset.MoveNext
        Loop
    End If
 
    myRecordset.Close
    Set myRecordset = Nothing
    Set cnnl = Nothing
    DoCmd.SetWarnings True

You could process or change the values between the reading from the input table and writing to the output table, if necessary - or you could even gather other values from other tables and write to other destination tables as well. But that could get more complicated depending on the output you're looking for.

 

[JessPedersenSCM]

Thank you for the code!

It is much appreciated that you have taken the time to provide a solid answer - cannot stress how grateful I am.

Again - thank you!

 

[Libre]

Happy to help, Jess.