Update the second table with the same item # and description using a query (1 Viewer)

[access2010]

Can we please get a suggestion on how to create a query to update the same item from two different tables? The second table has four choices, but has the SAME COMMON NUMBER?
We do not want to again enter the same COMMON NUMBER in the second table again.

Thank you. Nicole

 

[MajP]

You need to spend some time and explain what you mean. I could not even start to guess. No idea which table is the first and which is the second. Both are numbered 1. How about use real names instead. Talk in table names and field names. You mention something about 4 choices. What is that supposed to mean? Four rows, four columns, four yes no fields? I do not see 4 of anything. What is a common number? What information is supposed to move from where to where? If you want an answer spend some time explaining in detail.

 

[MajP]

Also, is this information coming from another source and you are stuck with the format? If not these tables are a mess and need to be redone. We can help you create properly structure tables. Whenever you see a table with repeating numbered fields it is immediately clear that the data is not normalized.

 

[jdraw]

Access2010,
With almost 1000 posts and 15 years on the forum, I agree with MajP --give a meaningful description of the issue/opportunity and what you want to accomplish.

 

[The_Doc_Man]

There is a basic principle that if you have the same element and value in two different tables and it makes sense in the context of your project that you need to change it in both places at the same time (in effect if not in actual fact), the immediate suspicion is that they are not two different representations. They are avatars of a single thing. If so, they should not be in two different tables. Whatever this thing is, it should only exist in ONE PLACE and anything that needs to know it can find it in that place. If you take that approach, you don't have an issue of a double-barreled update in the first place.

MajP's comment about normalization is directly applicable. A normalized design would have prevented this situation from occurring.

PLEASE, try to develop the habit of giving more detailed explanations. Without some kind of reasonable context, it will be hard - verging on impossible - to help you very well.

 

[access2010]

I do appreciate all of your comments.
We are trying to do 4 different comparisons for each of the stock codes at different time elements.

Our table”01” currently contains 103 fields, with the Primary Key “Symbol_Stock”
I thought that we could create a new table “01K” with the required fields to use for comparisons without increasing the number of (rows) fields in Table “01”
Only the Stock Code and Stock names are the same in both tables.
I was hoping to join the two tables by using a Query and the “Symbol_Stock” field as the join criterion.

 

[Pat Hartman]

It is sounding more like your tables are not normalized. Maybe you could post the schema and some data.

 

[MajP]

Still makes little or no sense. Table one as provided does not have 103 fields. Table 01k has 3 not 4 repeating groups of fields.
Why the non-normalized repeating groups of fields? What is the purposes?
Why not simply a record for each group? Is this coming from a spread sheet or external data source?

If you simply want to update table 01 with some values in 01K simply join on symbol_stock an do an update query update query.

UPDATE investments01k_tbl    AS 01k
INNER JOIN investments01_tbl AS 01
ON         [01K].symbol_stock = [01].symbol_stock
SET        [01].bmo_intel = [bmo_intel_02],
           [01].cfra_advc = [cfra_advc_02],
           [01].researchdate = [researchdate_02];

 

[access2010]

Still makes little or no sense. Table one as provided does not have 103 fields. Table 01k has 3 not 4 repeating groups of fields.
Why the non-normalized repeating groups of fields? What is the purposes?
Why not simply a record for each group? Is this coming from a spread sheet or external data source?
View attachment 111941

If you simply want to update table 01 with some values in 01K simply join on symbol_stock an do an update query update query.

UPDATE investments01k_tbl    AS 01k
INNER JOIN investments01_tbl AS 01
ON         [01K].symbol_stock = [01].symbol_stock
SET        [01].bmo_intel = [bmo_intel_02],
           [01].cfra_advc = [cfra_advc_02],
           [01].researchdate = [researchdate_02];

Thank you * * * Majp for your suggestion THAT WORKS and which I * * * appreciate.
The main table in our database does have 103 fields, so I thought a new table would be helpful.
The data that we use comes from MANY different sources and is manually entered into the database.
I am sorry but I do not know what normalized means.
We use the data that we have to help our endowment fund know which equities to hold or sell.
Each Stock Name has its research history and comparative data attached to it on a separate form.
I am a volunteer and not even a Junior Programmer, and * * * appreciate your help.

 

