Limit recordset to top 1 per group

  • Thread starter Thread starter BruceM
  • Start date Start date
B

BruceM

This should be basic enough, but I can't figure it out. I have a Purchase
Order database of typical design that includes a Purchase Order table
(tblPO) and a Line Items table (tblLines), with one PO >> many line items.
In some cases a PO may be revised. The defualt value in this field (PO_Rev)
is 0. When a revised PO is created, the value is 1, then 2, etc.; it never
gets beyond maybe 3 revisions.

I want the recordset to include only the highest PO_Rev for each PO. There
is a wrinkle (maybe) in that when an order is created it is a requisition.
ReqID is the PK for the table. The PO Number (PO_Number) is created only
after the PO has been approved (approval field not shown). Until then it is
null (but could be 0, if that helps). Sample raw data:

ReqID PO_Number PO_Rev
10 1245 0
12 1247 0
13 1247 1
15 0
16 0

Desired recordset:

ReqID PO_Number PO_Rev
10 1245 0
13 1247 1
15 0
16 0

This is the SQL with subquery I attempted, but it returns the entire
recordset:

SELECT tblPO.ReqID, tblPO.PO_Number, tblPO.PO_Rev,
(SELECT TOP 1 P2.PO_Rev
FROM tblPO AS P2
WHERE tblPO.PO_Number = P2.PO_Number
ORDER BY P2.PO_Rev DESC) AS OnePO
FROM tblPO;

However, even if I can get it to work, it seems the recordset is not
editable, so maybe this is not the correct course after all.

It could be I need to add a Boolean field to tblPO for CurrentRev. When a
new revision is created, the previous revision has CurrentRev marked as
False. I could then use True in CurrentRev as the criteria in most cases.
Actually, I wouls use Allen Browne's substitute Yes/No field instead of a
Boolean, but the idea is the same in any case. However, since the recordset
seems to contain all the data I need it seems adding this extra field should
not be necessary, but I haven't figured out the alternative yet.

Another thing I tried was grouping:

SELECT Max(PO_Rev) as MaxRev
FROM tblPO
GROUP BY Nz(PO_Number,ReqID)

This returns the right number of records. The trouble is I am unsure how to
add other fields to a totals query. In order to join this query to tblPO I
added ReqID to the SELECT part of the query, but since it is a grouping
query I had to do something with the field, so I tried Sum, and ended up
with this when I joined to tblPO:

SELECT tblPO.*
FROM tblPO
RIGHT JOIN qryOnePO
ON tblPO.ReqID = qryOnePO.SumOfReqID;

This works, but I am concerned about the use of Sum.
 
I think you were very close. All you really need is to move the
subquery to the where clause and account for null values in PO-Rev

SELECT tblPO.ReqID, tblPO.PO_Number, tblPO.PO_Rev,
FROM tblPO
WHERE NZ(PO_Rev,0) in
(SELECT TOP 1 NZ(P2.PO_Rev,0)
FROM tblPO AS P2
WHERE tblPO.PO_Number = P2.PO_Number
ORDER BY P2.PO_Rev DESC)

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
BruceM said:
This should be basic enough, but I can't figure it out. I have a Purchase
Order database of typical design that includes a Purchase Order table
(tblPO) and a Line Items table (tblLines), with one PO >> many line items.
In some cases a PO may be revised. The defualt value in this field (PO_Rev)
is 0. When a revised PO is created, the value is 1, then 2, etc.; it never
gets beyond maybe 3 revisions.

I want the recordset to include only the highest PO_Rev for each PO. There
is a wrinkle (maybe) in that when an order is created it is a requisition.
ReqID is the PK for the table. The PO Number (PO_Number) is created only
after the PO has been approved (approval field not shown). Until then it is
null (but could be 0, if that helps). Sample raw data:

ReqID PO_Number PO_Rev
10 1245 0
12 1247 0
13 1247 1
15 0
16 0

Desired recordset:

ReqID PO_Number PO_Rev
10 1245 0
13 1247 1
15 0
16 0

This is the SQL with subquery I attempted, but it returns the entire
recordset:

SELECT tblPO.ReqID, tblPO.PO_Number, tblPO.PO_Rev,
(SELECT TOP 1 P2.PO_Rev
FROM tblPO AS P2
WHERE tblPO.PO_Number = P2.PO_Number
ORDER BY P2.PO_Rev DESC) AS OnePO
FROM tblPO;
Click to expand...
[snip]

Try something more like:

SELECT tblPO.ReqID, tblPO.PO_Number, tblPO.PO_Rev
FROM tblPO
WHERE tblPO.PO_Rev = (SELECT Max(P2.PO_Rev)
FROM tblPO AS P2
WHERE tblPO.PO_Number = P2.PO_Number)
 
Thanks very much to both for the replies. Both suggestions work. I see
where I missed the mark. Now I need to figure out if there are advantages
one way or the other to using TOP 1 or Max. The one that uses Max does not
need an ORDER BY in the subquery, so it is a little shorter, but with my
limited recordset so far I do not see a difference in speed one way or the
other. In both cases I needed to modify the WHERE to allow for nulls in
PO_Number:
WHERE Nz(tblPO.PO_Number,0) = Nz(P2.PO_Number,0)

