Query based on multiple column match across tables

[abalonedeveloper]

Howdy,

I am trying to populate a field based on the criteria of other columns. I initially set it up as a nested if calculated field but received the error that the formula was too complicated.

So I moved to option 2...

I created a table with all the possible criteria combinations.

My question is... how do I populate the data on Table 1 in Column A when Table 1 Column B, C, D, and E match Table 2 Column B, C, D, and E. Table 1 and Table 2 do not have a singular "relationship" field. If anything the relationship would be when columns B, C, D and E are the same.

Thanks in advance!

 

[GPGeorge]

The short, correct, answer is that you do NOT do this at all!

You are trying to store calculated values. This is almost never a useful thing to do and can lead to problems with data integrity. Don't do it.

Instead, when you need to DISPLAY this calculation, do it in the interface where displaying calculations is the goal. You can either create a calculated field in a query and display that in a control on the form, or use the control's control source to calculate it.

 

[abalonedeveloper]

The goal is to ultimately export this data and upload to another system. The purpose of Column A in Table 1 is to "label" each record based on the data in columns B, C, D, and E.

For example,

Column A	Column B	Column C	Column D	Column E
Type 1	Yes	Yes	1	Yes
Type 2	No	Yes	3	No
Type 10	No	No	(Null)	No

I am not sure I understand the data integrity issue since it is a match only and column A is a column that is being created once Table 1 and Table 2 are uploaded to the DB.

 

[Uncle Gizmo]

What if you concatenated the fields:- B, C, D, and E into one field in each table, and then linked the Two tables together in a query, only where these two field match?

 

[theDBguy]

Hi. Welcome to AWF!

If you can store the "logic" for the value in Column A, you can then join that table into your other tables to get the value for Column A using a query, without having to store it in a table. You should be able to export a query, rather than a table, for your external system.

 

[Pat Hartman]

Sounds like you are trying to do this backwards. You should be storing in the main table ONLY ColumnA. Having a table to define all the values of the other tables for the ID in ColumnA is the best way to do this. Whenever you need to see the values of the other columns, you would use a join to the lookup table. Keep in mind that you do NOT allow updating to the lookup fields on the main form because that would change the underlying values in the lookup table and therefore change the definition of ColumnA and affect ALL rows where ColumnA is referenced. So, ALWAYS lock the lookup fields and change their tab stop to no.