finding a range based on a value, from 2 separate queries

[DawnTreader]

Hello All

i have a query that builds a range of values to give me information on what
is kind of servicing is needed in those ranges. i built this based on the
information from this page: http://allenbrowne.com/ser-58.html

my code for that query looks like this:

SELECT RT.IntervalHours AS Minimum, (SELECT MIN(RT1.IntervalHours)
FROM tblRebuildIntervals AS RT1
WHERE RT1.IntervalHours > RT.IntervalHours) AS Maximum, RT.r1000hr,
RT.r5000hr, RT.r10000hr, RT.r15000hr, RT.r20000hr, RT.r25000hr
FROM tblRebuildIntervals AS RT;

the interval hours is the amount of time that the machine in question has
been running, the r1000hr and others are the service interval requirements
for the ranges.

now i have another query that finds the products running hours. that looks
like this:

SELECT tblProductList.ProductID, subtblBlockList.BlockID,
tblProductList.SerialNumber, subtblBlockList.BlockSerial,
Max(subtblRunningHours.RHDate) AS MaxOfRHDate,
Max(subtblRunningHours.HoursAtDate) AS MaxOfHoursAtDate,
tblServiceReps.ServiceRepID, subtblRunningHours.ServiceReportID,
subtblRunningHours.IssueID
FROM tblServiceReps RIGHT JOIN ((tblSiteInformation RIGHT JOIN
(tblProductList RIGHT JOIN subtblBlockList ON tblProductList.ProductID =
subtblBlockList.ProductID) ON tblSiteInformation.SiteID =
tblProductList.SiteID) RIGHT JOIN subtblRunningHours ON
subtblBlockList.BlockID = subtblRunningHours.BlockID) ON
tblServiceReps.ServiceRepID = tblSiteInformation.ServiceRepID
GROUP BY tblProductList.ProductID, subtblBlockList.BlockID,
tblProductList.SerialNumber, subtblBlockList.BlockSerial,
tblServiceReps.ServiceRepID, subtblRunningHours.ServiceReportID,
subtblRunningHours.IssueID, tblProductList.DateDeleted
HAVING (((Max(subtblRunningHours.HoursAtDate)) Is Not Null) AND
((tblProductList.DateDeleted) Is Null))
ORDER BY Max(subtblRunningHours.HoursAtDate) DESC;

the question i have now, how do i take the MaxHoursAtDate and compare it to
the ranges in the first query? there is no key field that i can join the 2
queries on, so how to find what service interval might be required for the
product?

as always any and all help appreciated.

 

[KARL DEWEY]

I can not follow this but it seems like you need ProductID in the first query
so as to join the two queries.

 

[DawnTreader]

Hello Karl

sorry for the confusion. i guess i should have laid out the second query
better.

the problem is there is no relation between the 2 queries. having a product
id in the first query doesnt do anything. the table the query is made on does
not apply to any one product and therefore having a product id in that table
is not going to do anything.

the only thing that "relates" in any way between the 2 tables is the fact
that a product has running hours, and the other table is a "matrix" storing
the different intervals hours for service. so these 2 tables will help me
answer the question "if product 123 has had 1500 running hours, what service
interval should it have?".

for instance the table that the first query is based on looks like this:

HoursID IntervalHours r1000hr r5000hr r10000hr r15000hr r20000hr r25000hr
1 1000 1000 0 0 0 0 0
2 2000 1000 0 0 0 0 0
3 3000 1000 0 0 0 0 0
4 4000 1000 0 0 0 0 0
5 5000 1000 5000 0 0 0 0
6 6000 1000 0 0 0 0 0
7 7000 1000 0 0 0 0 0
8 8000 1000 0 0 0 0 0
9 9000 1000 0 0 0 0 0
10 10000 1000 0 10000 0 0 0
11 11000 1000 0 0 0 0 0
12 12000 1000 0 0 0 0 0
13 13000 1000 0 0 0 0 0
14 14000 1000 0 0 0 0 0
15 15000 1000 0 0 15000 0 0
16 16000 1000 0 0 0 0 0
17 17000 1000 0 0 0 0 0
18 18000 1000 0 0 0 0 0
19 19000 1000 0 0 0 0 0
20 20000 1000 0 0 0 20000 0
21 21000 1000 0 0 0 0 0
22 22000 1000 0 0 0 0 0
23 23000 1000 0 0 0 0 0
24 24000 1000 0 0 0 0 0
25 25000 1000 0 0 0 0 25000
26 26000 1000 0 0 0 0 0
27 27000 1000 0 0 0 0 0
28 28000 1000 0 0 0 0 0
29 29000 1000 0 0 0 0 0

now i made a query that allows me to get a set of hours that gives me the
range for each interval, that comes out looking like this:

Minimum Maximum r1000hr r5000hr r10000hr r15000hr r20000hr r25000hr
1000 2000 1000 0 0 0 0 0
2000 3000 1000 0 0 0 0 0
3000 4000 1000 0 0 0 0 0
4000 5000 1000 0 0 0 0 0
5000 6000 1000 5000 0 0 0 0
6000 7000 1000 0 0 0 0 0
7000 8000 1000 0 0 0 0 0
8000 9000 1000 0 0 0 0 0
9000 10000 1000 0 0 0 0 0
10000 11000 1000 0 10000 0 0 0
11000 12000 1000 0 0 0 0 0
12000 13000 1000 0 0 0 0 0
13000 14000 1000 0 0 0 0 0
14000 15000 1000 0 0 0 0 0
15000 16000 1000 0 0 15000 0 0
16000 17000 1000 0 0 0 0 0
17000 18000 1000 0 0 0 0 0
18000 19000 1000 0 0 0 0 0
19000 20000 1000 0 0 0 0 0
20000 21000 1000 0 0 0 20000 0
21000 22000 1000 0 0 0 0 0
22000 23000 1000 0 0 0 0 0
23000 24000 1000 0 0 0 0 0
24000 25000 1000 0 0 0 0 0
25000 26000 1000 0 0 0 0 25000
26000 27000 1000 0 0 0 0 0
27000 28000 1000 0 0 0 0 0
28000 29000 1000 0 0 0 0 0
29000 1000 0 0 0 0 0

sorry for the wrapping, but i hope this helps understand what the first
query does.

the second query finds all the latest running hour information for each
product, then spits that out along with the serial number and so on. here is
a small example:

ProdID BlockID Serial BSerial MaxDate MaxHours ServRepID
2409 149 08276 08276 02-Feb-09 13658 3
2397 164 09026 09026 09-Oct-08 17917 3
2396 1653 08264 08264 02-Feb-09 15000 3
2279 1651 30524 Unknown 28-Jan-09 182 5
2220 1623 27490-3 28815-3 03-Mar-09 8 8
2219 1622 27490-2 28815-2 03-Mar-09 11 8
2218 1621 27490-1 28815-1 03-Mar-09 5 8
2036 1599 28252 28805 11-Feb-09 0 4
1962 1597 27037-2 27036-2 02-Feb-09 115 5

now if you notice the MaxHours column has a number, i would like to compare
that to the minimum and maximum columns in the other query and see what
interval the product is at. so product id 2396 would return the line:

Minimum Maximum r1000hr r5000hr r10000hr r15000hr r20000hr r25000hr
15000 16000 1000 0 0 15000 0 0

i dont know if that is all clear, but i hope this explains better what i am
looking to do.

any and all...