I used null instead of 0 for the default value of PO_Number before the
number is assigned. The default for PO_Rev. is 0. I could not say exactly
why I made those choices, except maybe that I thought the coding would be
simpler. I have a unique index on the combination of PO_Number and PO_Rev.
This seems to work if one of the values is null (the Null,0 combination is
allowed for any number of records), but of course it does not if both are 0
and there is more than one record for which the PO_Number has not been
created. I test for Null in PO_Number at various places in the code. I
could change that to testing for 0 if I also change the index to non-unique,
or if I use 0 as the default for PO_Number and null as the default for
PO_Rev (the opposite of what I have now).

If there is a good reason for doing so I could use null or 0 as the default
for PO_Number and/or PO_Rev in a different way than I am now. I would need
to change some of the code to test for 0 rather than Null or vice versa, but
that wouldn't be a big deal. My guess is that it doesn't much matter.

Marshall Barton said:
BruceM said:
This should be basic enough, but I can't figure it out. I have a Purchase
Order database of typical design that includes a Purchase Order table
(tblPO) and a Line Items table (tblLines), with one PO >> many line items.
In some cases a PO may be revised. The defualt value in this field
(PO_Rev)
is 0. When a revised PO is created, the value is 1, then 2, etc.; it
never
gets beyond maybe 3 revisions.

I want the recordset to include only the highest PO_Rev for each PO.
There
is a wrinkle (maybe) in that when an order is created it is a requisition.
ReqID is the PK for the table. The PO Number (PO_Number) is created only
after the PO has been approved (approval field not shown). Until then it
is
null (but could be 0, if that helps). Sample raw data:

ReqID PO_Number PO_Rev
10 1245 0
12 1247 0
13 1247 1
15 0
16 0

Desired recordset:

ReqID PO_Number PO_Rev
10 1245 0
13 1247 1
15 0
16 0

This is the SQL with subquery I attempted, but it returns the entire
recordset:

SELECT tblPO.ReqID, tblPO.PO_Number, tblPO.PO_Rev,
(SELECT TOP 1 P2.PO_Rev
FROM tblPO AS P2
WHERE tblPO.PO_Number = P2.PO_Number
ORDER BY P2.PO_Rev DESC) AS OnePO
FROM tblPO;
Click to expand...
[snip]

Try something more like:

SELECT tblPO.ReqID, tblPO.PO_Number, tblPO.PO_Rev
FROM tblPO
WHERE tblPO.PO_Rev = (SELECT Max(P2.PO_Rev)
FROM tblPO AS P2
WHERE tblPO.PO_Number = P2.PO_Number)
Click to expand...
 
BruceM said:
Thanks very much to both for the replies. Both suggestions work. I see
where I missed the mark. Now I need to figure out if there are advantages
one way or the other to using TOP 1 or Max. The one that uses Max does not
need an ORDER BY in the subquery, so it is a little shorter, but with my
limited recordset so far I do not see a difference in speed one way or the
other. In both cases I needed to modify the WHERE to allow for nulls in
PO_Number:
WHERE Nz(tblPO.PO_Number,0) = Nz(P2.PO_Number,0)

I used null instead of 0 for the default value of PO_Number before the
number is assigned. The default for PO_Rev. is 0. I could not say exactly
why I made those choices, except maybe that I thought the coding would be
simpler. I have a unique index on the combination of PO_Number and PO_Rev.
This seems to work if one of the values is null (the Null,0 combination is
allowed for any number of records), but of course it does not if both are 0
and there is more than one record for which the PO_Number has not been
created. I test for Null in PO_Number at various places in the code. I
could change that to testing for 0 if I also change the index to non-unique,
or if I use 0 as the default for PO_Number and null as the default for
PO_Rev (the opposite of what I have now).

If there is a good reason for doing so I could use null or 0 as the default
for PO_Number and/or PO_Rev in a different way than I am now. I would need
to change some of the code to test for 0 rather than Null or vice versa, but
that wouldn't be a big deal. My guess is that it doesn't much matter.
Click to expand...


The big difference between TOP and Max is that Top can
return more than one record when there is a tie. Also, I'm
pretty sure that TOP sorts the entire set where Max can take
advantage of an index to just pull out the value.

I don't understand your new question. How can records in
the two tables be related when the linking field is Null???
Seems to me that those records should just be filtered out
by using a criteria like:
Is Not Null
 
Thanks for clarifying the difference between Top and Max. There should be
no ties, so the one that takes advantage of the index seems to make the most
sense.

PO_Number and PO_Rev are both in the main PO table. The reason there is not
a related table for the rev is that any of the information could change
between the initial PO and the revision. It will have a different date in
almost all cases. Other things could change such as shipping method,
accounting code, and so forth. The line items will almost surely change,
too. I wrestled with this late-breaking requirement for a while before
deciding that the revision would be a whole new PO record. The only
alternative I could see would be to have PO_Number and SupplierID (the only
guaranteed unchanging information) as the main record, with all other
information in a related record (the Revison record). Line items would need
to be related to the revision rather than to the main record. Had I known
of the requirement sooner I might have taken that design approach, but I
opted against reworking the database to such an extent on the eve of its
initial deployment. On the other hand, there would be some amount of
duplication from one Revision to another (and no way to predict exactly what
fields would be duplicated), so it wouild be quite dificult to avoid
redundancy in any case.

The default value for PO_Number is not specifically set, and therefore (as I
understand) it is Null. The default value for PO_Rev is 0. I created a
unique index on the combination of PO_Number and PO_Rev. As I mentioned,
the PO_Number is created after the requisition has been approved, so there
could be several records in which PO_Number is Null and PO_Rev is 0. The
unique index seems to allow this combination.

