Where am I going wrong?

[Guest]

Dear All (again)!

I have the follwoing table, Sheet 1:

ID PartNo Qty InvDate Price
1 LH01040038 4 01/01/2001 459.92
2 LH01040038 4 01/01/2002 454.02
3 LH01040038 4 01/01/2003 430.02
4 LH12345678 3 01/01/2004 50.99


I require output of ONLY the PartNo and the most recent price ever paid (ie
Price at MAX InvDate). Expected Output would thus be:

LH01040038 430.02
LH12345678 50.99


I am using the following code:

SELECT Q.partno, Q.Price
FROM Sheet1 AS Q
WHERE Q.invdate = (SELECT Max(T.invdate) FROM Sheet1 AS T Where T.invdate =
Q.invdate);

But the output is not as I would expect:

partno Price
LH01040038 459.92
LH01040038 454.02
LH01040038 454.02



This is actually just a small sample of the data I'm using to test the
coding of the queries, the final query is to be run on thousands of lines of
data so I need to be sure that each part number only appears once with its
latest price.

Where am I going wrong?

 

[Allen Browne]

See:
Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm

The article suggests 4 ways to do this.

 

[Tom Ellison]

Dear Matt:

Your subquery has been correlated on invdate, while I would have
expected you would want it correlated on PartNo:

SELECT Q.partno, Q.Price
FROM Sheet1 AS Q
WHERE Q.invdate = (SELECT Max(T.invdate) FROM Sheet1 AS T
WHERE T.PartNo = Q.PartNo);

Does that work better?

Please consider whether it may be possible to have two rows in Sheet 1
for the same PartNo and InvDate. If so, then this creates an
ambiguity in which is the "most recent price." If this happens, what
do you wish to see? More than one price? The highest or lowest price
on that "most recent date?" The price with the largest quantity (may
also be duplicated and thus ambiguous)? The price with the lowest or
highest ID (an arbitrary way of getting just one price)? These
alternatives are meant to be illustrative of possible ways to obtain a
single "most recent price" for any PartNo.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Guest]

Thanks Allen! I've actually been using that exact article, as you can see my
code is an exact copy of that given in Example 2 as far as I can see:

My Code:

SELECT Q.partno, Q.Price
FROM Sheet1 AS Q
WHERE invdate = (SELECT Max(T.invdate) FROM Sheet1 AS T Where T.invdate =
Q.invdate);


Example Code:

SELECT Q.BookID, Q.BorrowerID
FROM TableName As Q
WHERE DateOut = (SELECT Max(T.DateOut)
FROM TableName As T
WHERE T.BookID = Q.BookID)


and as I explained, the output was not as this article had lead me to believe!

Where am I going wrong?

Thanks for your help.

Matt

 

[Guest]

TOM! Thank you! That seems to have done the trick on the test data! I'm going
to run it on the full data -set and see what happens - I know how many Rows
it should produce so I can check if it's worked properly.

I hadn't thought about the contradictions you mentioned - I presume if such
a Row is encountered the default is to display both records? I shouldn't
think it will happen with out data, but what's the simplest safeguard (i
think outputting by ID number would probably be fine).

Thanks a million!

Matt

 

[Tom Ellison]

Dear Matt:

No problem. It only took a half minute of study to see this. For
someone who has written dozens (if not hundreds) of correlated
subqueries, it becomes pretty quickly obvious.

When not certain of whether there may be duplicates, in your case for
the combination of PartNo and InvDate, a quick test grouping on these
two and HAVING COUNT() > 1 will tell you if there are currently
duplicates. Over the long run, if you wish to absolutely exclude
duplicates from occurring, a unique index is indicated.

Anyway, I tend to always include a brief outline of the duplication
problem, as it occurs frequently enough. Just remember that if it
doesn't occur now, it certainly may later if you allow it. If you
allow it, then deliberately create some examples and think about how
you want to solve it. I recommend something like showing the highest
price from among all the most recent rows for each PartNo as this is a
meaningful way of selecting a unique price. An alternative might be
to show the average price on the most recent date, along with a count
of how many there were that make up this average. It could be a
weighted average by quantity. By showing how many invoices make up
the price the user can then judge why an average is necessary, as
there is not just one price for that date. Presented nicely, humans
can quickly come to understand such ambiguity in the data. Another
way might be to show the minimum, maximum, and average price along
with the count. Then when reporting it, show only the average column
when the count is 1. This would explain the situation rather nicely,
don't you think?

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts