Karen,
As long as "last" is determined by the sorting of the data, you can do this
with SQL. If you were to sort by DateVersion DESC, SeqNum DESC, would your
"last" rows always be on top?
If so, here it is in pure SQL, I think:
SELECT * FROM ActionsTable +
WHERE SuppID = .vSuppID +
AND SeqNo = +
(SELECT MAX(SeqNo) +
FROM ActionsTable t2 +
WHERE SuppID = .vSuppID AND DateVersion = +
(SELECT MAX (DateVersion) FROM ActionsTable t3 WHERE t3.SuppID
= .vSuppID)) +
AND DateVersion = +
((SELECT MAX (DateVersion) FROM ActionsTable t4 WHERE t4.SuppID =
.vSuppID))
(But you might want to do it in steps and store the MAX (DateVersion) in a
variable, and then the MAX (SeqNo) for that MaxDateVersion in a variable.)
Bill
On Mon, Jun 1, 2015 at 4:47 PM, Karen Tellef <[email protected]> wrote:
> Trying to think of an easy way to do this. I have many rows per SuppID.
> When a form comes up that shows actions for the supplier, it sorts by
> DateVersion then by SeqNo. There is an autonumber PK field, but it cannot
> be relied on for being the exact order, nor are the SeqNo's sequential in
> this table.
>
> I am designing a report showing supplier information, and for some of the
> data I need to find the LAST action row for each supplier. I would be fine
> with defining a temp table before the report comes up, doing the calc in
> the table, then looking up data from the report.
>
> For example, the actions form would come up like this. I would want to
> grab the PK of the LAST row I'm showing
>
> DateVersion SeqNo
> 03/01/15 1
> 03/15/15 4
> 04/01/15 2
> 04/01/15 3
>
>
> Any clues? Thanks!
>
> Karen
>
>
>
>