As I mentioned, I found I had to use Nz in the query's Where condition to
include records for which the PO_Number has not yet been created. I
wondered if there would be any advantage to setting the default value to 0
for PO_Number (and updating current records from Null to 0) to avoid needing
to use Nz in the Where condition. However, if I do that I cannot use the
unique index on the combination of PO_Number and PO_Rev. The unique index
will not come into play very much for searches and filtering that I can see,
so its main purpose is to enforce a unique constraint on the combination of
the two fields. Another option would be to use 0 as the default for
PO_Number, null for PO_Rev, and keep the unique index.

The question comes down to this: Is there an advantage or disadvantage to
any of the following options:
1) Leave things as they are (PO_Number Null, PO_Rev 0, unique index on the
two
2) PO_Number 0, PO_Rev null, unique index on the two
3) PO_Number 0, PO_Rev 0, no unique index
 
I still don't understand. How can a rev num to a PO make
sense if there can be multiple POs without a PO num???

Whatever, I don't see how a default value of 0 for an
unknown PO num can do anything but get in the way, The Nz
function allows you to use a zero anywhere in your query if
that's really useful, but I just don't see it.

Maybe the Requsition num is the real key and the PO num is
just an afterthought. But, that doesn't make sense if a Req
num can have multiple PO nums some or all of which are not
specified and any of them can have revisions.
 
Every record has a Requisition number (ReqID). It is the PK. When the
requisition is approved it becomes a PO and is assigned a PO number. Not
every requisition is approved. Each record has one ReqID, one PO_Number,
and one PO_Rev. It's not that PO_Number is an afterthought, but rather the
way we do things means that every purchase request is a requisition that may
or may not become a PO. I don't know how else I could have done about that.

One Req can have one PO. However, the same PO number could appear in two
records (Rev. 0 and Rev. 1).

PO_Number does not have a default value; therefore it is null before a
PO_Number is created, as I understand. For reasons that I do not recall I
made 0 the default for PO_Rev. Perhaps it would have been better not to
have a default value, but then I suppose I would have needed to use Nz
before I could find the Max value for the field.

I had to use Nz in this part of your suggested SQL:
WHERE tblPO.PO_Number = P2.PO_Number

This became:
WHERE Nz(tblPO.PO_Number,0) = Nz(P2.PO_Number,0)

When I didn't use Nz the records without a PO_Number were excluded from the
recordset. I wondered if the Nz function would bog things down as compared
to the value being 0.

Marshall Barton said:
I still don't understand. How can a rev num to a PO make
sense if there can be multiple POs without a PO num???

Whatever, I don't see how a default value of 0 for an
unknown PO num can do anything but get in the way, The Nz
function allows you to use a zero anywhere in your query if
that's really useful, but I just don't see it.

Maybe the Requsition num is the real key and the PO num is
just an afterthought. But, that doesn't make sense if a Req
num can have multiple PO nums some or all of which are not
specified and any of them can have revisions.
--
Marsh
MVP [MS Access]

PO_Number and PO_Rev are both in the main PO table. The reason there is
not
a related table for the rev is that any of the information could change
between the initial PO and the revision. It will have a different date in
almost all cases. Other things could change such as shipping method,
accounting code, and so forth. The line items will almost surely change,
too. I wrestled with this late-breaking requirement for a while before
deciding that the revision would be a whole new PO record. The only
alternative I could see would be to have PO_Number and SupplierID (the
only
guaranteed unchanging information) as the main record, with all other
information in a related record (the Revison record). Line items would
need
to be related to the revision rather than to the main record. Had I known
of the requirement sooner I might have taken that design approach, but I
opted against reworking the database to such an extent on the eve of its
initial deployment. On the other hand, there would be some amount of
duplication from one Revision to another (and no way to predict exactly
what
fields would be duplicated), so it wouild be quite dificult to avoid
redundancy in any case.

The default value for PO_Number is not specifically set, and therefore (as
I
understand) it is Null. The default value for PO_Rev is 0. I created a
unique index on the combination of PO_Number and PO_Rev. As I mentioned,
the PO_Number is created after the requisition has been approved, so there
could be several records in which PO_Number is Null and PO_Rev is 0. The
unique index seems to allow this combination.

As I mentioned, I found I had to use Nz in the query's Where condition to
include records for which the PO_Number has not yet been created. I
wondered if there would be any advantage to setting the default value to 0
for PO_Number (and updating current records from Null to 0) to avoid
needing
to use Nz in the Where condition. However, if I do that I cannot use the
unique index on the combination of PO_Number and PO_Rev. The unique index
will not come into play very much for searches and filtering that I can
see,
so its main purpose is to enforce a unique constraint on the combination
of
the two fields. Another option would be to use 0 as the default for
PO_Number, null for PO_Rev, and keep the unique index.

The question comes down to this: Is there an advantage or disadvantage to
any of the following options:
1) Leave things as they are (PO_Number Null, PO_Rev 0, unique index on the
two
2) PO_Number 0, PO_Rev null, unique index on the two
3) PO_Number 0, PO_Rev 0, no unique index


"Marshall Barton" wrote
Click to expand...
Click to expand...
 
In your original example the only requisition that had a
revision also had a PO number. I that case you do not want
to include reqs that have a Null PO num.

Still in that example, there is no way to determine if
requisitions 16 a revision of 15 or if they are two
independent reqs. Hence my statement that a Null (or 0) PO
num field maens that the record should be excluded.
--
Marsh
MVP [MS Access]

