Printable Version of Topic

Click here to view this topic in its original format

UtterAccess Forums _ Access Tables + Relationships _ Updating Records In Main Database?

Posted by: ciapul12 Dec 7 2017, 06:21 AM

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
Dan ( 1.24MB ): 8

Posted by: mike60smart Dec 7 2017, 07:35 AM

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??

Posted by: GroverParkGeorge Dec 7 2017, 07:43 AM

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.


Posted by: ciapul12 Dec 7 2017, 09:54 AM

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

Posted by: ciapul12 Dec 7 2017, 09:54 AM

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…

Posted by: ciapul12 Dec 8 2017, 06:19 AM

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.


Posted by: John Vinson Dec 8 2017, 08:31 PM

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

Posted by: ciapul12 Dec 13 2017, 03:45 AM

Appreciate your advice however this was a quick project in regards to the time I was given to design database.
I was hoping that someone could do a sample for me so that I could understand better how it works.
I will learn when I'll have time to actually read all posts about relationships etc.


Posted by: GroverParkGeorge Dec 13 2017, 07:56 AM

Unfortunately, it's still not at all clear what your database is really supposed to be doing.

You did elaborate on how you want the database to work, but not very much on the business itself.

"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"

So, am I right in thinking that you are collecting information about "products", "varieties" and suppliers of those products? And then using that information to print something?

I also strongly reinforce the idea that, as has been suggested, you should stop wasting time on what definitely appears to be an inadequately designed set of tables and invest time in learning how to use Access effectively.

Posted by: John Vinson Dec 14 2017, 03:12 PM

You understand your business, what "products" and "varieties" and "printers" and "suppliers" are and how they relate.

We do not. All we can see is what you type into messages here, and so far that's been more confusing than helpful.

Also, typically the unpaid volunteers here will help you with concepts and design suggestions - creating a working sample database is going rather beyond what's typical. Could you consider hiring someone to assist you with this, or to design and build the database?