Ranking query

[BruceM]

I have a query that assigns row numbers to the recordset returned by another
query:

SELECT Insp1.RepairDate, Insp1.DetailID, Insp1.UnitID

(SELECT Count(*)

FROM qryRepairs

AS Insp2

WHERE Insp2.RepairDate <= Insp1.RepairDate

AND (Insp2.DetailID <= Insp1.DetailID

OR Insp2.RepairDate <> Insp1.RepairDate))

AS ListOrder

FROM qryRepairs AS Insp1;



This works up to a point, which is that I want the count to start over when
there is a new I_UnitID value. This is what I get:



ListOrder UnitID RepairDate DetailID

1 29 4/14/2008 42

2 29 4/16/2008 18

3 29 4/16/2008 19

4 30 4/16/2008 39

5 30 4/16/2008 40



However, I want ListOrder to start over at 1 with UnitID 30. This is an
abridged version of the SQL, but the idea is that this shows a repair
history for an individual piece of equipment (UnitID). One table lists
types of equipment (e.g. clamping fixture), and a related table lists
individual equipment items (Fixture 1, Fixture 2, etc.). These are brought
together, along with repair records for individual equipment items, into
qryRepairs. At the form level, the user navigates from one individual
equipment record to another. At each record a subform displays the repairs
that have been performed. For Fixture 1 (UnitID 29) there are three items
on the list, numberd 1, 2, and 3. At the next record (for Fixture 2) there
are two items on the list. They should be numbered 1 and 2, not 4 and 5.



