Share via

Access Database Question: populate a column with data based on another column

Anonymous
2024-03-25T15:48:27+00:00

Hello

I am new to access and wondering if it is possible to populate a column with data based on other column info for example:

I would like to populate GRAA Class Based on Tax Class and Class. I put the answer in the column labeled Answer.. The criteria is below the table

WHEN CLASS_CODE = '0360' THEN 'A'

WHEN CLASS_CODE = '0350' THEN 'N'

WHEN CLASS_CODE = ('4934','4935','4340','4350') THEN 'PP'

WHEN CLASS_CODE <> to the above THEN TAX CLASS

                                  WHEN '1' THEN 'R'

WHEN '1s' THEN 'S'

WHEN '1m' THEN 'M'

WHEN '2' THEN 'N'

WHEN '2a' THEN 'A'

                                  WHEN '3o' THEN 'C'

                                  WHEN '3e' THEN 'E' 

                                  WHEN '3r' THEN 'I'

                                  WHEN '4r' THEN 'P' 

                                  WHEN '4p' THEN 'PP' 

                                  WHEN '0' THEN 'C'

Microsoft 365 and Office | Access | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

8 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-03-25T22:41:02+00:00

    The referenced table should include all possible distinct combinations of Tax_Class and Class as a composite key of the table, along with Graa_Class as a non-key column.  You can then create an enforced relationship on Tax_Class and Class between the two tables. The current referencing table should not include a Graa_Class column as this can be obtained by joining the tables in a query on the composite keys.  To include a Graa_Class column would introduce a transitive dependency on the key, and the table would not be normalised to Third Normal Form (3NF), and thus open to the risk of update anomalies.

    You might like to take a look at Relationships.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    This little demo file illustrates how relationships are built up across a database to enforce constraints like this.  As its example it uses a simplified version of a medical prescriptions database.  For as simple as possible, but no more so, outline of Normalization take a look at Normalization.zip in the same OneDrive folder.

    Was this answer helpful?

    0 comments
  2. Duane Hookom 26,825 Reputation points Volunteer Moderator
    2024-03-25T21:43:20+00:00

    I would then probably create a small user defined function that accepts class and tax_class as arguments and returns the appropriate new class. You could use some SELECT CASE statements for the logic.

    Was this answer helpful?

    0 comments
  3. Anonymous
    2024-03-25T19:03:53+00:00

    Thanks Scott

    I did create another table and joined it but the issue is I have 2 columns of info to combine into 1. I had no issues doing it for 1 just not sure how to do it for both. I joined the TAX_Class but there is another factor that comes into play for the Column CLASS for example if the property use is 0350 and the it would get a new class of N, no matter what the TAX class is.

    TAX_CLASS New Class
    1 R
    1s S
    3o C
    1m M
    4r P
    2 N
    2a A
    3e E
    4p PP
    0 C
    3r I
    9 C

    .

    Was this answer helpful?

    0 comments
  4. ScottGem 68,810 Reputation points Volunteer Moderator
    2024-03-25T18:40:47+00:00

    As a general rule we do NOT store calculated values. As Duane suggested, you can create a table that you can match against to pull the reference value in a query.

    Was this answer helpful?

    0 comments
  5. Duane Hookom 26,825 Reputation points Volunteer Moderator
    2024-03-25T16:33:36+00:00

    The typical solution is to create a small reference table of the class codes and the “answer”. The class code field would be unique.

    If that doesn’t work for you, consider creating a small user defined function that returns the answer.

    Was this answer helpful?

    0 comments