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 In Main Database?, Access 2010    
post Dec 7 2017, 06:21 AM

Posts: 225
Joined: 7-June 14

Hi All
I desperately need help please.
I reckon for some of you it’s simple but I’ve been trying to learn about the relationships and still stack where I was 2 weeks ago…
What I need is, I have 3 tables where I store the information, 1 table for suppliers/growers, another one for varieties and 3rd one for all different products. Then there’s main table which user populate by using a simple form called Prodlive1.
Everything works but when I change some details in one of those 3 earlier mentioned tables, the records in Prodlive1 table do not update…
Real data base contains thousands of records so for example if I have 200 records referring to a specific product in Prodlive1 table and I have changed the price in pack type table for that product I then need to update manually 200 records… The same things go for other information from those 3 sub tables…

Can you please help me? I’m losing my mind :-(

I’ve attached my database with sample data..

Best Regards
This post has been edited by ciapul12: Dec 7 2017, 06:21 AM
Attached File(s)
Attached File  Prod_Live_FE__2_.zip ( 1.24MB )Number of downloads: 5
Go to the top of the page
post Dec 7 2017, 07:35 AM

UtterAccess VIP
Posts: 12,350
Joined: 6-June 05
From: Dunbar,Scotland

Hi Dan

Well I am having great difficulty understanding your 3 tables

In your tblPack_Type you have a field called "Variety-id", But you are linking Pack_ID to tblVariety ??

Should you not be linking Variety_ID to Variety_ID in tblVariety??

Also, there is not relationship between tblGrowers and the other tables??

Hope this helps?


Get happiness out of your work or you may never know what happiness is.

Go to the top of the page
post Dec 7 2017, 07:43 AM

UA Admin
Posts: 31,195
Joined: 20-June 02
From: Newcastle, WA

Please explain, in plain business language, the business this database is supposed to be tracking. You are, for example, growing and selling produce? Or are you buying produce?

What's the reason for the database? What business process will it support?

Based on the tables alone, there seem to be several structural problems, but not knowing what's being tracked does make the task of analyzing it more difficult.


Go to the top of the page
post Dec 7 2017, 09:54 AM

Posts: 225
Joined: 7-June 14

Hi GroverParkGeorge
This database drives network of printers.
Prodlive1 table is directly linked to printing system and 3 other tables hold the information about suppliers, product and varieties.
User then selects required info from drop-downs (front End) and creates new record which is combination of those 3 variables (grower, pack type and variety)
I just need to be able to control records in Prodlive1 via those 3 tables so if I change any of details in these 3 tables then these changes are reflected in records in Prodlive1 table...

Go to the top of the page
post Dec 7 2017, 09:54 AM

Posts: 225
Joined: 7-June 14

Hi mike60smart
I believe you’re right in what you’re saying… the reason these ID’s are there is because I was trying to do it myself without enough knowledge and I guess at some point it become messy…
Go to the top of the page
post Dec 8 2017, 06:19 AM

Posts: 225
Joined: 7-June 14

Is it that difficult? I have hoped that it will be something simple..
My deadline is Today..
I guess I need to go with what I have.

Thanks everyone.

Go to the top of the page
John Vinson
post Dec 8 2017, 08:31 PM

UtterAccess VIP
Posts: 4,143
Joined: 6-January 07
From: Parma, Idaho, US

I think you're misunderstanding how Relationships (and relational databases) work! One basic principle is to avoid redundancy: information should be stored ONCE, in only one place; if you're copying information from the other three tables into Prodlive1, DON'T! The information should - must! - be stored only in the tables into which you enter it, and then linked together for display. Each table should contain information only about one "Entitytype" - tblProducts should store information only about Products, nothing about varieties or suppliers, and so on. I suspect your Prodlive1 table contains information about all three of your entitytypes, information which already exists in the other tables; and therefore the Prodlive1 table should simply not exist. Instead you should have a Form with appropriate tools (Combo Boxes, Subforms, etc.) to display information from all three tables (and probably from additional tables); if you're assuming that information must all be gathered into one table to be usable, that assumption is simply wrong.

If you have not done so already, please read the "getting started" topic HERE. If you have read this, please post some relevant details of your tables (table name, brief description of the type of entity it models, and relationships).

Note also that the folks who answer questions here are all unpaid volunteers, with other demands on our time; you may not get responses as fast as you'd like, but we do our best.

John W. Vinson
Wysard of Information
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    11th December 2017 - 11:34 PM