Link records from one table with range of numbers from another 1

[VBill57]

Hello,

I've been working on this for awhile, and can't figure out how to do it. I have 2 tables, and I'll simplify it by only including a few fields in each.

tblIssued
Name From To

AA 1 20
BB 40 70
CC 100 125
DD 200 250
__________________________

tblUsed
TransID CodeNum

11 18
12 15
13 123
14 43
15 111
16 66
17 42
18 68
19 57
20 212
21 6

In the tblUsed table, the transID field does not matter - the only field that we need to use is the CodeNum field.

What I would like to do is to combine the 2 tables in a query that can return the count of records for each Name, where CodeNum is within the range between From and To values in the tblIssued table.

For example, these are the counts that would be returned, given the CodeNums:

AA 3 (between 1 and 20)
BB 5 (between 40 and 70)
CC 2 (between 100 and 125)
DD 1 (between 200 and 250)

I'm not sure how to link the 2 tables, because one table has an exact number, and the other includes a range of numbers. I've seen examples of using aliases, and IN clauses, but don't understand those too well. If someone could help point me in the right direction, I would appreciate it.

Thanks in advance!

 

[BNPMike]

You can join tables other than with an equijoin ie you can join where From => Codenum and To =< Codenum. I don't think you can do that in QBE. You have to amend the SQL by hand.

 

[PHV]

What about this ?
SELECT [Name], Count(*)
FROM tblIssued, tblUsed
WHERE [CodeNum] Between [From] And [To]
GROUP BY [Name];

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[VBill57]

Thanks PHV! That's exactly what I needed. I thought the two tables had to be linked somehow first, so I was trying to make it harder than it needed to be. Looks like the group by [Name] was the key.

Thank you, and have a star.