Update query criteria compare a field in another table and update accordingly (1 Viewer)

AccessBee

Registered User.
Local time
Today, 00:48
Joined
Jun 22, 2015
Messages
12
I am programming an Acess Database, the primary data come from an excel sheet. Since Access one to many relationship only works for primary key and another field in another table as foreign key, I can't have normalized database.

I will explain what I mean with a hypothetical situation.
So, let's say I have two tables: WindowT1, CustomerT2.
WindowT1 has fields: WinID (the pk), WindowName.
CustomerT2 fields include CustID (pk), CustomerName, WindowName, WinID.

So, in table "CustomerT2', WinID is the foreign key and I need that field filled in to make one-to-many relationship between these two tables to work. CustomerName and WindowName will be imported and prefilled already from an excel file with append query.

My question is can I make a update query (after the initial append query that brings in the data into Access) such that the criteria is matching WindowName (from CustomerT2) and the update would be the corresponding WinID (the pk number from WindowT1) that would be filled in the field WinID (in CustomerT2)? I know for this to work tables must have relationship and I did that.

Is there a better way to accomplish this task? i.e. through a form (remember info will be prefilled, so can't use combo box to fill in another form field) or maybe can I do this using the first append query (that brings in data)? If code is a must that is welcome too as long as it is easy enough for me to understand.

I am not good at Macro or VBA. The only thing I have done in code builder is After Update event in forms while using combo boxes. So, if there is no easier solution, I will try to deal with codes.
 
Last edited:

MarkK

bit cruncher
Local time
Today, 00:48
Joined
Mar 17, 2004
Messages
8,178
Hi there and welcome.

It doesn't make sense to me to link to WindowName if WindowName is already a field in the Customer record. Will you remove that field from the Customer table later?

But more generally, I suggest that you not describe a hypothetical. A solution to a problem in data is very specific. If you describe a hypothetical problem, then at best, we can suggest a hypothetical solution. How are you sure that the solution is appropriate to your actual problem? Why not describe your actual problem, and we can study that and make actual recommendations?

Hope this helps,
 

AccessBee

Registered User.
Local time
Today, 00:48
Joined
Jun 22, 2015
Messages
12
Hi there and welcome.

It doesn't make sense to me to link to WindowName if WindowName is already a field in the Customer record. Will you remove that field from the Customer table later?

But more generally, I suggest that you not describe a hypothetical. A solution to a problem in data is very specific. If you describe a hypothetical problem, then at best, we can suggest a hypothetical solution. How are you sure that the solution is appropriate to your actual problem? Why not describe your actual problem, and we can study that and make actual recommendations?

Hope this helps,

Hi Markk,

Thank you for your reply. At least somebody replied!
I am dealing with confidential material for my company and I am an intern. Tech people are not allowed to discuss things outside their department, only media people can do that. So, I just wanted to be on the safe side.

I know programming is specific, but the hypothetical situation I mentioned exactly mimic the situation I am in. So, if a code or solution is presented, I can change things accordingly to my needs; at least I have that much programming knowledge (I work with C++). It is just that I started learning Access last week, and haven't gotten the chance to learn SQL or VBA code yet. If hypothetical problem itself becomes a new problem, I will probably post the actual problem, with a little change here and there.

Yes, I do need WinID filled in table "CustimerT2," because I need to link WindowT1 and CustomerT2. Can I do that without using pk from windowT1 table? I don't think I can. Also, WindowT1 table is related to other tables. So, when I do a query, CustomerT1 has to grab other fields from these other tables via WindowT1 table relationship. So yes, I do want WinID filled in, unless there is other option to link those two tables.
 
Last edited:

AccessBee

Registered User.
Local time
Today, 00:48
Joined
Jun 22, 2015
Messages
12
Hi Markk,

Yes, I do need WinID filled in table "CustimerT2," because I need to link WindowT1 and CustomerT2. Can I do that without using pk from windowT1 table? I don't think I can. Also, WindowT1 table is related to other tables. So, when I do a query, CustomerT1 has to grab other fields from these other tables via WindowT1 table relationship. So yes, I do want WinID filled in, unless there is other option to link those two tables.

OMG, I just tried to link table via fields that are not primary keys and it worked. So, I was able to link WindowName in those two tables with one to many relationship. But I do need to know the solution of this situation.

One time, I was filling in table info with combo box in forms. I did not know about bound value changing at that time. So, instead of NameID, the field was populated by the the actual Name. Later I wanted to use update query to update the field, i.e. changing Name to NameID for hundreds of records. So, can a query do that? Lets say another table has Name and NameID. So, query crietria would be recognizing the specific Name in the current table and update would be changing that current field with NameID according to matched Name from another table?

I just presented a silimar but new problem while explaining, but answer to the original problem would solve this one too.
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 03:48
Joined
Jan 23, 2006
Messages
15,364
So this issue is solved?
I think you re recognizing that spreadsheet(EXCEL) and database (Access) are different, and based on different object models. I think you should research Normalization to get more familiar with database and table design.

Good luck with your project.
 
Last edited:

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 02:48
Joined
Feb 28, 2001
Messages
27,001
Just a comment:

Code:
WindowT1 has fields: WinID (the pk), WindowName. 
CustomerT2 fields include CustID (pk), CustomerName, WindowName, WinID.

If this is correct, you have redundant data (not normalized) - or at least it appears that way.

WinID is an FK to WindowT1 but WindowName ALSO appears in WindowT1. You don't need the name in CustomerT2. If you JOINed the two tables, the name would follow the ID number. You also appear to be doing this backwards. However, if the linkage really IS that the WinID can be null (because you have the data in CustomerT2 first and have to back-fill, then there can be NO formal relationship between the tables. You must do some type of lookup to fill in WinID first.

Does this seem to make sense in the light of your problem? If not, then I'm not getting it yet. If so, I have some thoughts on how to do this constructively but I would prefer to wait for your confirmation.
 

AccessBee

Registered User.
Local time
Today, 00:48
Joined
Jun 22, 2015
Messages
12
Code:
[COLOR=black][FONT=Verdana]WindowT1 has fields: WinID (the pk), WindowName. [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]CustomerT2 fields include CustID (pk), CustomerName, WindowName, WinID.[/FONT][/COLOR]

If this is correct, you have redundant data (not normalized) - or at least it appears that way.

I had mentioned earlier that I am aware that my database is not normalized, because I was under the following impression:
AccessBee said:
Since Access one to many relationship only works for primary key and another field in another table as foreign key, I can't have normalized database.

I was working earlier when I tried to link two tables (those were the only two objects that existed in DB at that time) via an ordinary field, but an error message showed "primary key not found" or something like that when those two tables actually HAD autonumbered PK. However, later I figured out that I can connect two tables via a non-pk field. I don't know why that time it worked, maybe I had many other tables and relationship (which seems pretty illogical from a coding POV), or maybe something changed.

So, I was able to create a one-to-many relationship between WinowT1 and CustomerT2 via linking the WindowName (a non-pk) field in two tables and therefore deleted WinID field from CustomerT2. Later when I ran a query that grabbed CustomerName and WinID together it worked fine (thank God).

The_Doc_Man said:
if the linkage really IS that the WinID can be null (because you have the data in CustomerT2 first and have to back-fill, then there can be NO formal relationship between the tables. You must do some type of lookup to fill in WinID first.

So, yes, even if I import data to fill in CustomerT2 first, Access can create the relationship between CustomerT2 and WindowT1 only based on the windowName field; PK don’t have to get involved in the relationship, but for Access's own code (for the link to actually work), pk is must for both of the tables.

Lookup into a table is a bad idea, because it messes up query and VBA code etc. etc. So, I will never use lookup data in the table level. I am happy with using comboBox in Form, but here data was prefilled. So, no autofill of WinID by the comboBox trick.

So, I don’t have to convert info or a particular field back to its ID every time to match another table and have the relationship via pk- Which means I can now have normalized database J.
 
Last edited:

AccessBee

Registered User.
Local time
Today, 00:48
Joined
Jun 22, 2015
Messages
12
If so, I have some thoughts on how to do this constructively but I would prefer to wait for your confirmation.

I do need to know the solution to create such type of query where the criteria is a field from another table instead of just a value and updating according to the match (criteria) through inter-table relationship. This is important for my coding knowledge. I may run into such problem in future again!

Before this particular problem I ran into a similar problem that I just decided to ignore and changed manually instead of trying to know more. Of course, later I got stuck and had to ask the question. This is hypothetical situation#2 that will exactly describe the problem I was in before:

Two related tables: DoorT1, CustomerT2
DoorT1 fields: DoorID, DoorName
CutomerT2: CustID, CustomerName, DoorName
One Form: CustDoorF1 (for data entry into CustomerT2)

The form has two fields included, CustomerName and DoorName which is a combo box. Let’s say we have 4 different door names. So, I went to combobox wizard and made the field available in combobox option. What I did not notice at the beginning (when I was novice in combobox), that the DoorID is automatically included even if I only grab DoorName column. I know, I know, using wizard is probably not the best idea, but in the excitement of discovering cascading comboBox I choce the quick path and ignored manual method of creating the comboBox; but even then PK has to be included. The ComboBox had the following property:

Bound Column=1
Column Width=0”;1”

So you can imagine what happened when I told one of my colleagues, “hey, guess what! I created a form. So, you just put the customer info in, I will check later.” The comboBox showed the DoorName but stored values from DoorID field. At the end of the day, when I went back to check CustomerT2, all I saw was numbers instead of actual names in DoorName field.

I thought about using update query with criteria DoorName (from CustomerT2) which actually has ID (number) by mistake, match that value against DoorID field (in DoorT1), get the corresponding DoorName (from DoorT1) and update the same column DoorName (in CustomerT2) with those correct door names taken from DoorT1(matched by DoorID). It made sense to me that I can do that because these two tables were related by WinID(pk), it was also the same info that got stored by mistake. Of course, I had no idea how to do that, so I forgot about that algorithm and changed 20 records manually. But, what if I had 500 records entered already? What if instead of 4 door names, I had 100 door names? I definitely wouldn’t have forgotten about that algorithm and change manually. That’s what happened when I created the Widnow problem (original problem of this post).


The two problems I mentioned are almost the same:
Window Problem: WindowName came from excel sheet. I wanted a separate column that would be updated or filled in by corresponding WindowID.

Door Problem: DoorName actually filled with DoorID because of wrong programming of comboBox in form. So, I want to update the same column with the corresponding DoorName.

These two will have same type of solution or code. The only difference is updating same or separate column. I wanted to mention that since I don’t know SQL. So, if you choose to provide SQL solution, these two case will be a little different. VBA code is fine too. I just want to learn more.
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 03:48
Joined
Jan 23, 2006
Messages
15,364
Based on this
Two related tables: DoorT1, CustomerT2
DoorT1 fields: DoorID, DoorName
CutomerT2: CustID, CustomerName, DoorName

I suggest you work through this tutorial from RogersAccesLibrary. You have to work through the exercise, but you will learn the concepts of database and table design.
I think your issues are based in design shortcomings.

Good luck with your project.
 

AccessBee

Registered User.
Local time
Today, 00:48
Joined
Jun 22, 2015
Messages
12
Based on this


I suggest you work through this tutorial from RogersAccesLibrary. You have to work through the exercise, but you will learn the concepts of database and table design.
I think your issues are based in design shortcomings.

Good luck with your project.

Thanks jdraw for suggesting and providing the guide. I am excited about programming, especially I have worked with C++, MatLab, HTML and CSS. My studies have been focused on math, physics and engineering, that's why probably I missed Access. But now in a corporate world, I am working with Access to manage a giant database. The more I learn Access’ powerfull data processing, the more I get surprised. I will definitely try to learn from above link ASAP when I am free of my workload a little bit. I guess it will take time since I have to do the exercise as well. BTW, I am never tired of learning, so I will do it.

However, the issue I am facing is not about organizing, it's about Query and Update when I have already done something by mistake. In a query we can say, in columnA (from TableB ) if criteria matches "1" (the ID) update ColumnA to "Wooden Door" (the name associated with ID "1"). I can do this by “Query Design” in Access. So, only rows that have 1's will get changed. I want to change rows with "2"s to its door name as well. But then I have to create and update query for however many different door types I have.... (imagine having 100 DoorID and thus 100 query to make updates)

So, instead I want the criteria to check if a field value matches another field value from a second table. Get some other info from the second table that is related to the original value (i.e. coming from the same row), then update the first table field with that new info.

I really hope I am making sense. I am in this problem because I don't know much about SQL or VBA. It will take way more time for me to learn everything in coding form. That's why I am asking. I believe the solution is pretty easy especially if SQL have something like that.
 
Last edited:

jdraw

Super Moderator
Staff member
Local time
Today, 03:48
Joined
Jan 23, 2006
Messages
15,364
There are a few things in your latest post that are a little confusing.
But now in a corporate world, I am working with Access to manage a giant database.
Really. Few organizations would use Access for corporate data management. I can see its use for groups of users or teams, and/or for technical users to get info via ODBC from corportae databases (Oracle, SQL server, etc)

However, the issue I am facing is not about organizing, it's about Query and Update when I have already done something by mistake.
I think your data structure may be the reason for the issues you are having with queries and update.

Experienced database developers will tell you that getting your tables and relationships designed to meet your business requirements is the single biggest factor in having an efficient, useful shared and manageable database. Too often, you hear --"I'll fix that when I get some time".
 

AccessBee

Registered User.
Local time
Today, 00:48
Joined
Jun 22, 2015
Messages
12
jdraw said:
There are a few things in your latest post that are a little confusing.

Really. Few organizations would use Access for corporate data management. I can see its use for groups of users or teams, and/or for technical users to get info via ODBC from corportae databases (Oracle, SQL server, etc)

Yes, you are right. I am only working for my team, I am not managing the whole company level database (I didn't mean to say that). I am not that important yet! I am barely a college graduate and trying to fix some of the issues in the team. But, the data I am working on is horrifyingly giant, no doubt on that, because it comes from main BD. That's why I am trying to organize things using Access for my team.

jdraw said:
Experienced database developers will tell you that getting your tables and relationships designed to meet your business requirements is the single biggest factor in having an efficient, useful shared and manageable database. Too often, you hear --"I'll fix that when I get some time".
Yes, I completely agree with that. I just need some time to learn everything. Well, if you compare to some of the people here, I almost know nothing of Access. So yes, I will start very soon on this one. Thanks again.
 

Users who are viewing this thread

Top Bottom