Turning one row into 5 copied (but slightly modified) rows (1 Viewer)

[peskywinnets]

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)...

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 Sub

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?

 

[sneuberg]

I don't see the need for the Temp table. Aren't all five new records going to have going to have different keys than the existing one.

In your code you are opening two recordsets on the same table. I suggest having only one recordset open. You can use bookmarks to move around within the recordset.

 

[jdraw]

The 5 pc set does not have a SKU of its own? Sometimes people sell items and "assemblies" where assemblies are composed of more than 1 item and have separate SKU. Just asking to be sure of requirement.

Here is a link to a sample hierarchical data structure. You might get some ideas from it. It's good to be aware of such structures even if it may not apply to your current situation.

I suggest working through some structures on paper to get a design that you are comfortable with --then do the appropriate coding base don a working design.

Good luck.

 

[peskywinnets]

I don't see the need for the Temp table. Aren't all five new records going to have going to have different keys than the existing one.

In your code you are opening two recordsets on the same table. I suggest having only one recordset open. You can use bookmarks to move around within the recordset.

Thanks....as I say, I'm often out my depth! Before seeing your reply, I pushed on & spent some time with a temp table...but problems to do with the Primary keys (access wouldn't let me modify the Primary ID once the data was copied over to the temp table) ...I'd rather keep it in the same table (as per your suggestion)but frankly don't know how!! Incidentally, the reason for wishing to do a complete replica of the table, was to be sure that any changes I make to the table over time are always embraced. There...

1. delete any existing temp table
2. Duplicate the main table (structure only) to a new temp table
3. Copy the whole row from main table to temp table
4. modify the row in the temp table + copy row back to the original table (five times)

Whilst a little cumbersome, it ought to work (& will ensure any changes made over time to the main table will not cause issues)...but like I say Primary Key woes were hit.

The 5 pc set does not have a SKU of its own? Sometimes people sell items and "assemblies" where assemblies are composed of more than 1 item and have separate SKU. Just asking to be sure of requirement.

Here is a link to a sample hierarchical data structure. You might get some ideas from it. It's good to be aware of such structures even if it may not apply to your current situation.

I suggest working through some structures on paper to get a design that you are comfortable with --then do the appropriate coding base don a working design.

Good luck.

Thanks for the links (haven't had time to read them, but I will)....yes the individual items will all have a SKU of their own. For example, lets say the full 5pc set 'SKU' is called AA-SET, the five individual pieces will be called AA-01 AA-02 AA-03 AA-04 and AA-05

I can see a lot of hours spent on this one today!

 

[peskywinnets]

Re my earlier comment...

I spent some time with a temp table...but problems to do with the Primary keys (access wouldn't let me modify the Primary ID once the data was copied over to the temp table)

Ok, I've managed to achieve what I seek using the temporary 'holding' table to massage the data as required (whilst ensuring I keeping the contents of the whole original table row), the primary key issue I had was circumvented by modifying the associated field in the temp table like this ...

(fyi EbayOrders_Scratch is my temp table)

    DoCmd.RunSQL ("ALTER TABLE EbayOrders_Scratch DROP CONSTRAINT PrimaryKey") 'get rid of primary key as it won't be used in the temporary table
    DoCmd.RunSQL ("ALTER TABLE EbayOrders_Scratch Alter Column ID INTEGER") ' change ID field from 'Autonumber' to 'Integer' to allow me to change this field in my code as required

...once the constraint was removed & the ID field changed from Autonumber to Integer, I was able to make the temp table ID column a correctly (incrementing) ID number for copying back to the original table

here's the sequence of events...

1. Hunt through the newly added table rows looking for a SKU match for the 5pc set
2. Once a SKU match if found , copy the whole table (single) row to a 'temp table'
3. Do the calculations needed to split the 5pc set into individual items (price, SKU name) ...update the 'scratch' row in the temp table
4. Copy back the single row from the temp table to the original table (making sure the primary key ID field is incremented to suit the original table's needs)
5 Loop 4 more times (therefore a total 5 individual items)
6. Delete the original table row entry (i.e. the original 5pc set)
7. delete the temp table


At least going the above somewhat longwinded way, ensures that no matter what changes I make to the original table (i.e. over the passage of time), the procedure will still work.

Definitely a case of ugly but functional!

 

[peskywinnets]

One last thing & I'm sorted!


This command...

    DoCmd.TransferDatabase acImport, "Microsoft Access", "[B]Z:\Sales.accdb[/B]", acTable, "EbayOrders", "EbayOrders_Scratch", True 'copy main table (structure only)

For portability reasons, I don't really want to have a hard coded path to the database name (the bolded bit)...can anyone suggest the correct syntax to essentially say 'this database that's open' instead?

 

[sneuberg]

Application.CurrentProject.Path

 

[peskywinnets]

Thanks, but I'm not understanding.

My Access database is Z:\Sales.accdb ....but I want to change this command (which works)....

DoCmd.TransferDatabase acImport, "Microsoft Access", "Z:\Sales.accdb", acTable, "EbayOrders", "EbayOrders_Scratch", True

to something along the lines of....

DoCmd.TransferDatabase acImport, "Microsoft Access", "CurrentDatabase", acTable, "EbayOrders", "EbayOrders_Scratch", True

(i.e. to avoid using a path, which will allow me to move the database onto my laptop should the need arise ...as the present code will barf as on my laptop, there's no Z drive) - is this possible?

 

[sneuberg]

It's good that you didn't understand as I didn't have that right anyway. The full path is given by:

Application.CurrentProject.Path & "\" & Application.CurrentProject.Name

You can try this in the Immediate Window. If you type

?Application.CurrentProject.Path & "\" & Application.CurrentProject.Name

you should get

Z:\Sales.accdb

so after you are convinced it works just put it in your expression like:

DoCmd.TransferDatabase acImport, "Microsoft Access",Application.CurrentProject.Path & "\" & Application.CurrentProject.Name , acTable, "EbayOrders", "EbayOrders_Scratch", True

 

[sneuberg]

On the other hand

Application.CurrentProject.Path & "\" & Application.CurrentProject.Name

can be shorten to:

Application.CurrentProject.FullName

or even

CurrentProject.FullName

or in you expression:

DoCmd.TransferDatabase acImport, "Microsoft Access", CurrentProject.FullName, acTable, "EbayOrders", "EbayOrders_Scratch", True

 

[peskywinnets]

Hi Steve,

Your original suggestion worked a treat

DoCmd.TransferDatabase acImport, "Microsoft Access",Application.CurrentProject.Path & "\" & Application.CurrentProject.Name , acTable, "EbayOrders", "EbayOrders_Scratch", True

(I didn't try the others...I normally apply the principle - "if it ain't broke don't fix it")

Thanks ever so much

(I hadn't realised I could copy/paste/type in the 'immediate window' - doh!)

EDIT: I'm happy to report that your shorter command worked too ...

DoCmd.TransferDatabase acImport, "Microsoft Access", CurrentProject.FullName, acTable, "EbayOrders", "EbayOrders_Scratch", True