Limit recordset to top 1 per group

Worst part was I didn't learn about it until after the initial build. I
guess it's one of those things they started doing at some point, and it
became part of the system without a deliberate plan.
Thanks again for your interest in the problem and the thought you have
devoted to helping me sort it out.

"Marshall Barton" wrote in message
...
That sure does sound like an odd accounting approach, but I
guess you have it under control. Good luck with the next
problem ;-)
--
Marsh
MVP [MS Access]


BruceM wrote:
A revised PO, unlike a new requisition record, starts with a PO_Number,
but
unlike a "standard" PO it has not been approved. Therefore when the
personnel who do approvals review records that need approval, they need to
see the unapproved revised POs as well as unapproved requisitions
(PO_Number
not yet assigned). Seems a cumbersome accounting approach, but that's how
they do it. The default recordset when the PO form is opened is all
records
that need at least one approval.
I see the logic of doing away with the Nz in the Where condition. I tried
your longer version that tests for equivalency and null, and it seems to
work (although I have not tested thoroughly yet).

"Marshall Barton" wrote
BruceM wrote:

I will take a careful look at the code to be sure I am using Nz only
where
it is needed. It is definitely needed to increment PO_Rev if the first
version of a PO has PO_Rev null and the first revision (second version)
has
it at 1. It is also needed for the comparison I mentioned earlier in
the
thread where I want to include records in which the PO_Number is null:
WHERE Nz(tblPO.PO_Number,0) = Nz(P2.PO_Number,0)


I think I might disagree with using Nz(tblPO.PO_Number,0) =
Nz(P2.PO_Number,0). That kind of looks like you are linking
details to an unapproved PO that has no POnum. If an
unapproved PO can have details, then it seems to me that
they would have to be linked on some other field. The way
you have it, it seems like it would connect all the details
of every unapproved PO to each unapproved PO.

That Nz expression could also be done by using the condition

((tblPO.PO_Number = P2.PO_Number) OR (tblPO.PO_Number Is
Null And P2.PO_Number Is Null))

which may be more cumbersom to write, but at least has a
chance of using index optimizations. But, as I said above I
just don't understand how this accomplishes anything useful.