Every record has a Requisition number (ReqID). It is the PK. When the
requisition is approved it becomes a PO and is assigned a PO number. Not
every requisition is approved. Each record has one ReqID, one PO_Number,
and one PO_Rev. It's not that PO_Number is an afterthought, but rather the
way we do things means that every purchase request is a requisition that may
or may not become a PO. I don't know how else I could have done about that.

One Req can have one PO. However, the same PO number could appear in two
records (Rev. 0 and Rev. 1).

PO_Number does not have a default value; therefore it is null before a
PO_Number is created, as I understand. For reasons that I do not recall I
made 0 the default for PO_Rev. Perhaps it would have been better not to
have a default value, but then I suppose I would have needed to use Nz
before I could find the Max value for the field.

I had to use Nz in this part of your suggested SQL:
WHERE tblPO.PO_Number = P2.PO_Number

This became:
WHERE Nz(tblPO.PO_Number,0) = Nz(P2.PO_Number,0)

When I didn't use Nz the records without a PO_Number were excluded from the
recordset. I wondered if the Nz function would bog things down as compared
to the value being 0.

Marshall Barton said:
I still don't understand. How can a rev num to a PO make
sense if there can be multiple POs without a PO num???

Whatever, I don't see how a default value of 0 for an
unknown PO num can do anything but get in the way, The Nz
function allows you to use a zero anywhere in your query if
that's really useful, but I just don't see it.

Maybe the Requsition num is the real key and the PO num is
just an afterthought. But, that doesn't make sense if a Req
num can have multiple PO nums some or all of which are not
specified and any of them can have revisions.
--
Marsh
MVP [MS Access]

PO_Number and PO_Rev are both in the main PO table. The reason there is
not
a related table for the rev is that any of the information could change
between the initial PO and the revision. It will have a different date in
almost all cases. Other things could change such as shipping method,
accounting code, and so forth. The line items will almost surely change,
too. I wrestled with this late-breaking requirement for a while before
deciding that the revision would be a whole new PO record. The only
alternative I could see would be to have PO_Number and SupplierID (the
only
guaranteed unchanging information) as the main record, with all other
information in a related record (the Revison record). Line items would
need
to be related to the revision rather than to the main record. Had I known
of the requirement sooner I might have taken that design approach, but I
opted against reworking the database to such an extent on the eve of its
initial deployment. On the other hand, there would be some amount of
duplication from one Revision to another (and no way to predict exactly
what
fields would be duplicated), so it wouild be quite dificult to avoid
redundancy in any case.

The default value for PO_Number is not specifically set, and therefore (as
I
understand) it is Null. The default value for PO_Rev is 0. I created a
unique index on the combination of PO_Number and PO_Rev. As I mentioned,
the PO_Number is created after the requisition has been approved, so there
could be several records in which PO_Number is Null and PO_Rev is 0. The
unique index seems to allow this combination.

As I mentioned, I found I had to use Nz in the query's Where condition to
include records for which the PO_Number has not yet been created. I
wondered if there would be any advantage to setting the default value to 0
for PO_Number (and updating current records from Null to 0) to avoid
needing
to use Nz in the Where condition. However, if I do that I cannot use the
unique index on the combination of PO_Number and PO_Rev. The unique index
will not come into play very much for searches and filtering that I can
see,
so its main purpose is to enforce a unique constraint on the combination
of
the two fields. Another option would be to use 0 as the default for
PO_Number, null for PO_Rev, and keep the unique index.

The question comes down to this: Is there an advantage or disadvantage to
any of the following options:
1) Leave things as they are (PO_Number Null, PO_Rev 0, unique index on the
two
2) PO_Number 0, PO_Rev null, unique index on the two
3) PO_Number 0, PO_Rev 0, no unique index


BruceM wrote:
Thanks very much to both for the replies. Both suggestions work. I see
where I missed the mark. Now I need to figure out if there are
advantages
one way or the other to using TOP 1 or Max. The one that uses Max does
not
need an ORDER BY in the subquery, so it is a little shorter, but with my
limited recordset so far I do not see a difference in speed one way or
the
other. In both cases I needed to modify the WHERE to allow for nulls in
PO_Number:
WHERE Nz(tblPO.PO_Number,0) = Nz(P2.PO_Number,0)

I used null instead of 0 for the default value of PO_Number before the
number is assigned. The default for PO_Rev. is 0. I could not say
exactly
why I made those choices, except maybe that I thought the coding would
be
simpler. I have a unique index on the combination of PO_Number and
PO_Rev.
This seems to work if one of the values is null (the Null,0 combination
is
allowed for any number of records), but of course it does not if both
are
0
and there is more than one record for which the PO_Number has not been
created. I test for Null in PO_Number at various places in the code. I
could change that to testing for 0 if I also change the index to
non-unique,
or if I use 0 as the default for PO_Number and null as the default for
PO_Rev (the opposite of what I have now).

If there is a good reason for doing so I could use null or 0 as the
default
for PO_Number and/or PO_Rev in a different way than I am now. I would
need
to change some of the code to test for 0 rather than Null or vice versa,
but
that wouldn't be a big deal. My guess is that it doesn't much matter.


The big difference between TOP and Max is that Top can
return more than one record when there is a tie. Also, I'm
pretty sure that TOP sorts the entire set where Max can take
advantage of an index to just pull out the value.

I don't understand your new question. How can records in
the two tables be related when the linking field is Null???
Seems to me that those records should just be filtered out
by using a criteria like:
Is Not Null
Click to expand...
Click to expand...
Click to expand...
 
I sincerely appreciate the time and effort you have put into looking at this
problem, from your initial suggestion through your follow-up comments.

