Can I duplicate records dependant on a field

[tdktown]

Being relatively new with access databases but pretty good with computers in general i need a little help with a solution.

I have a table that contains fields...
order #
line #
quantity
text field1
text field2
text field3

there is no unique id
order # is repeated as many times as there are line numbers.
quantity can be from 1-20 typically

we do a daily import from a text file to update and add new records.

what I am trying to accomplish and not sure the best route is:
i need to repeat each record the number of times=to the total quantity
ie:
if:
order 123 line 1 quantity 3
line 2 quantity 2
line 3 quantity 4


i need
order 123 line 1 quantity 1-3
order 123 line 1 quantity 2-3
order 123 line 1 quantity 3-3
order 123 line 2 quantity 1-2
order 123 line 2 quantity 2-2
order 123 line 3 quantity 1-4
order 123 line 3 quantity 2-4
order 123 line 3 quantity 3-4
order 123 line 3 quantity 4-4

with the corresponding text fields that follow.

basically it is copying a record the number of time equal to the quantity column but substituting the number in the quantity column for 1-3, 2-3, 3-3... with the "-3" representing the total quantity.

any help would be greatly appreciated.

also if I could do it on only the new order maybe as part of the import process (.txt) file that would be ok. I do not need it for existing orders although if the solution required would be able to implement that as well.

thanks in advance...

TD

 

[pavla]

Hello TD
I would import your order lines into the Access database and then run a public procedure through the new orders (sitting in the OrderLines table), creating your 'duplicate' records in another table, the OrderQuantityLines table:

Public Sub CreateQtyLines()
'Open a dynamic recordset which is the table with the quantity lines,
'which you will be creating during this procedure:

Dim rstQtyLines As ADODB.Recordset
Set rstQtyLines = New ADODB.Recordset
rstQtyLines.ActiveConnection = CurrentProject.Connection
rstQtyLines.CursorType = adOpenDynamic
rstQtyLines.LockType = adLockOptimistic
rstQtyLines.Open "tblOrderQtyLine"

'Open a forward-only recordset (faster than a static recordset) to read
'the imported table with the order lines
'(the WHERE clause will restrict it only to the orders you need to process):

Dim rstOrderLines As ADODB.Recordset
Set rstOrderLines = New ADODB.Recordset
rstOrderLines.ActiveConnection = CurrentProject.Connection
rstOrderLines.CursorType = adOpenForwardOnly
rstOrderLines.LockType = adLockOptimistic
rstOrderLines.Open "SELECT OrderID, OrderLine, Qty FROM tblOrderLine " _
& " WHERE OrderID >= 123;"

'If the OrderLines recordset is not empty, step and loop through its records
'to create your 'duplicates':

Dim bytN As Byte
If Not rstOrderLines.BOF And Not rstOrderLines.EOF Then
rstOrderLines.MoveFirst
Do Until rstOrderLines.EOF
For bytN = 1 To rstOrderLines!Qty
rstQtyLines.AddNew
rstQtyLines!OrderID = rstOrderLines!OrderID
rstQtyLines!OrderLine = rstOrderLines!OrderLine
rstQtyLines!QtyLine = bytN & "-" & rstOrderLines!Qty
rstQtyLines.Update
Next bytN
rstOrderLines.MoveNext
Loop
End If
End Sub

Regards, Pavla.

 

[pavla]

Regards, Pavla.
Hallo again,
I should have said, the procedure which creates the duplicate records uses Access 2000's ActiveX Data Objects (ADO). If you are using Access 97 you would need to use Data Access Objects (DAO) and start your procedure by retrieving a reference to your current database:
Dim db as DAO.Database
Set db = CurrentDb().

Similarly, the recordsets you open will be DAO recordsets:
Dim rst as DAO.Recordset
Set rst = db.OpenRecordset (Source, [Type, Options, LockEdits]).

Regards, Pavla.