By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,862 Members | 868 Online
Bytes IT Community
+ Ask a Question
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
  1. Iif([Character].[XP] Between [XP Levels].[XP Min] and [XP Levels].[XP Max], [XP Levels].[Level], "")
Aug 14 '17 #1

✓ answered by NeoPa

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.

Share this Question
Share on Google+
5 Replies


PhilOfWalton
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
  1. Level = (XP \ 500) + 1
  2.  
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
Thanks for the reply Phil!

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
File Type: jpg 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
  1. SELECT [XP Levels].Level, Character.[Character Name], Character.XP
  2. FROM [Character], [XP Levels]
  3. WHERE ((([Character]![XP]) Between [XP Levels]![XP Min] And [XP Levels]![XP Max]));
Aug 15 '17 #4

PhilOfWalton
Expert 100+
P: 1,430
Simplest way that I can think of is a simple function

Expand|Select|Wrap|Line Numbers
  1. Function GetLevel(Character As Integer) As Integer
  2.  
  3.     GetLevel = DMax("Level", "TblXPLevel", Character & " > XPmin")
  4.  
  5. End Function
  6.  
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

NeoPa
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
  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.
Aug 19 '17 #6

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

Post your reply

Sign in to post your reply or Sign up for a free account.