Update queries, lookup tables, and changing foreign key IDs

[tomasm]

I want to change foreign key IDs in a table using an update query and I have hardcoded the ID value in VBA. This feels like the wrong approach so I searched and found this discussion:

stackoverflow.com/questions/1650309/getting-rid-of-hard-coded-values-when-dealing-with-lookup-tables-and-related-bus

This question was asked 14 years ago and I'm curious what the current consensus is on hardcoding IDs in update queries and if Access offers an alternative method (the question was for sql server).

The only solution I can think of for getting rid of hard coded values in Access is to use Dlookup. For the rental service example (UPDATE Items SET State = 1), replace 1 with DLookup("ID", "State", "State = 'Available'").

But for this rental service scenario I might be inclined to delete the ItemState table and change the ItemState field into a text field. If this is for something like a local tool rental company, the total inventory may only be in the hundreds and having an ItemState table may not be necessary. I often think I have normalized my own database too far, and while I notice no speed difference in having multiple lookup tables it is something that lingers in the back of my mind.

 

[plog]

I don't think the state should be stored when it can be calculated.

Presumably there's a transaction table that lists borrowing, returning, breaking etc. You should be able to use logic in a SELECT query on that table to determine the status of every item and not have to UPDATE anything.

Perhaps you can better explain your specific situation and post a screenshot of your Relationship Tool so we can give complete advice.

I often think I have normalized my own database too far

From what we see on this forum, that's a good kind of mistake to make. It means you care about normalization, read about it and tried to implement it. That puts you far ahead of people who just import spreadsheets into Access and build forms and reports on top of that one table and try to solve everything with VBA.

 

[The_Doc_Man]

The usual method assuming there is no barrier or policy against it is to have... for example... the state code in your primary table, and JOIN that table to a state-name translation table so that in the query, you can see BOTH fields. When a translation JOIN is possible, I would say it is a good rule to NEVER use DLOOKUP to get a translation. (Based on speed AND efficiency concerns.)

 

[tomasm]

I also thought there should be a transaction table that records each time an item is borrowed/returned and it seems odd to use an UPDATE query to change a status.

For my database, there isn't a transaction table and it probably wouldn't make sense to have one. I have a series of UPDATE and DELETE queries executed in VBA to modify records in a table called "Budget". I need to modify thousands of records at once so that's why I am using code instead of a form with a combo box. Here's one query to update dollar and number fields based on the value of a foreign key:

UPDATE Budget SET BY4dollars = BY3dollars, BY4positions = BY3positions
WHERE BudgetTypeID = 1;

BudgetType is a lookup table and one field is named "BudgetType". BudgetTypeID 1 on the BudgetType table has BudgetType "Perm". How would I create a JOIN in the UPDATE so I can replace ID 1 with "Perm"? I can join these tables in a SELECT query to see "Perm" based on ID 1, but I can't figure out how to do this in the WHERE clause of my UPDATE query to get ID 1 based on "Perm".

Here's another where I am updating the foreign key:

UPDATE Budget SET BudgetTypeID = 3
WHERE BudgetTypeID = 4 AND CYdollars <> 0 OR BudgetTypeID = 4 AND CYpositions <> 0;

In this case I would need to JOIN on two fields: Type and Years.
BudgetTypeID = 3 has Type "Limited" and Years "0"
BudgetTypeID = 4 has Type "Limited" and Years "1"


I agree with the people on the old Stack Exchange question saying that using the foreign ID is fine because there should be no reason for the ID to change. In my database there is no reason for any of the table IDs to change but I still want to learn how to avoid hardcoding in these queries. If DLookup is only to be avoided for speed and efficiency concerns then I can use DLookup in this database since it is relatively small. But again, I want to learn the right way.

 

[plog]

I have a series of UPDATE and DELETE queries executed in VBA to modify records in a table called "Budget"

Red flag #1: Data shouldn't be moved around in a relational database. It should stay in place and be related if it changes status or type.

...BY4dollars = BY3dollars, BY4positions = BY3positions

Red flag #2: Numerated field names. When you have a fields that are differentiated by numbers (By3..., By4..., By5...etc.), it's a sign you need another table. Data should be accomodated vertically, with more rows, and not horizontally with more fields.

