UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Updating Records Using Referential Integrity?, Access 2010    
 
   
ciapul12
post Jun 13 2018, 10:45 AM
Post#1



Posts: 250
Joined: 7-June 14



Hi All,
I have a couple of very simple tables tblPack_Types and Prodlive.
The first table tblPack_Types contains data about each product including prices, the other table is linked to the printer and that table contains records needed to print labels.
Data is merged into a single table using simple FE app (Info comes from 4 separate tables) but I'm only focusing on those 2 tables.
What I need is somehow be able to update price on all records in Prodlive table for any given product.
Sample database has only few records but the actual database has close to a 3000 records so you can imagine how painful it is to change price on hundreds of records (one by one)

The idea is that I would only need to change price in tblPack_Types table and any record in Prodlive table associated with the product in tblPack_Types table would be automatically updated with new price.
Is this even possible?

I have attached sample of my database which has sample data in it but the structure of databases is identical to live database.

I would kindly appreciate if one one of you could help me out or at least guide me through it.
I have created a relationship between both tables and set Referential integrity on One to Many Joint but nothing happens, I mean price or any other detail changed in tblPack_Types isn't reflected in Prodlive table..

Thank you in advance.

Dan

Attached File(s)
Attached File  Prod_Live.zip ( 400.99K )Number of downloads: 3
 
Go to the top of the page
 
theDBguy
post Jun 13 2018, 10:52 AM
Post#2


Access Wiki and Forums Moderator
Posts: 72,423
Joined: 19-June 07
From: SunnySandyEggo


Hi Dan,

Referential Integrity with cascades only affect the related fields. Since you linked both tables using Pack_ID, only when you update Pack_ID in the parent table will the change auto-populate to the child table. To update the price, if you must store them in two separate tables, you'll need to use an UPDATE query. Best practice (normalization) suggests you only store price in one main table, so when you change it, you only change it in one place. You can then use the price in the live data by using a query.

Just my 2 cents...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
JonSmith
post Jun 13 2018, 11:59 AM
Post#3



Posts: 3,420
Joined: 19-October 10



Agreed, Stop duplicating the data and this stops being a problem. You have a structural issue, not a record updating one.
Go to the top of the page
 
ciapul12
post Jun 14 2018, 03:11 AM
Post#4



Posts: 250
Joined: 7-June 14



Thanks for advice guys.
There's perfectly good reason as to why I have separate tables..
I have already mentioned in my original post that Prodlive table contains the records required for printing, these records are made from the information stored in other tables using front End app.

My live database has 25 products (tblPack_Types), 85 varieties (tblVariety) and roughly 300 suppliers (tblGrowers). A record in Prodlive is made out of the info from those 3 tables, It can be a combination of all of those Varieties, Suppliers and Products (Thought that this is how you structure database?)

Dan
Go to the top of the page
 
JonSmith
post Jun 14 2018, 03:44 AM
Post#5



Posts: 3,420
Joined: 19-October 10



QUOTE
There's perfectly good reason as to why I have separate tables..


Sorry but I disagree, there is no appropriate reason to duplicate data like this.

QUOTE
My live database has 25 products (tblPack_Types), 85 varieties (tblVariety) and roughly 300 suppliers (tblGrowers). A record in Prodlive is made out of the info from those 3 tables, It can be a combination of all of those Varieties, Suppliers and Products (Thought that this is how you structure database?)


No, thats not. You structure a data in what is called a 'Normal' fashion. See Normalisation for a detailed explanation.

Its quite possible that your structure for the 3 other tables is quite sound but you don't duplicate those records in some sort of summary or printing table.
You use queries to build the data you need on the fly.

Why do you think you need this table instead of just using a query?
Go to the top of the page
 
ciapul12
post Jun 14 2018, 04:29 AM
Post#6



Posts: 250
Joined: 7-June 14



Some great points raised here! In any normal circumstance I'd completely agree with you however this our system is only capable of pulling data from specified data-source and queries aren't one of those sources.
The way we print data is via a networking system which takes data from table and passes it to the unit located on production line. That unit has a basic UI which allows operator to select Job (Job being the record from Prodlive table)

If we were printing directly from the access then as you've mentioned having a query would be the way to go but printing software does not see queries (just tables) which is why I'm merging data from 3 tables into one.
Now I'm struggling to find the solution to amend prices efficiently. There may be hundreds of records/Job for Avocado in Prodlive table and I'd like to be able to change the price value or possibly even other fields in one go...

Regards
Dan
Go to the top of the page
 
JonSmith
post Jun 14 2018, 04:39 AM
Post#7



