Results 1 to 7 of 7

Thread: using "between"

  1. #1
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246

    Unanswered: using "between"

    Hi,
    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”

    Anyone any ideas?

    Regards
    John
    Last edited by Sticker; 06-09-04 at 12:21.

  2. #2
    Join Date
    Apr 2004
    Location
    metro Detroit
    Posts
    634
    Do you have a link between theese tables or is table 2 a reference table?

  3. #3
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Quote Originally Posted by rogue
    Do you have a link between theese tables or is table 2 a reference table?

    No link, the 2nd table is only a reference table

    regards
    John

  4. #4
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    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.

  5. #5
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi,
    Thanks for the reply.

    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"

    Regards
    John

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    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

    Case 0
    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"
    Case esle
    AgeRange = "More than a year"
    End Select

    End Function

    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.

  7. #7
    Join Date
    Feb 2004
    Location
    UK
    Posts
    246
    Hi thanks for the reply I shall give it a go.

    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.

    Regards
    John

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •