Last Date

[Guest]

I have a Purchase Order table. It has a Product Code, PO Received Date and a
Unit Price Date field among other fields. I wan't to query the last unit
price at the end of the 2006 calendar year. How do I do that? I've tried to
select the product code and unit price and max of poreceiveddate to get the a
single record for each product with last time it was purchased and the last
price it was purchased at but the query pulls in multiple records for each
time there is a different price. How do I write the query so it only pulls
in the last record of last year with the price? Here is the code I'm using:

SELECT [Purchase Order].PCODE, Max([Purchase Order].PORECDATE) AS
MaxOfPORECDATE, [Purchase Order].POUNITPRICE
FROM [Purchase Order]
WHERE ((([Purchase Order].PORECDATE)<=#12/31/2006#))
GROUP BY [Purchase Order].PCODE, [Purchase Order].POUNITPRICE;

What am I missing? Why can't I get a single record for each product code?

Thanks for your help.

Scott

 

[Guest]

It sounds like you would want to use a SELECT TOP 1 clause in your SQL
statment.

Somenting like:
SELECT TOP 1 [Purchase Order].PCODE, ...

add just enough fields to get a unique record

then end with an ORDER BY clause that sorts on the date descending (DESC)
like:
.....ORDER BY [Purchase Order].PORECDATE DESC;


Let me know if this helps.


Randy

 

[Guest]

The TOP 1 filter all but one record. I need one record for every product.

Thanks,

Scott

It sounds like you would want to use a SELECT TOP 1 clause in your SQL
statment.

Somenting like:
SELECT TOP 1 [Purchase Order].PCODE, ...

add just enough fields to get a unique record

then end with an ORDER BY clause that sorts on the date descending (DESC)
like:
....ORDER BY [Purchase Order].PORECDATE DESC;


Let me know if this helps.


Randy

I have a Purchase Order table. It has a Product Code, PO Received Date and a
Unit Price Date field among other fields. I wan't to query the last unit
price at the end of the 2006 calendar year. How do I do that? I've tried to
select the product code and unit price and max of poreceiveddate to get the a
single record for each product with last time it was purchased and the last
price it was purchased at but the query pulls in multiple records for each
time there is a different price. How do I write the query so it only pulls
in the last record of last year with the price? Here is the code I'm using:

SELECT [Purchase Order].PCODE, Max([Purchase Order].PORECDATE) AS
MaxOfPORECDATE, [Purchase Order].POUNITPRICE
FROM [Purchase Order]
WHERE ((([Purchase Order].PORECDATE)<=#12/31/2006#))
GROUP BY [Purchase Order].PCODE, [Purchase Order].POUNITPRICE;

What am I missing? Why can't I get a single record for each product code?

Thanks for your help.

Scott

 

[Guest]

Thank you for your reply.

Please disregard my last reply. I added the Sort command to the PORECDATE
field and it returned 52 records which is better but still not exactly what I
need. The last date for 2006 for any PO’s in the table is 12/29/06. The
query returned only those records which had a PO receive date of 12/29/06. I
need a single record for every product in the table purchased prior to
12/31/06 with the last date purchased and the corresponding unit price
whether or not the date is 12/29/06; the last date could be 9/1/06 or 1/1/06
or whatever. If you have any further ideas I would greatly appreciate them.

Thanks,

Scott

It sounds like you would want to use a SELECT TOP 1 clause in your SQL
statment.

Somenting like:
SELECT TOP 1 [Purchase Order].PCODE, ...

add just enough fields to get a unique record

then end with an ORDER BY clause that sorts on the date descending (DESC)
like:
....ORDER BY [Purchase Order].PORECDATE DESC;


Let me know if this helps.


Randy

I have a Purchase Order table. It has a Product Code, PO Received Date and a
Unit Price Date field among other fields. I wan't to query the last unit
price at the end of the 2006 calendar year. How do I do that? I've tried to
select the product code and unit price and max of poreceiveddate to get the a
single record for each product with last time it was purchased and the last
price it was purchased at but the query pulls in multiple records for each
time there is a different price. How do I write the query so it only pulls
in the last record of last year with the price? Here is the code I'm using:

SELECT [Purchase Order].PCODE, Max([Purchase Order].PORECDATE) AS
MaxOfPORECDATE, [Purchase Order].POUNITPRICE
FROM [Purchase Order]
WHERE ((([Purchase Order].PORECDATE)<=#12/31/2006#))
GROUP BY [Purchase Order].PCODE, [Purchase Order].POUNITPRICE;

What am I missing? Why can't I get a single record for each product code?

Thanks for your help.

Scott