Query with a Tabe with a Range of Values

[wcrimi]

I have a table with 3 critical pieces of information.

Class
Grade
Purse

The Purse value can contain a large number of possible values.

I will create a second table with Class, Grade a "Purse Range", and a PAR Value for that "Purse Range".

For example in Table 1: Class, Grade, Purse

STK.....1.....750k
STK.....1.....500k
STK.....1.....900k
STK.....1.....250k
STK.....2.....150k
STK.....2.....200k
STK.....3.....100K


Table 2: Class, Grade, Purse Minimum, Purse Maximum, Par Value

STK.....1.....600k.....999k.....110
STK.....1.....500k.....599k.....109
STK.....1.....400k.....499k.....108


The first record in table 1 has a purse of 750k. That falls between 600k and 999k. I want the query to return 110.

The second record in table 1 has a purse of 500k. That falls between 500k and 599k. I want the query to return 109.



The third record in table 1 has a purse value of 900k. That falls between 600k and 999k. I want the query to return 110.

etc....

Thanks

[JoeM]

The SQL Code of your Query would look something like this:

SELECT Table1.Class, Table1.Grade, Table1.Purse, Table2.[Par Value]
FROM Table1 INNER JOIN Table2 ON (Table1.Grade = Table2.Grade) AND (Table1.Class = Table2.Class)
WHERE (((Table1.Purse) Between [Table2].[Purse Minimum] And [Table2].[Purse Maximum]));

[JoeM]

I should also clarify, all your "Purse" fields will need to be numeric for this to work. If they are text fields, it will not work.

[wcrimi]

Thanks Joe. I'll give that a try this evening. Thanks so much for the help.

[wcrimi]

The SQL Code of your Query would look something like this:

SELECT Table1.Class, Table1.Grade, Table1.Purse, Table2.[Par Value]
FROM Table1 INNER JOIN Table2 ON (Table1.Grade = Table2.Grade) AND (Table1.Class = Table2.Class)
WHERE (((Table1.Purse) Between [Table2].[Purse Minimum] And [Table2].[Purse Maximum]));

I'm having a problem with this.

As coded, I was getting some missing operator and syntax errors on the "Where" part of this. I played around with it and eventually got it to work perfectly. Unfortunately, I made some changes to other queries that fed this one and somehow it destroyed this query. I've tried to recreate it, but it won't work. I must have stumbled onto the right syntax for the statement initially because I've been unable to get it to work. I've spent a couple of hours on that single statement. HELP!

[wcrimi]

Found it. I made an extra copy of the syntax. Thanks