425,862 Members | 868 Online
Need help? Post your question and get tips & solutions from a community of 425,862 IT Pros & Developers. It's quick & easy.

# Populate field based on comparison with two fields in another table (between?)

 P: 2 I am building a fairly simple database, I have two tables where I need to populate a field based on two values in another table, and return the value in the third field. In excel this would be similar to an IF/Then nested in a VLookup. Scenario: The "Character" table has an "XP" field and a "Level" field When "XP" is between 0 and 499, the "Level" field should be "1", between 500 and 999, the "Level" field should be "2", so on and so forth at differing intervals of XP increase dependent on level. In order to simplify this calculation, and save a loooooooong if/then/else statement, I created a table holding all of the values in three fields "XP Min", "XP Max" and "Level". I need the [Character].[Level] field to look at the [Character].[XP] field, and find out what level is appropriate by finding which record in the [XP Levels] table has the correct [XP Min] and [XP Max] values. I've come up with the following, but it doesn't return the right value unless the XP field is exactly the same as one of the values in the XP Min or XP Max values in XP Levels Table: Expand|Select|Wrap|Line Numbers Iif([Character].[XP] Between [XP Levels].[XP Min] and [XP Levels].[XP Max], [XP Levels].[Level], "") Aug 14 '17 #1

You could try the following (NB. This can be saved as a `QueryDef` in Access but not in Design View.) :
Expand|Select|Wrap|Line Numbers
1. UPDATE [Character]
2.        INNER JOIN
3.        [XP Levels]
4.     ON [Character].[XP] Between [XP Levels].[XP Min] And [XP Levels].[XP Max]
5. SET    [Character].[Level]=[XP Levels].[Level]
Bear in mind this `UPDATE` query will handle all records in the [Character] table. If you need to update a specific record then a `WHERE` clause would follow to stipulate which record to update.

Alternatively, you could update all those not previously set by using a `WHERE` clause of :
Expand|Select|Wrap|Line Numbers
1. WHERE  ([Character].[Level] Is Null)
If you're only updating a single record then a VBA approach may well make good sense.

5 Replies

 Expert 100+ P: 1,430 When yoy say "When "XP" is between 0 and 499, the "Level" field should be "1", between 500 and 999, the "Level" field should be "2", so on and so forth at differing intervals of XP increase dependent on level." what exactly do you mean by "so on and so forth"? If yo mean this is a linear interval of 500 then this is much simpler Expand|Select|Wrap|Line Numbers Level = (XP \ 500) + 1   In fact you don't even need the Level field in the table as it is calculated on the fly. Phil Aug 15 '17 #2

P: 2

Unfortunately, no the XP increase isn't fixed at 500 per level. Every few levels the XP requirement for level up increases and then stays there for a few levels before going up again, but it doesn't go up a fixed amount. I've attached a screenshot of the XP Levels table so you can see the steps up the ladder. I have tried coming up with some sort of fixed formula that takes the increases into account, but I haven't found one, that's why I decided to try to compare the character XP to the XP Levels table to retrieve the level.

Attached Images
 Capture.JPG (38.6 KB, 210 views)
Aug 15 '17 #3

 P: 2 I do have a working SELECT query that will check the fields against the criteria and return the correct level. I finally got this working tonight. SO, now I need help figuring out how to make this update the Level field in the Character table whenever the XP field is updated in that same table. Query in SQL: Expand|Select|Wrap|Line Numbers SELECT [XP Levels].Level, Character.[Character Name], Character.XP FROM [Character], [XP Levels] WHERE ((([Character]![XP]) Between [XP Levels]![XP Min] And [XP Levels]![XP Max])); Aug 15 '17 #4

 Expert 100+ P: 1,430 Simplest way that I can think of is a simple function Expand|Select|Wrap|Line Numbers Function GetLevel(Character As Integer) As Integer       GetLevel = DMax("Level", "TblXPLevel", Character & " > XPmin")   End Function   Note that you don't need XPMax in your table. By the way, I strongly suggest you don't have spaces in field names (and Query Names & Form Names) to avoid having to put square brackets round them Phil Aug 15 '17 #5

 Expert Mod 15k+ P: 31,418 You could try the following (NB. This can be saved as a `QueryDef` in Access but not in Design View.) : Expand|Select|Wrap|Line Numbers UPDATE [Character]        INNER JOIN        [XP Levels]     ON [Character].[XP] Between [XP Levels].[XP Min] And [XP Levels].[XP Max] SET    [Character].[Level]=[XP Levels].[Level] Bear in mind this `UPDATE` query will handle all records in the [Character] table. If you need to update a specific record then a `WHERE` clause would follow to stipulate which record to update. Alternatively, you could update all those not previously set by using a `WHERE` clause of : Expand|Select|Wrap|Line Numbers WHERE  ([Character].[Level] Is Null) If you're only updating a single record then a VBA approach may well make good sense. Aug 19 '17 #6

 Expert Mod 15k+ P: 31,418 @Phil. Your module probably wants to use `DMin()` in those circumstances, rather than `DMax()`. Aug 19 '17 #7