[MajP]

I am sorry but I do not know what normalized means.

Your comparisons should look like separate records with a unique research date, and not groups of columns in a table

InvestmentsNew

symbol_stock	ResearchDate	bmo_intel	cfra_advc	mrng_advc	scotia_advc	bmo_va_advc	rbc_advc	fair_value	td_advc
GIM-J.TO	2/2/2002​	2	2	2	2	2	2	$2.00​	2
GIM-J.TO	3/3/2003​	3	3	3	3	3	3	$3.00​	3
GIM-J.TO	4/4/2004​	4	4	4	4	4	4	$4.00​	4
SHRM	2/2/2002​	2	2	2	2	2	2	$2.00​	2
SHRM	3/3/2003​	3	3	3	3	3	3	$3.00​	3
SHRM	4/4/2004​	4	4	4	4	4	4	$4.00​	4

Now you can do something with that format but not with what you had.

I do not know what you plan to update, but I assume it is the most current update.
That can be found using an aggregate query
qrymaxreviewrecords qrymaxreviewrecords

ResearchDate	bmo_intel	cfra_advc	mrng_advc	scotia_advc	bmo_va_advc	rbc_advc	fair_value	td_advc
4/4/2004​	4	4	4	4	4	4	$4.00​	4
4/4/2004​	4	4	4	4	4	4	$4.00​	4

So if you store your comparisons in a properly normalized table you can update your other table on the most current update in a single query

However in this propre format you can also find the highest scores in the period
Query2 Query2

symbol_stock	MaxOfbmo_intel	MaxOfcfra_advc	MaxOfmrng_advc	MaxOfscotia_advc	MaxOfbmo_va_advc	MaxOfrbc_advc	MaxOffair_value	MaxOftd_advc
GIM-J.TO	4	4	4	4	4	4	$4.00​	4
SHRM	4	4	4	4	4	4	$4.00​	4

Which is not too illustrative since no different in this case

Or the mins for the period
qryMinScores qryMinScores

or the avg, etc.

symbol_stock	MinOfbmo_intel	MinOfcfra_advc	MinOfmrng_advc	MinOfscotia_advc	MinOfbmo_va_advc	MinOfrbc_advc	MinOffair_value	MinOftd_advc
GIM-J.TO	2	2	2	2	2	2	$2.00​	2
SHRM	2	2	2	2	2	2	$2.00​	2

 

[The_Doc_Man]

I am sorry but I do not know what normalized means.

"Normalized" is a database term (among other disciplines) that describes a particular style of laying a table and its contents. It is a way of gathering things together to keep "like" with "like" and to separate the things that are not like each other. Once you understand it, database normalization is a way to save space AND save effort.

Thinking back on your questions that I have seen in the past, I realize that part of your problem is continually thinking along what we call "flat-file" lines. A spreadsheet is a flat file in which you list data in a grid. But sometimes your data has an inherent structure. A spreadsheet - Excel or any other brand-name - has really no easy way to efficiently represent hierarchies or parent/child structures. The table you showed us has what is called a "repeating group" which is one big indicator of normalization issues.

For instance, you show table Investments01K_tbl with fields such as BMO_Intel_02, CFRA_Advc_02, MRNG_Advc_02, .... BMO_Intel_03, ...., BMO_Intel_04, etc. That should be TWO tables... Investments01K_tbl and a child table with a field showing BMO_Intel, CFRA_Advc, MRNG_Advc, ... plus some field that links it back to the specific record in Investments01K_tbl. Flattening out those items into the single table causes you have to write extra code for each of the _02, _03, and _04 columns. Not to mention the headaches that would be caused by needing to add a 5th column.

I know you folks work by committee and are volunteers but let me say that you will ALL work a LOT less if you learn to normalize your databases. You can search this forum for keyword "normalize" because this IS a database forum. However, if you really wanted to study this, search the general web for "Database Normalization." You need the "Database" as a qualifier for web searches because "normalization" also is used in the fields of mathematics, chemistry, medicine, physics, and international diplomacy. At least at first, pick articles from .EDU sites - but don't ignore the .COM sites that have explanations of normalization. Just realize that they might have something they waht to sell to you.