I clearly am unable to express what is going on. I will just say that the
users will need to look at the data in many different ways. For the people
who approve the POs, they just need to see POs that are not approved (and
therefore do not have a PO_Number). The default recordset when the database
is opened is just those records. People who administer the system on an
accounting level need the ability to look at (for instance) POs for which
not all line items have been received, or all POs within a date range.
Users may need to look at previous POs, which they will use as the basis for
new ones.

In almost all cases there is no need to see POs other than the highest
revision. However, there may be a need to review the revision history of a
PO (rarely more than one or two revisions). For this and other reasons I
need a lot of flexibility in building the recordset.

In most cases the user will need to see a limited recordset, so I load a
minimum recordset when the form is opened rather than loading the full
recordset and filtering it. If the user needs a different recordset they
select parameters from combo boxes or type parameters into text boxes (for
dates). The contents of the various text boxes and combo boxes is used to
build a new recordset.

My question was how to limit the records to just the highest revision level
for a particular PO number. You have answered that question. I'm sure some
of my design choices are subject to debate, but in any case the recordset is
subject to change. In some cases a null PO_Number field should be excluded,
but in other cases the recordset needs to include those records. A revised
PO is different from a standard PO in that with a revision the PO_Number is
created before it is approved. Not my call, but that's the way it is.

Marshall Barton said:
In your original example the only requisition that had a
revision also had a PO number. I that case you do not want
to include reqs that have a Null PO num.

Still in that example, there is no way to determine if
requisitions 16 a revision of 15 or if they are two
independent reqs. Hence my statement that a Null (or 0) PO
num field maens that the record should be excluded.
--
Marsh
MVP [MS Access]

Every record has a Requisition number (ReqID). It is the PK. When the
requisition is approved it becomes a PO and is assigned a PO number. Not
every requisition is approved. Each record has one ReqID, one PO_Number,
and one PO_Rev. It's not that PO_Number is an afterthought, but rather
the
way we do things means that every purchase request is a requisition that
may
or may not become a PO. I don't know how else I could have done about
that.

One Req can have one PO. However, the same PO number could appear in two
records (Rev. 0 and Rev. 1).

PO_Number does not have a default value; therefore it is null before a
PO_Number is created, as I understand. For reasons that I do not recall I
made 0 the default for PO_Rev. Perhaps it would have been better not to
have a default value, but then I suppose I would have needed to use Nz
before I could find the Max value for the field.

I had to use Nz in this part of your suggested SQL:
WHERE tblPO.PO_Number = P2.PO_Number

This became:
WHERE Nz(tblPO.PO_Number,0) = Nz(P2.PO_Number,0)

When I didn't use Nz the records without a PO_Number were excluded from
the
recordset. I wondered if the Nz function would bog things down as
compared
to the value being 0.

Marshall Barton said:
I still don't understand. How can a rev num to a PO make
sense if there can be multiple POs without a PO num???

Whatever, I don't see how a default value of 0 for an
unknown PO num can do anything but get in the way, The Nz
function allows you to use a zero anywhere in your query if
that's really useful, but I just don't see it.

Maybe the Requsition num is the real key and the PO num is
just an afterthought. But, that doesn't make sense if a Req
num can have multiple PO nums some or all of which are not
specified and any of them can have revisions.
--
Marsh
MVP [MS Access]


BruceM wrote:
PO_Number and PO_Rev are both in the main PO table. The reason there is
not
a related table for the rev is that any of the information could change
between the initial PO and the revision. It will have a different date
in
almost all cases. Other things could change such as shipping method,
accounting code, and so forth. The line items will almost surely
change,
too. I wrestled with this late-breaking requirement for a while before
deciding that the revision would be a whole new PO record. The only
alternative I could see would be to have PO_Number and SupplierID (the
only
guaranteed unchanging information) as the main record, with all other
information in a related record (the Revison record). Line items would
need
to be related to the revision rather than to the main record. Had I
known
of the requirement sooner I might have taken that design approach, but I
opted against reworking the database to such an extent on the eve of its
initial deployment. On the other hand, there would be some amount of
duplication from one Revision to another (and no way to predict exactly
what
fields would be duplicated), so it wouild be quite dificult to avoid
redundancy in any case.

The default value for PO_Number is not specifically set, and therefore
(as
I
understand) it is Null. The default value for PO_Rev is 0. I created a
unique index on the combination of PO_Number and PO_Rev. As I
mentioned,
the PO_Number is created after the requisition has been approved, so
there
could be several records in which PO_Number is Null and PO_Rev is 0.
The
unique index seems to allow this combination.

As I mentioned, I found I had to use Nz in the query's Where condition
to
include records for which the PO_Number has not yet been created. I
wondered if there would be any advantage to setting the default value to
0
for PO_Number (and updating current records from Null to 0) to avoid
needing
to use Nz in the Where condition. However, if I do that I cannot use
the
unique index on the combination of PO_Number and PO_Rev. The unique
index
will not come into play very much for searches and filtering that I can
see,
so its main purpose is to enforce a unique constraint on the combination
of
the two fields. Another option would be to use 0 as the default for
PO_Number, null for PO_Rev, and keep the unique index.

The question comes down to this: Is there an advantage or disadvantage
to
any of the following options:
1) Leave things as they are (PO_Number Null, PO_Rev 0, unique index on
the
two
2) PO_Number 0, PO_Rev null, unique index on the two
3) PO_Number 0, PO_Rev 0, no unique index