One way to do this, I suppose, is to load the SQL at run time (in the main
form's Current event?) as I move to each record, so that it includes only
one UnitID. I don't know if there is a performance hit by doing it this way
(there could be tens of thousands of records eventually). I have not been
able to discover a way to restart the numbering using SQL.



A related question is whether there are any considerations one way or the
other to using a named query in another query. I could replace the name of
the query with the query's SQL in the example above, if there is a reason
for doing so.

 

[Dale Fye]

Bruce,

Try this. This uses a non equi-join, so you can only complete it in the SQL
view. I generally set it up in the design view with equi-joins (which should
result in all the ListOrders = 1. Then, jump over to the SQL view and modify
the second join, which should get you what you are looking for.

SELECT Count(I2.*) as ListOrder, I1.UnitID, I1.RepairDate, I1.DetailID
FROM qryRepairs as I1
INNER JOIN qryReparis as I2
ON I2.UnitID = I1.UnitID
AND I2.RepairDate <= I1.RepairDate
GROUP BY I1.UnitID, I1.RepairDate, I1.DetailID
ORDER BY I1.UnitID, Count(I2.*)

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[BruceM]

Thanks for the suggestion. As it turned out it did not do what I needed in
that the order became 1, 3, 3, 1. However, it got me going in a different
direction than I had tried before, and I ended up with:

SELECT Count(*) AS ListOrder, I1.UnitID, I1.RepairDate, I1.DetailID
FROM qryRepairs AS I1
INNER JOIN qryRepairs AS I2
ON I2.UnitID = I1.UnitID
WHERE (((I2.DetailID)<= I1.DetailID)
AND ((I2.RepairDate)<= I1.RepairDate))
OR (((I2.RepairDate)<=I1.RepairDate)
AND ((I2.RepairDate)<>I1.RepairDate))
GROUP BY I1.UnitID, I1.RepairDate, I1.DetailID
ORDER BY I1.UnitID, Count(*);

This gives me the desired result, and incidentally it can be viewed in
design view. I'm not exactly sure what you mean by non equi-join, but it is
certainly true that it cannot be viewed in design view. I see that the
inner join in your suggestion has an AND component, and that it is
non-equal, so I expect that's what you mean, but I have to admit I don't see
what it does.

 

[Dale Fye]

Based on your comments, and my reanalysis of your data, I should have joined
on UnitID and DetailID (instead of RepairDate), something like:

ON T2.UnitID = T1.UnitID
AND T2.UnitID <= T1.UnitID

Let me give you an example. Suppose you have a table that contains the
values 1-10, but what you want is a qruery to list for each item in that
table, all of the numbers less than or equal to it. Now you could do this as:

Select T1.intNumber, T2.intNumber
FROM yourTable as T1, yourTable as T2
WHERE T2.IntNumber <= T1.intNumber
ORDER BY T1.IntNumber, T2.intNumber

You could also do this as:

Select T1.intNumber, T2.intNumber
FROM yourTable as T1
LEFT JOIN yourTable as T2
ON T2.intNumber <= T2.intNumber
ORDER BY T1.intNumber, T2.intNumber

Now, if you wanted a count of the number of numbers in your table, that were
less than or equal to a number, you might use:

SELECT T1.intNumber, (SELECT Count(*)
FROM yourTable
WHERE intNumber <= T1.intNumber) as
FreqCount
FROM yourTable as T1

But with this method, you have to run the subquery for each record in T1.
Another way to do this would be:

SELECT T1.intNumber, Count(T2.intNumber) as FreqCount
FROM yourTable as T1
INNER JOIN yourTable as T2
ON T2.intNumber <= T1.intNumber
Group BY T1.intNumber

In my admittedly limited testing, I've found that this latter query
generally runs quicker.

HTH
Dale
--
HTH
Dale

Don''''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[BruceM]

Thanks again for the information. I did some experiments, and saw how the
various queries handle the data. One thing I'm certainly seeing is that
there may be several options for solving any problem. The observation about
the subquery running for each record is something to keep in mind. I expect
that limiting the recordset on which the subquery operates is the way to
keep that from getting bogged down when a subquery is needed. In any case,
I expect that using a named query (as opposed to its SQL) will be OK.

I see that a non equi-join is something such as you demonstrated in your
last example (and elsewhere) in which the join is on something other than
Equal To. I'll have to keep an eye out for that. I can imagine places
where it will be useful.

Thanks again for your input and suggestions.

 

[Dale Fye]

Another place where it is useful is when you want to map a value in one table
to a range of values in another table.

SELECT Students.Name, Students.GradePct, Grades.Grade
FROM Students
LEFT JOIN Grades
ON Students.GradePct >= Grades.MinPct
AND Students.GradePct < Grades.MaxPct

This would give you the letter grade associated with each student.

--
HTH
Dale

Don''''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Jim Murray]

I've gotten this to work for me, but the sequence is reversed. The entry with
the highest # primary key is listed 1st, then down to the oldest. What have I
done wrong?

SELECT FormalCharges.UniqueID, Count(FormalCharges_1.UniqueID) AS Sequence,
FormalCharges.DefendantID
FROM FormalCharges INNER JOIN FormalCharges AS FormalCharges_1 ON
(FormalCharges.DefendantID = FormalCharges_1.DefendantID) AND
(FormalCharges.UniqueID <= FormalCharges_1.UniqueID)
GROUP BY FormalCharges.UniqueID, FormalCharges.DefendantID
HAVING (((FormalCharges.DefendantID)=[Forms]![AddForm]![DefendantID]))
ORDER BY FormalCharges.UniqueID;

 

[John Spencer]

Try changing <= to >=

SELECT FormalCharges.UniqueID, Count(FormalCharges_1.UniqueID) AS Sequence,
FormalCharges.DefendantID
FROM FormalCharges INNER JOIN FormalCharges AS FormalCharges_1 ON
(FormalCharges.DefendantID = FormalCharges_1.DefendantID) AND
(FormalCharges.UniqueID >= FormalCharges_1.UniqueID)
GROUP BY FormalCharges.UniqueID, FormalCharges.DefendantID
HAVING (((FormalCharges.DefendantID)=[Forms]![AddForm]![DefendantID]))
ORDER BY FormalCharges.UniqueID;

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================