Prev: Count number of times an entry for list B shows up in List A (cont
Next: query tables using multiple listed values from another table
From: Jim Murray Jim on 3 Jul 2008 14:58
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;

From: John Spencer on 3 Jul 2008 19:11
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
'====================================================


Jim Murray wrote:
> 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;
>
 | 
Pages: 1
Prev: Count number of times an entry for list B shows up in List A (cont
Next: query tables using multiple listed values from another table