BruceM wrote:
Thanks very much to both for the replies. Both suggestions work. I
see
where I missed the mark. Now I need to figure out if there are
advantages
one way or the other to using TOP 1 or Max. The one that uses Max
does
not
need an ORDER BY in the subquery, so it is a little shorter, but with
my
limited recordset so far I do not see a difference in speed one way or
the
other. In both cases I needed to modify the WHERE to allow for nulls
in
PO_Number:
WHERE Nz(tblPO.PO_Number,0) = Nz(P2.PO_Number,0)

I used null instead of 0 for the default value of PO_Number before the
number is assigned. The default for PO_Rev. is 0. I could not say
exactly
why I made those choices, except maybe that I thought the coding would
be
simpler. I have a unique index on the combination of PO_Number and
PO_Rev.
This seems to work if one of the values is null (the Null,0
combination
is
allowed for any number of records), but of course it does not if both
are
0
and there is more than one record for which the PO_Number has not been
created. I test for Null in PO_Number at various places in the code.
I
could change that to testing for 0 if I also change the index to
non-unique,
or if I use 0 as the default for PO_Number and null as the default for
PO_Rev (the opposite of what I have now).

If there is a good reason for doing so I could use null or 0 as the
default
for PO_Number and/or PO_Rev in a different way than I am now. I would
need
to change some of the code to test for 0 rather than Null or vice
versa,
but
that wouldn't be a big deal. My guess is that it doesn't much matter.


The big difference between TOP and Max is that Top can
return more than one record when there is a tie. Also, I'm
pretty sure that TOP sorts the entire set where Max can take
advantage of an index to just pull out the value.

I don't understand your new question. How can records in
the two tables be related when the linking field is Null???
Seems to me that those records should just be filtered out
by using a criteria like:
Is Not Null
Click to expand...
Click to expand...
Click to expand...
 
BruceM said:
I clearly am unable to express what is going on. I will just say that the
users will need to look at the data in many different ways. For the people
who approve the POs, they just need to see POs that are not approved (and
therefore do not have a PO_Number). The default recordset when the database
is opened is just those records. People who administer the system on an
accounting level need the ability to look at (for instance) POs for which
not all line items have been received, or all POs within a date range.
Users may need to look at previous POs, which they will use as the basis for
new ones.

In almost all cases there is no need to see POs other than the highest
revision. However, there may be a need to review the revision history of a
PO (rarely more than one or two revisions). For this and other reasons I
need a lot of flexibility in building the recordset.

In most cases the user will need to see a limited recordset, so I load a
minimum recordset when the form is opened rather than loading the full
recordset and filtering it. If the user needs a different recordset they
select parameters from combo boxes or type parameters into text boxes (for
dates). The contents of the various text boxes and combo boxes is used to
build a new recordset.

My question was how to limit the records to just the highest revision level
for a particular PO number. You have answered that question. I'm sure some
of my design choices are subject to debate, but in any case the recordset is
subject to change. In some cases a null PO_Number field should be excluded,
but in other cases the recordset needs to include those records. A revised
PO is different from a standard PO in that with a revision the PO_Number is
created before it is approved. Not my call, but that's the way it is.
Click to expand...


I think that really helps clarify it for me.

Given all the different views of the data that are needed by
users in different modes or roles. I think the entire
question boils down to how users specify the options and how
you translate all those choices into a WHERE clause that
accomplishes the chosen options. The way I would approach
that is to use code to check the options and construct the
form record source SQL statement. Something like needing to
see only unapproved POs would just be the simple phrase
POnum Is Null. The approved POs would use PoNum Is Not
Null. That can be combined with the last revision using the
subquery criteria I posted earlier by using AND.

The code may get a little messy, but it is more tedious than
complex. A general outline of this kind of code could be
something like:

If {some option} Is Not Null Then
strWhere = strWhere & " And {some criteria}"
End If
If {this option} Is Not Null Then
strWhere = strWhere & " And {this criteria}"
End If
If {that option} Is Not Null Then
strWhere = strWhere & " And {that criteria}"
End If
, , ,
Me.RecordSource = "SELECT . . . FROM . . . WHERE " _
& Mid(strWhere, 6)
 
Thanks again for your input. I am doing in essence what you suggest. At
first I was loading the whole recordset and building a filter, then I
realized it was more records than I would need in most cases, so I am in the
process of switching to loading a limited recordset. In either case
building the Where condition is a similar exercise. I have adapted a
technique Allen Browne described for using a multi-select list box to filter
a report. My approach has been to add " AND " to the end of each criteria
addition, then strip off the last five characters, but I see it is a bit
simpler to remove the leading " AND ".

I agree that it is more tedious than difficult. For me, misplaced quotes in
particular can be difficult to track down.
 
Ok. It seems like we're on the same track.

In that context, I don't see where a question about Nz or a
default vallue of 0 comes into the picture. Is there some
complication in the code that you are having trouble dealing
with?

Maybe you should post a Copy/Paste of your code and explain
what part is the problem.
 
I am sometimes unsure whether to assign a default value to a number field.
Usually I do not, which means undoing the default 0 Access assigns for
number fields in Access 2003. Until a PO_Number is assigned, PO_Number is
Null. I can test for that, and use Nz as needed. However, I found I was
using Nz more often than not in VBA code and sometimes in queries, and
wondered if there is any appreciable amount of extra overhead to using Nz to
convert Null to 0 as opposed to using 0 as the default field value. My
questions and commentary on the topic were an attempt to resolve this
specific point, both for this project and for future use.

It is not so much a problem as a question. I can work with it either way,
but I wonder if one approach is preferable to the other.

