peskywinnets
Registered User.
- Local time
- Today, 19:23
- Joined
- Feb 4, 2014
- Messages
- 578
So I have some incoming data ..basically sales orders
One item that I sell is sold as a '5pc set', but it's actually comprised of 5 individual single items. To ensure the correct stock is deducted further along the line, I need to break out the 'set' into 5 individual items & calculate the price per item (basically the cost of the set divided by 5 - there's some rounding involved, because 5 quite often doesn't divide cleanly into the selling price)
I can do the above fine. Where I'm struggling is how to then insert back to the table the five new duplicated rows (from the original) & then delete the original row.
here's how I'm working out the price per item (apologies if it's rubbish from a programming perspective...I'm still finding my feet)...
What would be the high level approach here?
I'm clearly going to need somewhere to 'store/hold' the contents of the original table row, then copy 5 times ...but with the price & SKU name changed accordingly for each row & then inserted back to the original table.
My first thought is to have a replica of the table I'm working on...just as a 'temporary storage' for the original single row data therefore
1. Hunt through the rows looking for a SKU match
2. Copy the whole row to the 'temp table'
3. Do the calculations (as per my code above)
4. Read in the single row from the temp table.
5. Loop 5 times with an inserting with each loop back into the original table, but changing the SKU name & item price.
6. Delete the original table row
7. delete the row in the temp table
- sound plausible/workable?
One item that I sell is sold as a '5pc set', but it's actually comprised of 5 individual single items. To ensure the correct stock is deducted further along the line, I need to break out the 'set' into 5 individual items & calculate the price per item (basically the cost of the set divided by 5 - there's some rounding involved, because 5 quite often doesn't divide cleanly into the selling price)
I can do the above fine. Where I'm struggling is how to then insert back to the table the five new duplicated rows (from the original) & then delete the original row.
here's how I'm working out the price per item (apologies if it's rubbish from a programming perspective...I'm still finding my feet)...
Code:
Public Sub SplitItems()
Dim SingleItemPrice As Double
'Set rstDestination = CurrentDb.OpenRecordset("SELECT * FROM [Test] ")
Set rst = CurrentDb.OpenRecordset("SELECT * FROM [Test]")
Do While Not rst.EOF
SKU = rst!SKU
If SKU = "5pcSet" Then
SingleItemPrice = rst!ItemPrice / 5 'divide the 'set' price by 5 items
RoundedUpSingleItemPrice = Round(SingleItemPrice)
ItemPiece1 = Format(RoundedUpSingleItemPrice, "#,##0.00")
ItemPiece2 = Format(RoundedUpSingleItemPrice, "#,##0.00")
ItemPiece3 = Format(RoundedUpSingleItemPrice, "#,##0.00")
ItemPiece4 = Format(RoundedUpSingleItemPrice, "#,##0.00")
ItemPiece5 = Format((rst!ItemPrice - (4 * RoundedUpSingleItemPrice)), "#,##0.00")
End If
If Not rst.EOF Then rst.MoveNext
Loop
End SubWhat would be the high level approach here?
I'm clearly going to need somewhere to 'store/hold' the contents of the original table row, then copy 5 times ...but with the price & SKU name changed accordingly for each row & then inserted back to the original table.
My first thought is to have a replica of the table I'm working on...just as a 'temporary storage' for the original single row data therefore
1. Hunt through the rows looking for a SKU match
2. Copy the whole row to the 'temp table'
3. Do the calculations (as per my code above)
4. Read in the single row from the temp table.
5. Loop 5 times with an inserting with each loop back into the original table, but changing the SKU name & item price.
6. Delete the original table row
7. delete the row in the temp table
- sound plausible/workable?
Last edited:
