Re: How do you find data in a range?

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

hi Rick,

Rick Tipton wrote:
I have 2 tables. 1st has 2 fields that will be used to look up in another table and retrieve a value in a range.

Table 1 Data - Priority - 2, Sales - $200.00

Table 2 Data - Priority - 2, Range - $0, Contacts - 0
Priority - 2, Range - $150, Contacts - 2
Priority - 2, Range - $500, Contacts - 4
Priority - 2, Range - $1000, Contacts - 8

Should return Contacts = 4
You have an implicit range.

Using an explicit range it would be:

Table2: Data, Prio, RangeFromEx, RangeToInc, Contacts

SELECT T1.*, T2.*
FROM T1
INNER JOIN T2 ON T2.Prio = T1.Prio
AND T2.RangeFromEx > T1.Sales
AND T2.RangeToInc <= T1.Sales


I'm getting all rows above the expected value.
SELECT Table 1.Name, Table 1..Priority, Table 1.Sales, Table 2.Priority, Table 2.Range, Max(Table 2.Range) AS MaxOfRange, Table 2.Contacts
FROM Table 1 INNER JOIN Table 2 ON Table 1.Priority = Table 2.Priority
GROUP BY Table 1.Name, Table 1.Priority, Table 1.Sales, Table 2.Priority, Table 2.Range, Table 2.Contacts
HAVING (((Max(Table 2.Range))>=[Table 1]![Sales]));

SELECT TOP 1 T1.*, T2.*
FROM T1
INNER JOIN T2 ON T2.Prio = T1.Prio
AND T2.Range > T1.Sales
ORDER BY T2.Range


mfG
--> stefan <--

.