Marshall Barton said:
Ok. It seems like we're on the same track.

In that context, I don't see where a question about Nz or a
default vallue of 0 comes into the picture. Is there some
complication in the code that you are having trouble dealing
with?

Maybe you should post a Copy/Paste of your code and explain
what part is the problem.
--
Marsh
MVP [MS Access]

Thanks again for your input. I am doing in essence what you suggest. At
first I was loading the whole recordset and building a filter, then I
realized it was more records than I would need in most cases, so I am in
the
process of switching to loading a limited recordset. In either case
building the Where condition is a similar exercise. I have adapted a
technique Allen Browne described for using a multi-select list box to
filter
a report. My approach has been to add " AND " to the end of each criteria
addition, then strip off the last five characters, but I see it is a bit
simpler to remove the leading " AND ".

I agree that it is more tedious than difficult. For me, misplaced quotes
in
particular can be difficult to track down.

"Marshall Barton" wrote
Click to expand...
Click to expand...
 
If you assign a default POnum of 0, then it gets messy
figuring out if two records are for the same PO or if they
have not been approved yet. I really think Null is a far
more meaningful value for unapproved POs.

I suspect that you are overusing Nz when it doesn't really
help. As I said earlier, Is Null as a criteria (or IsNull()
in code) is way more efficient and easier to understand than
Nz(POnum) = 0. Nz makes more sense if you ar doing
arithmetic on a value that might be Null and this is not
something you would do with a POnum.

The code you are using to construct the Where clause should
just omit a criteria condition if you don't care whether the
POnum has been assigned or not.
--
Marsh
MVP [MS Access]

I am sometimes unsure whether to assign a default value to a number field.
Usually I do not, which means undoing the default 0 Access assigns for
number fields in Access 2003. Until a PO_Number is assigned, PO_Number is
Null. I can test for that, and use Nz as needed. However, I found I was
using Nz more often than not in VBA code and sometimes in queries, and
wondered if there is any appreciable amount of extra overhead to using Nz to
convert Null to 0 as opposed to using 0 as the default field value. My
questions and commentary on the topic were an attempt to resolve this
specific point, both for this project and for future use.

It is not so much a problem as a question. I can work with it either way,
but I wonder if one approach is preferable to the other.

Ok. It seems like we're on the same track.

In that context, I don't see where a question about Nz or a
default vallue of 0 comes into the picture. Is there some
complication in the code that you are having trouble dealing
with?

Maybe you should post a Copy/Paste of your code and explain
what part is the problem.
--
Marsh
MVP [MS Access]

Thanks again for your input. I am doing in essence what you suggest. At
first I was loading the whole recordset and building a filter, then I
realized it was more records than I would need in most cases, so I am in
the
process of switching to loading a limited recordset. In either case
building the Where condition is a similar exercise. I have adapted a
technique Allen Browne described for using a multi-select list box to
filter
a report. My approach has been to add " AND " to the end of each criteria
addition, then strip off the last five characters, but I see it is a bit
simpler to remove the leading " AND ".

I agree that it is more tedious than difficult. For me, misplaced quotes
in
particular can be difficult to track down.

"Marshall Barton" wrote
BruceM wrote:
I clearly am unable to express what is going on. I will just say that
the
users will need to look at the data in many different ways. For the
people
who approve the POs, they just need to see POs that are not approved
(and
therefore do not have a PO_Number). The default recordset when the
database
is opened is just those records. People who administer the system on an
accounting level need the ability to look at (for instance) POs for
which
not all line items have been received, or all POs within a date range.
Users may need to look at previous POs, which they will use as the basis
for
new ones.

In almost all cases there is no need to see POs other than the highest
revision. However, there may be a need to review the revision history
of
a
PO (rarely more than one or two revisions). For this and other reasons
I
need a lot of flexibility in building the recordset.

In most cases the user will need to see a limited recordset, so I load a
minimum recordset when the form is opened rather than loading the full
recordset and filtering it. If the user needs a different recordset
they
select parameters from combo boxes or type parameters into text boxes
(for
dates). The contents of the various text boxes and combo boxes is used
to
build a new recordset.

My question was how to limit the records to just the highest revision
level
for a particular PO number. You have answered that question. I'm sure
some
of my design choices are subject to debate, but in any case the
recordset
is
subject to change. In some cases a null PO_Number field should be
excluded,
but in other cases the recordset needs to include those records. A
revised
PO is different from a standard PO in that with a revision the PO_Number
is
created before it is approved. Not my call, but that's the way it is.


I think that really helps clarify it for me.

Given all the different views of the data that are needed by
users in different modes or roles. I think the entire
question boils down to how users specify the options and how
you translate all those choices into a WHERE clause that
accomplishes the chosen options. The way I would approach
that is to use code to check the options and construct the
form record source SQL statement. Something like needing to
see only unapproved POs would just be the simple phrase
POnum Is Null. The approved POs would use PoNum Is Not
Null. That can be combined with the last revision using the
subquery criteria I posted earlier by using AND.

The code may get a little messy, but it is more tedious than
complex. A general outline of this kind of code could be
something like:

If {some option} Is Not Null Then
strWhere = strWhere & " And {some criteria}"
End If
If {this option} Is Not Null Then
strWhere = strWhere & " And {this criteria}"
End If
If {that option} Is Not Null Then
strWhere = strWhere & " And {that criteria}"
End If
, , ,
Me.RecordSource = "SELECT . . . FROM . . . WHERE " _
& Mid(strWhere, 6)
Click to expand...
Click to expand...
Click to expand...
 
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)

