how to select between two values in table

[Jon]

Hi,
I have two tables and one query. In table one there are too many values for
many employees. In table twoâ€category table†there are 3 columns, one for
description of the category, one for “From†which contains % start from 0 and
in the last one is for “To†which contains % start from the last values in
“From†fields. E.g.

Category From To
-excellent 90% 100%
-very good 89.99 % 80%
And so one, I create a query for calculate the performance for 3 years and
it is work. But when I need to join the category with the query dose not work
I need to select the property category for each present in the query.
Can any body help me solving this problem?

 

[Stefan Hoffmann]

hi Jon,

Category From To
-excellent 90% 100%
-very good 89.99 % 80%
And so one, I create a query for calculate the performance for 3 years and
it is work. But when I need to join the category with the query dose not work
I need to select the property category for each present in the query.
Can any body help me solving this problem?

You need an INNER JOIN. Just place your query and the table in the
designer. Switch to SQL mode and change the JOIN condition:

INNER JOIN yourTable
ON query.Performance BETWEEN yourTable.From AND yourTable.To

This will give you the correct results. But be aware: this kind of query
cannot be displayed in the GUI design mode.


mfG
--> stefan <--

 

[Jon]

hi,
thanks a lot, but i need to dispaly them in GUI mode. is there any Solution?
thanks

 

[Stefan Hoffmann]

hi Jon,

thanks a lot, but i need to dispaly them in GUI mode. is there any Solution?

No, the query designer doesn't know this kind of relation ship.


mfG
--> stefan <--

 

[Dale Fye]

Yes, there is an alternative, although it is probably significantly slower.
Rather than an inner join, use a Cartesian join and then restrict the values
in your WHERE clause. Something like:

SELECT Table1.*, tblCategory.Category
FROM Table1, tblCategory
WHERE Table1.Performance BETWEEN tblCategory.From and tblCategory.To

Hope this helps
Dale