Red flag #3: Groups of fields. When you have a set of fields that share a prefix or suffix (...dollars, ...positions, etc.), that prefix/suffix is actual data that should be stored in a field, not in a field name.

I'm pretty confident you haven't properly normalized your data and this issue you have posted about isn't the one you should be focusing on. Could you post a screenshot of your relationship tool with all tables and fields fully visible? With that we can help you move to a proper structure.

 

[The_Doc_Man]

I'll simply suggest that you do some research into Database Normalization before you go much farther, because the things plog pointed out reveal potential troubles down the road in terms of inefficiencies in storage and added complexity in code.

 

[tomasm]

I understand what you are saying and will need to think more about my table structure. As I said, I feel like I've already normalized too much. This database should be a flat file in my opinion. The Budget table is the main table and is simply a list of about 22k budget lines. It should all be in a single Excel spreadsheet, but I was asked to use Access. There are certainly some benefits to having this data in Access and there will be greater benefits if I can change the process for how new data is added. But it really should be in Excel for simplicity. I'll post my table relationships when I'm back at work. Thanks.

 

[MajP]

I am not sure I am following what you are asking, but maybe this is your question.

When you simply have values without other fields often in my opinion people overdue the lookup tables.
Instead of lots of tables with simply an ID and Value

tblState

StateID	StateValue
1	Unavailable
2	Available

TblColor

ColorID	Color
1	Red
2	Blue

People will do a single "value" lookup table, and simply store the value not the key.

lookupID	Category	LookupValue	Sort
1	State	Unavailable	1
2	State	Available	2
3	Color	Red	2
4	Color	Blue	1

Now you could store in your data table the lookupID if you wanted, or more likely simply store the LookupValue. Or in fact you can do both.

Comboboxes in data table forms simply filter on Category.

Maybe in a huge database you get efficiency storing a numeric ID, versus the actual lookup value. But it probably makes little difference. The Sort field helps in pull downs so if you want to show
Small
Medium
Large
X-Large
in a not alphabetic order you do not have to put them in the table in that order.

 

[The_Doc_Man]

As I said, I feel like I've already normalized too much.

If you have a choice between a JOIN-based query using translation tables or a query that does a DLOOKUP for every field or a query that has to resolve a multi-value field, the fastest will always be the JOIN/translation case followed by the gyrations needed for the Multi-Value field followed in last place by the use of DLOOKUP. To suggest "normalized too much" makes sense if you are doing some kind of data warehousing because there, your tables need to be structured for specific types of search optimization. But for general databases that perform multiple functions for your business, everything should be (as a goal, not a requirement) in 3rd-normal form - for Access. There are higher levels than 3rd-normal form, but if you can at least get that far, you can do a LOT of what you probably want to achieve.

 

[tomasm]

Attached is a screenshot of my Relationships window. One main table with six lookup tables. No parent/child relationships.

Red flag #2: Numerated field names. When you have a fields that are differentiated by numbers (By3..., By4..., By5...etc.), it's a sign you need another table. Data should be accomodated vertically, with more rows, and not horizontally with more fields.

Until recently, I had only one dollars field and one positions field, and an additional field to identify the fiscal year for dollars/positions. I removed the fiscal year field and made the Budget table shorter and wider, not thinking about how that violates basic table design rules. My thought right now is that I want to keep the "bad" design. Changing back to the proper design will add unwanted complexity to my queries and forms. That's my initial reaction. I haven't thought about it thoroughly.

MajP: my question is the same as the question in the Stack Exchange link in post #1 but this may be the wrong question because as others have written, my table design is flawed. If you look at my relationships I have lookup tables that are very wide and short. I may need to make them narrow and long with something like a Category field as have in your third table example.

 

[plog]

Changing back to the proper design will add unwanted complexity to my queries and forms.

Well yeah, after you've built a fourth story onto your house that has a cracked foundation laid to hold only 2 stories, of course fixing the foundation is going to be work. And perhaps you can add load bearing beams in the middle of the kitchen and just inside the front door that only partially block the entrance for now. And if you ever put a bathtub on that fourth floor you can run support cables from the roof to all around the tub to support it.