If you do some reading on the subject, you might see WHY you will work less down the road. This is DEFINITELY a case of "pay me now or pay me later" when designing a database, and trust me this much: The later you wait, the more you will have to pay.

 

[jdraw]

I suggest the second link and, if you work through the tutorial, you will gain first hand experience in Normalization.

Here is a reference to articles on Normalization by Roger Carlson.

Here is link to a tutorial taking a business' description and breaking it down systematically into Normalized Tables and Relationships suited to building a database.

 

[access2010]

Your comparisons should look like separate records with a unique research date, and not groups of columns in a table

InvestmentsNew

symbol_stock	ResearchDate	bmo_intel	cfra_advc	mrng_advc	scotia_advc	bmo_va_advc	rbc_advc	fair_value	td_advc
GIM-J.TO	2/2/2002​	2	2	2	2	2	2	$2.00​	2
GIM-J.TO	3/3/2003​	3	3	3	3	3	3	$3.00​	3
GIM-J.TO	4/4/2004​	4	4	4	4	4	4	$4.00​	4
SHRM	2/2/2002​	2	2	2	2	2	2	$2.00​	2
SHRM	3/3/2003​	3	3	3	3	3	3	$3.00​	3
SHRM	4/4/2004​	4	4	4	4	4	4	$4.00​	4

Now you can do something with that format but not with what you had.

I do not know what you plan to update, but I assume it is the most current update.
That can be found using an aggregate query
qrymaxreviewrecords qrymaxreviewrecords

ResearchDate	bmo_intel	cfra_advc	mrng_advc	scotia_advc	bmo_va_advc	rbc_advc	fair_value	td_advc
4/4/2004​	4	4	4	4	4	4	$4.00​	4
4/4/2004​	4	4	4	4	4	4	$4.00​	4

So if you store your comparisons in a properly normalized table you can update your other table on the most current update in a single query

However in this propre format you can also find the highest scores in the period
Query2 Query2

symbol_stock	MaxOfbmo_intel	MaxOfcfra_advc	MaxOfmrng_advc	MaxOfscotia_advc	MaxOfbmo_va_advc	MaxOfrbc_advc	MaxOffair_value	MaxOftd_advc
GIM-J.TO	4	4	4	4	4	4	$4.00​	4
SHRM	4	4	4	4	4	4	$4.00​	4

Which is not too illustrative since no different in this case

Or the mins for the period
qryMinScores qryMinScores

or the avg, etc.

symbol_stock	MinOfbmo_intel	MinOfcfra_advc	MinOfmrng_advc	MinOfscotia_advc	MinOfbmo_va_advc	MinOfrbc_advc	MinOffair_value	MinOftd_advc
GIM-J.TO	2	2	2	2	2	2	$2.00​	2
SHRM	2	2	2	2	2	2	$2.00​	2

Thank you for your suggestion and after creating a new database and its forms, I am having some problems.
The Query returns the results I was looking for.
I cannot enter any data using the Form or Query. BUT I can edit data in each of the tables in the query separately.
Could I please get a suggestion on how to fix this interesting problem?

Your comparisons should look like separate records with a unique research date, and not groups of columns in a table

InvestmentsNew

symbol_stock	ResearchDate	bmo_intel	cfra_advc	mrng_advc	scotia_advc	bmo_va_advc	rbc_advc	fair_value	td_advc
GIM-J.TO	2/2/2002​	2	2	2	2	2	2	$2.00​	2
GIM-J.TO	3/3/2003​	3	3	3	3	3	3	$3.00​	3
GIM-J.TO	4/4/2004​	4	4	4	4	4	4	$4.00​	4
SHRM	2/2/2002​	2	2	2	2	2	2	$2.00​	2
SHRM	3/3/2003​	3	3	3	3	3	3	$3.00​	3
SHRM	4/4/2004​	4	4	4	4	4	4	$4.00​	4

