I have a table that needs a field updated on it by referring to another table, but I just can’t get my head around how to do it.
Table 1 has in it a field called “days_age” and another field called “age_debt”. This table has around 6,000 records in it.
Table 2 has 3 fields, field 1 called “age_from”, field 2 called “age_to” and field 3 called “description” This table has 6 records in it.
What I need to do is compare the “days_age” filed in table 1 and see if it falls between the fields “age_from” & “age_to” in table 2. If it finds that “days_age” falls between these 2 values then update the field “age_debt” in table 1 with the relevant value in table 2, field “description”
How much programming do you know? Is this a one time thing? If not, will the values in Table2 change?
I have been doing alot of age range reporting on people. And typically I write a function the defines the Age Range for one person. Example, I pass 33 and I get back '30 - 35'. Then I have a second table that has '30 - 35' and some other data (usually just a sort order). But the way I use it is in one query I will get the range for each person by calling the function and calling it AgeRange in the query. Then another query will take the results of the first query and match them to Table2 to get the sort order.
If you give more details about what is in Table2 and how days_age and age_debt relate, then I can be more specific (help write the function) for your situation.
The table values that I need to return can vary and are amendable/added to by the user.
In table 2 has only 3 fields and an example of their values are
Age_from Age_to Age_debt
0 30 up to one month
31 90 1 to 3 months
91 180 3 to 6 months
181 365 6 to 12 months
In table 1 there are a number of fields but the fields that I am concerned with are
"Days_age" and "Age_debt". What I want to do is for the system to look at table 1 and determine where the value for "Days_age" falls in table 2 and return the corresponding "Age_debt" value from table 2 and update table 1 field "Age_debt".
So in this example if table 1 "Days_age" = 36 then I want an value of "1 to 3 months" returned to up date table 1 field "Age_debt".
If "Days_age" was 190 then the value would be "6 to 12 months"
The easiest sloution is to create function in a module.
Add the following code:
Public Function AgeRange(lngAge As Long) As String
Select Case lngAge
AgeRange = "Unknown"
Case Is < 31
AgeRange = "up to one month"
Case Is < 91
AgeRange = "1 to 3 months"
Case Is < 181
AgeRange = "3 to 6 months"
Case Is < 366
AgeRange = "6 to 12 months"
AgeRange = "More than a year"
Then in an update query for the field Age_debt in Table you can put AgeRange([Days_Age]). Then execute the query.
One caution though, this is considereda calculated field, and as a general rule you should not store calculated fields in tables. You should always calculate them at the time you need them. That way the data is always synchronized.
One thing though, as the descriptor and ranges can change if the user changes them I'm nt sure if this will cater for it?
The main reason that this is being used to update the record is that the field is used in a hell of a lot of places tables/forms/reorts etc and I thought it best to do it once because of the number of records involved.