The whole premise of this thread you started is trying to add unneeded complexity to your database (UPDATE queries, DELETE queries, VBA data manipulation etc). And because of your structure there will be more in the future. I don't think you are past the point of no return with this, in fact I think fixing your structure now is the way to do less work overall on this database in the long term.

Looking at your screenshot, it turns out I was right in my prior post about numerated field names and groups of fields. That data should exist not in field names but as data in fields themselves. You need a new table for all thos PY/BY dollars/position fields. Possibly the same could be said for the ObjectCategory and Program tables.

 

[Pat Hartman]

My thought right now is that I want to keep the "bad" design.

The expert advice is 100% to fix the tables now. Do not pass Go. Do not collect $200.

I also don't understand the question. Yes it is possible to change foreign keys but I don't understand the situation and I don't know why you would have to avoid specifying the from ID and the to ID. The question in StackOverflow talks about using a query to do this because the asker assumed that to be the correct solution. He just didn't know how to do it but none of the answers I read said - don't use a query. Period. And that is the correct answer. And that is why I never use StackOverflow.

The only application I have that I can think of that I ever did this with was an asset management system. A computer was assigned to Sally and she didn't need it anymore so it would be assigned to Joe. On the computer update form was a combo with the AssignedTO ID. To move from Sally to Joe, you picked Joe from the combo and that was it. (behind the scenes, an append query was run because we needed to keep history on who had what, when) Since the components were connected to the computer, they automagically went along with the transfer. However, if you wanted to transfer only one component, you would go to the component update record and there would be a combo that selected a computer. You would change the combo to the new computer. In both of these cases there was a person and a computer for Unassigned so anything not assigned to an actual employee was assigned to inventory and the same for components.

So, given the asset management example, you go to the record you want to reassign and change the assignedTo value. You would never do this with a query but if you did, you must reference (which is not the same as hard-code) both the from ID and TO values. Hard-code means that you have the two IDs in the actual SQL. However, that is ridiculous. You would need to create a new query or modify this one every time you needed to move something. If for some reason you want to do this with a query (bad idea all around), you would have a form with an ID field to reference the record and a TO field and you would have a run button to run the query. The query would reference the ID and TO fields to obtain the values it needed for this move. It would use the ID form field in the Where clause and the TO form field in the set clause.
Both the form fields could be combos so you don't need to type anything.

 

[tomasm]

Yes, I will fix the tables. I agree that it needs to be done.

I understand the StackOverflow question but not for the scenario given in the question. Maybe I understand the question because my ideas about table design are wrong.

It wouldn't make sense to assign equipment to users with an UPDATE query to change IDs because there is a finite number of unique equipment IDs to assign to a finite number of unique users. You would use a combo box for that to update one record at a time, as you said.

But what about this: you have an operating system table and store an OS ID for each computer in the computers table. If all of the HP computers are upgraded from Windows 11 to 12 and you have hundreds of records to update, how would you do it? (For this example let's pretend there's no need for an upgrade history). Would you not use an UPDATE query to change the OS IDs?

 

[Pat Hartman]

Would you not use an UPDATE query to change the OS IDs?

This is a hypothetical so yes you could. In reality, they won't all be updated at exactly the same time so depending on when you run the update, some of the records will be wrong. And maybe some don't actually get updated for whatever reason. If you're OK with that then so am I. In this case you don't have a specific CPU ID but you have a manufacturer. So you select records by type = computer and manf = HP, and update OS to 45. So you need to know THREE values to perform this update and you would NEVER hard code the query. You would always use a data entry form to capture the three values. All three should be combos so you don't need their IDs but you do need the text values.

 

[tomasm]

I'm back after being disabled by the CrowdStrike incident... and I think I may be getting close to an answer to my original question.

The first part of the answer is: don't hard code IDs in queries.
The second part of the answer is: if you are trying to run UPDATE queries to change foreign IDs in a table, that is a sign that your table structure is wrong or/and your process is wrong.

Now going back to my database, even after I correct my table design I still need to run UPDATE and DELETE queries to change thousands of records in the Budget table. Or at least I think I need to because I don't see any other way to accomplish what I need to do.

