-----Original Message-----
Dear Linda:
You said the reason for using > instead of < is that you don't want "a
record where the value of PowerNoteName is Null
or an empty string from being ranked as 1."
Now, if the only row in a group had a PowerNoteName which was NULL or
empty, I suppose it couldn't help but be ranked 1 as the only row
there.
By switching the comparison operator from < to > you not only put null
or empty values last, but you completely reverse the ranking of rows
that do have a non-empty value. Perhaps that's the effect you're
getting but is not what you want.
Let's start by simply creating a way of sorting PowerNoteName
appropriate to your requirements. Would it be appropriate to sort all
the rows where PowerNoteName is null or empty so they follow all the
rows where the value is not null or empty?
Your example data is for two rows in the same "group" having identical
ACCT_ID and ORIG_SERVICE_DATE values. Since they were ranked 2 and 3
or 1 and 3 depending on whether you had < or > in the comparison, I
assume there must have been at least one other row in that group.
I think it is the handling of null values here that is the source of
the problem. A relatively simple way of handling this is to replace
the null values with another value, perhaps "ZZZZZZZ" which would
almost certainly put that row at the end of the ordering. For this
you could replace PowerNoteName with Nz (PowerNoteName, "ZZZZZZZZ").
To replace empty values as well:
IIf(Nz(PowerNoteName, "") = "", "ZZZZZZZZ", PowerNoteName)
If there would ever be a possibility that real data might be
"ZZZZZZZZ" or greater then you should create another column with this:
IIf(Nz(PowerNoteName, "") = "", 1, 0)
This will be 1 when the value is null or empty and 0 when it has a
value. By sorting or ranking on this column you will place those rows
last. The ranking would be based on 3 columns instead of 2, being the
above, then PowerNoteName, and finally TX_NUM.
What is wrong with what you have (I think) is that you have reversed
the ranking of rows that are not null and not empty, while treating
the null/empty values as you wanted. It solves one problem but
creates another. By dividing the problem of null/empty values from
the problem of non-null/empty values, you can solve the complete
problem in a guaranteed fashion.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
One more thing: Before I tried the > in the first clause
of the Or, I tried it in this form:
(T1.PowerNoteName Is Not Null And Not "")
OR
((T1.PowerNoteName Is Null or "")
AND
(T1.TX_NUM < T.TX_NUM))) AS Rank,
That gave me the same results as I'm currently getting.
-----Original Message-----
Tom,
Thanks -- that format is definitely clearer.
I really did mean to specify 'T1.PowerNoteName >
T.PowerNoteName' rather than 'T1.PowerNoteName <
T.PowerNoteName'. What I'm really trying to do is to
prevent a record where the value of PowerNoteName is Null
or an empty string from being ranked as 1. Subsequent
queries depend on a non-null, non-empty value for
PowerNoteName in the #1-ranked record. If it weren't for
that, I could use a field other than PowerNoteName in the
comparison.
When I was debugging, I did try reversing the sign as a
way of figuring out what was happening. The result was
that the records previously ranked 2 and 3 were ranked 1
and 3.
Here are the problematic records:
PROV_NAME: SHEARS, LARRY
PowerNoteName: CT Surgery Medias Expl
ORIG_SERVICE_DATE: 7/7/2004
ACCT_ID: 01000202125570
Rank: 2
TX_NUM: 11
KeyID: 402
PROV_NAME: BOUJOUKOS, ARTHUR J
PowerNoteName:
ORIG_SERVICE_DATE: 7/7/2004
ACCT_ID: 01000202125570
Rank: 3
TX_NUM: 8
KeyID: 402
There are some other fields, but I didn't include them
because they shouldn't be relevant to this query.
Thanks for your help.
Linda
-----Original Message-----
Dear Linda:
I've modified the format of the query you provided for
my
own
preferences and posted it here for future reference:
SELECT DISTINCT T.PROV_NAME, T.PowerNoteName,
T.ORIG_SERVICE_DATE, T.ACCT_ID,
(SELECT COUNT(*) + 1 FROM [Relevant procedures] T1
WHERE (T1.ACCT_ID = T.ACCT_ID
AND T1.ORIG_SERVICE_DATE = T.ORIG_SERVICE_DATE
AND T1.PowerNoteName > T.PowerNoteName)
Or
(T1.ACCT_ID = T.ACCT_ID
AND T1.ORIG_SERVICE_DATE = T.ORIG_SERVICE_DATE
AND T1.PowerNoteName = T.PowerNoteName
AND T1.TX_NUM < T.TX_NUM )) AS Rank, keys.KeyID
FROM keys
INNER JOIN [Relevant procedures] AS T ON
keys.KeyID=T.MaxOfKeyID
WHERE keys.KeyID = T.MaxOFKeyID
ORDER BY T.ACCT_ID;
Using the distributive property of AND over OR, you
can "factor out"
the conditions on ACCT_ID and ORIG_SERVICE_DATE like
this:
SELECT DISTINCT T.PROV_NAME, T.PowerNoteName,
T.ORIG_SERVICE_DATE, T.ACCT_ID,
(SELECT COUNT(*) + 1 FROM [Relevant procedures] T1
WHERE T1.ACCT_ID = T.ACCT_ID
AND T1.ORIG_SERVICE_DATE = T.ORIG_SERVICE_DATE
AND
(T1.PowerNoteName > T.PowerNoteName
OR
(T1.PowerNoteName = T.PowerNoteName
AND T1.TX_NUM < T.TX_NUM))) AS Rank, keys.KeyID
FROM keys
INNER JOIN [Relevant procedures] AS T ON
keys.KeyID=T.MaxOfKeyID
WHERE keys.KeyID = T.MaxOFKeyID
ORDER BY T.ACCT_ID;
Your ranking is "grouped" by ACCT_ID and
ORIG_SERVICE_DATE so that
rankings start over within each set of values for those
columns. It
is then ranked by PowerNoteName first and by TX_NUM
within that.
Sorry for all the manipulation I've done, but I needed
to
understand
where you're going. And I see something a bit odd that
might be the
problem.
Where you say:
T1.PowerNoteName > T.PowerNoteName
Did you actuall intend a "<" instead of ">"?
Please try this and see if it might be the problem. If
not, please
show me a bit of sample data, the results it is
producing
that are
wrong, and what you want it to show. Maybe I can figure
it out from
that.
Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
On Tue, 7 Sep 2004 11:11:24 -0700, "lms"
When there is an OR within a WHERE statement in a
query,
and a record is True for the first OR, does Access
still
evaluate the record against the second OR?
I'm running the query below to identify all the records
in
a table, "Relevant Procedures", that have the same
ORIG_SERVICE_DATE and the same ACCT_ID. I want the
ordering to be 1) List the record(s) with the higher
value
for the field PowerNoteName first, then 2) if that's a
tie, rank the record(s)with the lowest TX_NUM first.
My question is: What would cause 2 records with equal
ORIG_SERVICE_DATE and ACCT_ID to be ranked 2 and 3,
with
no 1 anywhere? I understand that there can be ties
with
queries like this, resulting in rankings of, for
example,
1,3,3. The query below is actually the result of some
analysis + trial and error to determine ranking
conditions
that distinguish all members of each set without ties,
and
the problematic records do not tie; in fact, one has
both
a higher PowerNoteName and a higher TX_NUM.
Here's the query:
SELECT DISTINCT T.PROV_NAME, T.PowerNoteName,
T.ORIG_SERVICE_DATE, T.ACCT_ID,
(SELECT COUNT(*) + 1 FROM [Relevant procedures]
T1
WHERE
(T1.ACCT_ID = T.ACCT_ID AND T1.ORIG_SERVICE_DATE =
T.ORIG_SERVICE_DATE AND T1.PowerNoteName >
T.PowerNoteName) Or (T1.ACCT_ID = T.ACCT_ID AND
T1.ORIG_SERVICE_DATE = T.ORIG_SERVICE_DATE And
T1.PowerNoteName=T.PowerNoteName And T1.TX_NUM <
T.TX_NUM )) AS Rank, keys.KeyID
FROM keys INNER JOIN [Relevant procedures] AS T ON
keys.KeyID=T.MaxOfKeyID
WHERE (((keys.KeyID)=T.MaxOFKeyID))
ORDER BY T.ACCT_ID;
Thanks for any ideas you can offer.
Linda
.
.
.