Posts: 3,420
Joined: 19-October 10



Ok, that does present an interesting challenge. What is the printing software?
Go to the top of the page
 
ciapul12
post Jun 14 2018, 06:19 AM
Post#8



Posts: 250
Joined: 7-June 14



Hi,
Can't really say exactly what system we're using but can explain more or less how it works..

The networking system drives the entire printing operation but alone is useless, database in question provides system with all necessary information, the networking system then passes data onto the unit on production line from which user can select job and print that info on the pack.
Sounds simple and it is simple for the users but from an IT point of view the things aren't simple..

The maintenance of DB one of the most challenging aspects because certain information can be subject to changes at any time for example the Price, Price is one of those things that gets changed on a frequent basis due to Retailers want to be competitive so they'll drop their prices as soon as other retailer does it..
We actually have a massive product recall where price was decreased by only 20p on one of our products but the person who was amending records in Prodlive table missed some and as a result roughly a million packs had to be repacked due to incorrect price...

This is why I'm looking for some method to amend multiple records in one go, Yes I could export records to excel but it becomes messy and I don't personally like that idea...


Would greatly appreciate if someone is able to help me out.
Dan
This post has been edited by ciapul12: Jun 14 2018, 06:21 AM
Go to the top of the page
 
JonSmith
post Jun 14 2018, 06:20 AM
Post#9



Posts: 3,420
Joined: 19-October 10



How often is the printing table updated?

JS
Go to the top of the page
 
ciapul12
post Jun 14 2018, 06:22 AM
Post#10



Posts: 250
Joined: 7-June 14



Sometime it doesn't need to be updated for a week and sometimes as often as 3-4 times a week depending on UK's Retail...
Go to the top of the page
 
JonSmith
post Jun 14 2018, 06:27 AM
Post#11



Posts: 3,420
Joined: 19-October 10



Ok, my train of thought is to treat it like a temp table you see. That any time you update any of the data in the other tables you run delete all query and append all query. Its very brute force but would ensure the data matches and you don't miss any records?
You might want to put the temp table in a different file so it can be compacted easily.

Hopefully someone else has a cleaner idea.
Go to the top of the page
 
ciapul12
post Jun 14 2018, 07:07 AM
Post#12



Posts: 250
Joined: 7-June 14



Are you in the position of supplying a sample so that I can test if it'll work?
Go to the top of the page
 
theDBguy
post Jun 14 2018, 10:01 AM
Post#13


Access Wiki and Forums Moderator
Posts: 72,423
Joined: 19-June 07
From: SunnySandyEggo


Hi Dan,

Re: "Now I'm struggling to find the solution to amend prices efficiently."

Have you tried my suggestion to use an UPDATE query?

Just curious...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
ciapul12
post Jun 14 2018, 10:35 AM
Post#14



Posts: 250
Joined: 7-June 14



Not really sure how to do it, I have never used update queries..
Go to the top of the page
 
theDBguy
post Jun 14 2018, 10:46 AM
Post#15


Access Wiki and Forums Moderator
Posts: 72,423
Joined: 19-June 07
From: SunnySandyEggo


Try these steps:

1. Click the Create button on the Ribbon
2. Click on Query Design
3. Select the Production table from the list and click the Add button
4. Select the Live table from the list and click the Add button
5. Click on the Close button
6. If Access didn't do it already, link the two tables using the Prod_ID field
7. From the Live table, drag the Price field onto the design grid (assuming you want to update the prices in the live table; otherwise, drag the Price field from the Production table)
8. From the Design tab on the Ribbon, click on Update
9. In the Update To: line in the query grid, enter the following

tblPack_Type.Price

10. The query might look something like this

Attached File  update.PNG ( 18.74K )Number of downloads: 1


11. Run the query
12. Check the result

Make sure you have a backup copy before trying the above.

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
ciapul12
post Jun 15 2018, 02:40 PM
Post#16



Posts: 250
Joined: 7-June 14



Hi theDBguy!
Man, what a fantastic mind you have :-)
I have tested it on price field and it did worked which I'm super happy about.
Can the same approach be applied to other fields?

Best Regards
Dan
Go to the top of the page
 
theDBguy
post Jun 15 2018, 02:47 PM
Post#17


Access Wiki and Forums Moderator
Posts: 72,423
Joined: 19-June 07
From: SunnySandyEggo


Hi Dan,

Glad to hear you managed to make it work. Yes, the same approach should work for the other fields you want to update as well.

Just make sure to always have a backup copy before making any data changes, just to be safe.

Good luck with your project.

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st June 2018 - 09:09 AM