Again, I will take another look to be sure I am not making the criteria more
complex than it needs to be. That said, there are a lot of combinations, so
what is extraneous in one context may be necessary in another. It all
depends on the criteria.

Thanks again for all of the insight and input. Thanks to MGFoster too for
the observations about Null or 0. It really does seem to be a good way to
think about it, whether the value represents a known count of 0.

I think I need not worry too much about the overhead of using Nz where
needed. Null seems to have advantages over 0, so absent a specific reason
to use 0 it looks like null is in general the better choice.


Marshall Barton said:
If you assign a default POnum of 0, then it gets messy
figuring out if two records are for the same PO or if they
have not been approved yet. I really think Null is a far
more meaningful value for unapproved POs.

I suspect that you are overusing Nz when it doesn't really
help. As I said earlier, Is Null as a criteria (or IsNull()
in code) is way more efficient and easier to understand than
Nz(POnum) = 0. Nz makes more sense if you ar doing
arithmetic on a value that might be Null and this is not
something you would do with a POnum.

The code you are using to construct the Where clause should
just omit a criteria condition if you don't care whether the
POnum has been assigned or not.
--
Marsh
MVP [MS Access]

I am sometimes unsure whether to assign a default value to a number field.
Usually I do not, which means undoing the default 0 Access assigns for
number fields in Access 2003. Until a PO_Number is assigned, PO_Number is
Null. I can test for that, and use Nz as needed. However, I found I was
using Nz more often than not in VBA code and sometimes in queries, and
wondered if there is any appreciable amount of extra overhead to using Nz
to
convert Null to 0 as opposed to using 0 as the default field value. My
questions and commentary on the topic were an attempt to resolve this
specific point, both for this project and for future use.

It is not so much a problem as a question. I can work with it either way,
but I wonder if one approach is preferable to the other.

Ok. It seems like we're on the same track.

In that context, I don't see where a question about Nz or a
default vallue of 0 comes into the picture. Is there some
complication in the code that you are having trouble dealing
with?

Maybe you should post a Copy/Paste of your code and explain
what part is the problem.
--
Marsh
MVP [MS Access]


BruceM wrote:
Thanks again for your input. I am doing in essence what you suggest.
At
first I was loading the whole recordset and building a filter, then I
realized it was more records than I would need in most cases, so I am in
the
process of switching to loading a limited recordset. In either case
building the Where condition is a similar exercise. I have adapted a
technique Allen Browne described for using a multi-select list box to
filter
a report. My approach has been to add " AND " to the end of each
criteria
addition, then strip off the last five characters, but I see it is a bit
simpler to remove the leading " AND ".

I agree that it is more tedious than difficult. For me, misplaced
quotes
in
particular can be difficult to track down.

"Marshall Barton" wrote
BruceM wrote:
I clearly am unable to express what is going on. I will just say that
the
users will need to look at the data in many different ways. For the
people
who approve the POs, they just need to see POs that are not approved
(and
therefore do not have a PO_Number). The default recordset when the
database
is opened is just those records. People who administer the system on
an
accounting level need the ability to look at (for instance) POs for
which
not all line items have been received, or all POs within a date range.
Users may need to look at previous POs, which they will use as the
basis
for
new ones.

In almost all cases there is no need to see POs other than the highest
revision. However, there may be a need to review the revision history
of
a
PO (rarely more than one or two revisions). For this and other
reasons
I
need a lot of flexibility in building the recordset.

In most cases the user will need to see a limited recordset, so I load
a
minimum recordset when the form is opened rather than loading the full
recordset and filtering it. If the user needs a different recordset
they
select parameters from combo boxes or type parameters into text boxes
(for
dates). The contents of the various text boxes and combo boxes is
used
to
build a new recordset.

My question was how to limit the records to just the highest revision
level
for a particular PO number. You have answered that question. I'm
sure
some
of my design choices are subject to debate, but in any case the
recordset
is
subject to change. In some cases a null PO_Number field should be
excluded,
but in other cases the recordset needs to include those records. A
revised
PO is different from a standard PO in that with a revision the
PO_Number
is
created before it is approved. Not my call, but that's the way it is.


I think that really helps clarify it for me.

Given all the different views of the data that are needed by
users in different modes or roles. I think the entire
question boils down to how users specify the options and how
you translate all those choices into a WHERE clause that
accomplishes the chosen options. The way I would approach
that is to use code to check the options and construct the
form record source SQL statement. Something like needing to
see only unapproved POs would just be the simple phrase
POnum Is Null. The approved POs would use PoNum Is Not
Null. That can be combined with the last revision using the
subquery criteria I posted earlier by using AND.

The code may get a little messy, but it is more tedious than
complex. A general outline of this kind of code could be
something like:

If {some option} Is Not Null Then
strWhere = strWhere & " And {some criteria}"
End If
If {this option} Is Not Null Then
strWhere = strWhere & " And {this criteria}"
End If
If {that option} Is Not Null Then
strWhere = strWhere & " And {that criteria}"
End If
, , ,
Me.RecordSource = "SELECT . . . FROM . . . WHERE " _
& Mid(strWhere, 6)
Click to expand...
Click to expand...
Click to expand...
 
BruceM said:
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)
Click to expand...


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.
 
Thanks again for the thoughts and suggestions.
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).
 
That sure does sound like an odd accounting approach, but I
guess you have it under control. Good luck with the next
problem ;-)
 
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 said:
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]

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
Click to expand...
Click to expand...
 
Back
Top