Now you can do something with that format but not with what you had.

I do not know what you plan to update, but I assume it is the most current update.
That can be found using an aggregate query
qrymaxreviewrecords qrymaxreviewrecords

ResearchDate	bmo_intel	cfra_advc	mrng_advc	scotia_advc	bmo_va_advc	rbc_advc	fair_value	td_advc
4/4/2004​	4	4	4	4	4	4	$4.00​	4
4/4/2004​	4	4	4	4	4	4	$4.00​	4

So if you store your comparisons in a properly normalized table you can update your other table on the most current update in a single query

However in this propre format you can also find the highest scores in the period
Query2 Query2

symbol_stock	MaxOfbmo_intel	MaxOfcfra_advc	MaxOfmrng_advc	MaxOfscotia_advc	MaxOfbmo_va_advc	MaxOfrbc_advc	MaxOffair_value	MaxOftd_advc
GIM-J.TO	4	4	4	4	4	4	$4.00​	4
SHRM	4	4	4	4	4	4	$4.00​	4

Which is not too illustrative since no different in this case

Or the mins for the period
qryMinScores qryMinScores

or the avg, etc.

symbol_stock	MinOfbmo_intel	MinOfcfra_advc	MinOfmrng_advc	MinOfscotia_advc	MinOfbmo_va_advc	MinOfrbc_advc	MinOffair_value	MinOftd_advc
GIM-J.TO	2	2	2	2	2	2	$2.00​	2
SHRM	2	2	2	2	2	2	$2.00​	2

Thank you Majp for your suggestions and after creating a new database and its forms, I am having some problems.
The Query returns the results I was looking for.
I cannot enter any data using the Form or Query. BUT I can edit data in each of the tables in the query separately.
Could I please get a suggestion on how to fix this interesting problem?

 

[access2010]

"Normalized" is a database term (among other disciplines) that describes a particular style of laying a table and its contents. It is a way of gathering things together to keep "like" with "like" and to separate the things that are not like each other. Once you understand it, database normalization is a way to save space AND save effort.

Thinking back on your questions that I have seen in the past, I realize that part of your problem is continually thinking along what we call "flat-file" lines. A spreadsheet is a flat file in which you list data in a grid. But sometimes your data has an inherent structure. A spreadsheet - Excel or any other brand-name - has really no easy way to efficiently represent hierarchies or parent/child structures. The table you showed us has what is called a "repeating group" which is one big indicator of normalization issues.

For instance, you show table Investments01K_tbl with fields such as BMO_Intel_02, CFRA_Advc_02, MRNG_Advc_02, .... BMO_Intel_03, ...., BMO_Intel_04, etc. That should be TWO tables... Investments01K_tbl and a child table with a field showing BMO_Intel, CFRA_Advc, MRNG_Advc, ... plus some field that links it back to the specific record in Investments01K_tbl. Flattening out those items into the single table causes you have to write extra code for each of the _02, _03, and _04 columns. Not to mention the headaches that would be caused by needing to add a 5th column.

I know you folks work by committee and are volunteers but let me say that you will ALL work a LOT less if you learn to normalize your databases. You can search this forum for keyword "normalize" because this IS a database forum. However, if you really wanted to study this, search the general web for "Database Normalization." You need the "Database" as a qualifier for web searches because "normalization" also is used in the fields of mathematics, chemistry, medicine, physics, and international diplomacy. At least at first, pick articles from .EDU sites - but don't ignore the .COM sites that have explanations of normalization. Just realize that they might have something they waht to sell to you.

If you do some reading on the subject, you might see WHY you will work less down the road. This is DEFINITELY a case of "pay me now or pay me later" when designing a database, and trust me this much: The later you wait, the more you will have to pay.

Thank you for your suggestions, which I appreciate.

 

[MajP]

Thank you Majp for your suggestions and after creating a new database and its forms, I am having some problems.
The Query returns the results I was looking for.
I cannot enter any data using the Form or Query. BUT I can edit data in each of the tables in the query separately.
Could I please get a suggestion on how to fix this interesting problem?

