Update SKU Info

[techwonder]

So I am not sure if my teacher is incompetent or if it is me but almost everything he says doesn't work in Access.

From my research here, it looks this should be an update query. I have several tables with SKU in it and one that has Price too.



Here is the problem.

'Create a Trigger (Data Macro) in Access that can be used to replace any occurence of SKU 101200 with SKU 201200 with a Price of $150....'

So if I run an AFTER INSERT trigger in the Table that has the PRICE in it... I get a message that I cannot change the SKU number because it relates to other Tables.

So I thought that if I made an AFTER INSERT trigger in the SKU_DATA table, I could then trigger updates in all the other tables but I just dont understand it.

Any thoughts.

[June7]

Why are there several tables with SKU?

If the SKU is saved to multiple tables, that would mean an UPDATE on each table to change the SKU that has been saved. Could change the price in the Prices table and that would be reflected in queries when joined to data tables. Could change the SKU in Prices table if Relationship is set to allow Cascade Update.

If you want to provide db for analysis, follow instructions at bottom of my post.

[techwonder]

Thanks...see attached.. ( I hope I attached it right)

[June7]

Okay, the data structure looks normal enough, although the QuantityOnHand and QuantityOnOrder fields might not be needed. Review http://allenbrowne.com/AppInventory.html

I have never seen a CREATE VIEW query. What is it for? It won't open.

My assessment in earlier post still applies.

[techwonder]

The CREATE VIEW Query just makes a shortcut to TableView without creating a new table. Yes... Pointless in most cases.
They are not relevant in this issue though. Just had them there from other exercises.

The example you gave me relates to Quantity changes and not cascading SKU number changes. If I change the SKU number in the SKU_DATA table it obviously changes it in all related tables so not sure what the heck this 'MACRO' is he is referring to.

THat is what I meant about me wondering if I am missing something or he is. I am very skilled at learning software so something is off.

Thanks

[June7]

I know the link I referenced relates to Quantity - that was the point.

I just reread your first post and the instruction you have been given is definitely not clear. If instructor wants user entered SKU of 101200 in Order_Item replaced with 201200 only when entered price is $150, there must first be a record for 201200 in SKU_DATA. The data macro will trigger when attempt to commit record. Code in that macro could maybe test for value of SKU and Price and if conditions met, change the SKU. I don't see need to change SKU for records in Inventory table.

I have never used Data Macros