How do you find data in a range?

[Rick Tipton]

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 fields - Name, Priority, Sales
Table 2 fields - Priority, Range, Contacts

Objective is based on the Priority and Sales from Table 1 will be used to
lookup in Table 2 the Priority and the maximum of Sales Range on Total Sales
should return a Contacts value.

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

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]));

Thank you for looking at this.

 

[Stefan Hoffmann]

hi Rick,

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 <--