RE: Ranking query
- From: Dale Fye <dale.fye@xxxxxxxxxx>
- Date: Wed, 30 Apr 2008 08:43:00 -0700
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" wrote:
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.
- Follow-Ups:
- Re: Ranking query
- From: BruceM
- Re: Ranking query
- References:
- Ranking query
- From: BruceM
- Ranking query
- Prev by Date: Re: Query Criteria
- Next by Date: Re: Query Criteria
- Previous by thread: Ranking query
- Next by thread: Re: Ranking query
- Index(es):
Relevant Pages
|
|
