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



Posts: 226
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
Dan
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: 7
 
Go to the top of the page
 
mike60smart
post Dec 7 2017, 07:35 AM
Post#2


UtterAccess VIP
Posts: 12,368
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?

Mike

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

Go to the top of the page
 
GroverParkGeorge
post Dec 7 2017, 07:43 AM
Post#3


UA Admin
Posts: 31,245
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.

Thanks.

--------------------
Go to the top of the page
 
ciapul12
post Dec 7 2017, 09:54 AM
Post#4



Posts: 226
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
 
ciapul12
post Dec 7 2017, 09:54 AM
Post#5



Posts: 226
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
 
ciapul12
post Dec 8 2017, 06:19 AM
Post#6



Posts: 226
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.

Regards
Dan
Go to the top of the page
 
John Vinson
post Dec 8 2017, 08:31 PM
Post#7


UtterAccess VIP
Posts: 4,146
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
 
ciapul12
post Dec 13 2017, 03:45 AM
Post#8



Posts: 226
Joined: 7-June 14



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.

Thanks
Dan
Go to the top of the page
 
GroverParkGeorge
post Dec 13 2017, 07:56 AM
Post#9


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


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.



--------------------
Go to the top of the page
 
John Vinson
post Dec 14 2017, 03:12 PM
Post#10


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


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?

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


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 05:43 AM