Instead of running this from a module (with my bad table design):

UPDATE Budget SET BY3dollars = BY4dollars, BY3positions = BY4positions
WHERE BudgetTypeID <> 1;
'BudgetTypeID 1 corresponds to "Permanent" in a table named BudgetType

on a form, select BY3 from a combo box named comboSet and BY4 from a combo box named comboWhere. Each combo box uses a table named FiscalYear as the row source and the FiscalYearID as the bound column. Also select Permanent from a combo box named comboType which uses the BudgetType table as the source. A button runs this from the form module:

UPDATE Budget SET FiscalYearID = Me.comboSet
WHERE FiscalYearID = Me.comboWhere AND BudgetTypeID <> Me.comboType;

Am I on the right track or is this still way off??

The goal is to shift Dollars and Positions by one year. All non-permanent records in the Budget table with the FiscalYearID that corresponds with BY4 (this means next fiscal year +4) need to be updated to the FiscalYearID that corresponds with BY3 (this means next fiscal year +3), then BY3 to BY2 etc. (In the bad table design I am shifting Dollars and Positions values accross fields from right to left. In the better table design I am shifting FiscalYearIDs down accross records.)

This is strictly a budgeting database and will never have any expenditure data or any other accounting related data. And I won't need to see any historical data. If I do, I can open the backup database file that I created prior to running the queries. It will be archived in a different folder from my regular backup files and named differently from my backup files.

 

[Eugene-LS]

UPDATE Budget SET FiscalYearID = Me.comboSet

Take a look to strings bellow please

Spoiler: Form Module Code Example:

' ... in form module - something like this:
Dim sSQLStr As String
    
'01. comboSet check:
    If Me.comboSet.ListIndex = -1 Then 'value is not selected
        MsgBox "...value is not selected ...", vbExclamation
        Me.comboSet.SetFocus
        Me.comboSet.Dropdown ' ??? :)
        Exit Sub
    End If

'02. comboWhere check:
    If Me.comboWhere.ListIndex = -1 Then 'value is not selected
        MsgBox "...value is not selected ...", vbExclamation
        Me.comboWhere.SetFocus
        Exit Sub
    End If

'03. comboType check:
    If Me.comboType.ListIndex = -1 Then 'value is not selected
        MsgBox "...value is not selected ...", vbExclamation
        Me.comboType.SetFocus
        Exit Sub
    End If

'SQL QUERY String :
    sSQLStr = "UPDATE Budget SET FiscalYearID = " & Me.comboSet & vbCrLf & _
              "WHERE ((FiscalYearID = " & Me.comboWhere & ") AND (BudgetTypeID <> " & Me.comboType & "));"
    CurrentDb.Execute sSQLStr 'Executing query

 

[tomasm]

Take a look to strings bellow please

Thanks, I'll write the full code later. I just wanted to provide the SQL for now to get feedback on my plan.
Ideally I won't need to make combo box selections for each query that I run. My example query is just one of several, and I don't want to think about which combo box selections to make for every query. I want to run one procedure and let the code do the work, and I have already done that..... but I'm using hard coded IDs (and poor table design). I'll forget about that for the time being and hope someone will comment on my plan in my previous post.

 

[Pat Hartman]

Now going back to my database, even after I correct my table design I still need to run UPDATE and DELETE queries to change thousands of records in the Budget table. Or at least I think I need to because I don't see any other way to accomplish what I need to do.

I think you are making this harder than it should be. If you want to copy last year's budget, you would NOT be changing the FK's, You would be copying the records from last year and appending them with a new year value. You end up with both 2024 and 2025 forecasts. Then you can modify the 2025 as desired. The copy requires two arguments. The From year and the To year. I suppose you could always assume that the From year is the To year -1 but that is inflexible. For the form, you can open it and set the default from/to value in the form's open event. You can assume
Me.FromYear = Year(Date()) which is this year and you can also assume the ToYear
Me.ToYear = Me.FromYear + 1.

Or, if your forecast is more in the future, adjust the defaults also. Just keep in mind that if you have defined the unique Index correctly, you won't be able to add the same year twice.