I do not know what you eventually decided and what query we are talking about. I do not know what form you reference of what query it has as a recordsource.
Did you fix the table design.
The aggreagate query returning the last udpates is not editable, and should not be. This will make a challenge to update the source table. There are workarounds.

 

[access2010]

I do not know what you eventually decided and what query we are talking about. I do not know what form you reference of what query it has as a recordsource.
Did you fix the table design.
The aggreagate query returning the last udpates is not editable, and should not be. This will make a challenge to update the source table. There are workarounds.

New Experience. In the past we had been able to solve our MsAccess 2003 problems internally and with the help of the nice people in the forum. This time we were stumped even with the help of the Forum Group. A professor at the local Computer Science College offered our problem of connecting the Two Data Tables to our desired forms to his students and not one of them was able to fix our request. Majp could I pay you or make a donation to a charity of your choice to help us get the forms and tables working? Thank you very much. Nicole

 

[MajP]

New Experience. In the past we had been able to solve our MsAccess 2003 problems internally and with the help of the nice people in the forum. This time we were stumped even with the help of the Forum Group. A professor at the local Computer Science College offered our problem of connecting the Two Data Tables to our desired forms to his students and not one of them was able to fix our request. Majp could I pay you or make a donation to a charity of your choice to help us get the forms and tables working?

I must be missing something, because what you seem to be describing sounds trivial but I just do not understand the details. I am absolutely confident this forum can solve your needs, assuming the database is not so poorly designed that putting a band-aid over top of existing problems is not the solution.
Sometime people present databases that do not make any sense, and then only want some complicated code to make what should be easy very convoluted. The solution in this case is to bite the bullet and fix the tables. There are people in this forum that will provide a ton of work for free if we can understand the problem. So far you do not provide enough detail, and I have been only guessing at what you are asking.
Can you post the whole database or is the data proprietary?

 

[access2010]

Both my boss and I appreciate your suggestion MajP to have professionals look at our database problems.
We have some problems.
When forms # 01, # 02 and number # 03 are opened from the main menu, there is no Menu Bar (File, View) on these forms. But when opened directly the Full Menu Bar is available.
When these 3 forms are opened, we cannot enter any data.
When we open form # 03 we cannot get it to open as a full form.
We appreciate this kind suggestion to help us and are looking for a successful solution to our problem as after a very long time I am still confused as to how to fix our problem.

 

[Pat Hartman]

1. You are opening these forms as dialog and maximizing the form. Therefore, you can't see the Access window behind them. If you click on the form's header and move it, you can see the Access window behind the forms. You can't get there though because forms opened in dialog view prevent focus from moving to any other part of the Access interface. Here's a picture.

2. The reason that the forms are not updateable is because the underlying queries are not updateable. Your query is joining on data field to data field rather than PK to FK. Access cannot figure out the cardinality of the relationship and therefore, the query is not updateable. I looked at the data and nonticed that the Investment_ID in both tables matched so I changed the query to join on the ID fields. That made the query updateable. HOWEVER, there is something seriously wrong with your schema because it makes no sense for two tables to have the same autonumber values. So, I made the query updateable but you have much bigger problems than I can solve as I have mentioned numerous times in the past.

 

[access2010]

1. You are opening these forms as dialog and maximizing the form. Therefore, you can't see the Access window behind them. If you click on the form's header and move it, you can see the Access window behind the forms. You can't get there though because forms opened in dialog view prevent focus from moving to any other part of the Access interface. Here's a picture.
View attachment 112037
2. The reason that the forms are not updateable is because the underlying queries are not updateable. Your query is joining on data field to data field rather than PK to FK. Access cannot figure out the cardinality of the relationship and therefore, the query is not updateable. I looked at the data and nonticed that the Investment_ID in both tables matched so I changed the query to join on the ID fields. That made the query updateable. HOWEVER, there is something seriously wrong with your schema because it makes no sense for two tables to have the same autonumber values. So, I made the query updateable but you have much bigger problems than I can solve as I have mentioned numerous times in the past.
View attachment 112038

Thank you Pat Hartman, I appreciate your note. Could you please let me know where your query is?