Create a query that copies values from a table and updates when other tables values are changed

[AccessStudent]

I have a table called All_Sites and a table called Budget_Table I am trying to copy all the values from All_Sites to Budget_Table. I need Budget_Table to update whenever new values are added to All_sites. I tried using an update query and entering [All_Sites].[column name] but it doesn’t seem to work. Does anyone have any ideas. I’m really new to access as I am a student and just learned it within a few days I have everything else done this is just the last thing I need. Thanks in advance

 

[plog]

I need Budget_Table to update whenever new values are added to All_sites.

That's not how databases work. Data isn't to be moved around, its to be referenced. It lives in one table and if you need data from 2 tables together you JOIN them in a query and use the query.

I suggest you post 2 paragraphs and your database:

Paragraph 1 is an overview of what your organization does. No jargon, no mention of data. Just tell us what it is you do.

PAragraph 2 tell us what this database will help you accomplish within that organization. What data does this represent and how does the database further that?

Then post either a sample of your database or complete the Relationship Tool in Access, expand so all table fields show and post a screenshot of that.

From there we can help you get to where you want to go. But the path you are taking currently is incorrect.

 

[AccessStudent]

That's not how databases work. Data isn't to be moved around, its to be referenced. It lives in one table and if you need data from 2 tables together you JOIN them in a query and use the query.

I suggest you post 2 paragraphs and your database:

Paragraph 1 is an overview of what your organization does. No jargon, no mention of data. Just tell us what it is you do.

PAragraph 2 tell us what this database will help you accomplish within that organization. What data does this represent and how does the database further that?

Then post either a sample of your database or complete the Relationship Tool in Access, expand so all table fields show and post a screenshot of that.

From there we can help you get to where you want to go. But the path you are taking currently is incorrect.

Thanks for the advice I’ll take some pictures and write a more in depth description for the question.

 

[AccessStudent]

That's not how databases work. Data isn't to be moved around, its to be referenced. It lives in one table and if you need data from 2 tables together you JOIN them in a query and use the query.

I suggest you post 2 paragraphs and your database:

Paragraph 1 is an overview of what your organization does. No jargon, no mention of data. Just tell us what it is you do.

PAragraph 2 tell us what this database will help you accomplish within that organization. What data does this represent and how does the database further that?

Then post either a sample of your database or complete the Relationship Tool in Access, expand so all table fields show and post a screenshot of that.

From there we can help you get to where you want to go. But the path you are taking currently is incorrect.

I have a table called All_Sites and a table called Budget_Table I am trying to copy all the values from All_Sites to Budget_Table. I need Budget_Table to update whenever new values are added to All_sites. I tried using an update query and entering [All_Sites].[column name] but it doesn’t seem to work. Does anyone have any ideas. I’m really new to access as I am a student and just learned it within a few days I have everything else done this is just the last thing I need. Thanks in advance

The Table All_Sites is receiving information from a form the Columns are
Item_Main Item_Sub Total_Onhand Site_Loc
From the form which is name Inventory_Form I have created drop down lists which show the 4 different locations the different items and then the sub items which are sorted by the items.

Within the Table Budget_Table I have columns named
Item_Main Item_Sub Item_Location
I need these values to all be the same as the values in All_Sites
The other columns that are already automatically updated are Withdrawal_amt which was done using VBA on a withdrawal form button. Then Cost_Item and Total_Purchased
The goal of this is to create an inventory database which shows how many items we have and where they are located. I created a budget table so that our company could easily create the budget at the end of the year.
I have attached a picture of the relationships I created and the query I attempted to make.
Any help would be appreciated thank you.

 

[plog]

So you gave me 1 out of the 3 things I requested--a screenshot of your Relationships. Since I don't know the purpose of your database I will just work with the general things I see wrong:

1. Circular relationships. There should only be 1 way to travel between 2 tables. I can trace 3 paths from Budget_Table to Withdraw_Table. That is wrong. I don't know the 1 correct path, but I do know you have 2 too many.

2. No primary keys. These are very important, they ensure that you are able to uniquely identify a rows in your tables. They are the key to joining tables together, they make sure you connect your tables in the manner you want.

3. Incorrect joining. Budget_Table is joined to All_Sites in 3 different ways, this cannot be correct. Its not unusual to JOIN a table to another with multiple fields, but that is not what you have done. You have brought in 3 different instances of All_Sites and joined them to Budget_Table in ways.
I am sure there are more specific issues with your data that can only be revealed by knowing more about your data. That's why I wanted the 2 paragraphs I requested.

Honestly, I think you need to step away from this database for a second and read up on normalization:

Database normalization - Wikipedia

en.wikipedia.org

Work througha few tutorials on that. Then get a general understanding of how databases are to work:

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

Then once you have that you can apply what you have learned to your database.

 

[AccessStudent]

Okay thank you that actually helps a lot I wasn’t sure if I should add primary keys either because it didn’t seem to change anything at first so I removed them but then I made a lot of changes. I’ll get reading up on normalization since during my original questions on google I saw that pop up a few times.

 

[Pat Hartman]

Primary keys fall into two categories.
1. Natural
2. Surrogate.

Natural keys may be made up of multiple fields and are therefore difficult to work with and annoying in other ways. The easiest solution is to always use Surrogate primary keys which in Access are built with autonumbers. Autonumbers just generate UNIQUE, meaningless numbers. When the table also contains a natural key (single field or multi-field), then add a unique index on the natural key. You need to use the indexes dialog to define a multi-field index. Here's a picture.

Joins are NEVER made on the natural key. They always use the autonumber primary key. Do NOT use table level lookups if you run into something that recommends them, run the other way.