E
Elsie
hi, I am having some trouble with a query which is built by linking 2
tables:
Table rcpt:
--------------
po-num
po-line
po-release
rcvd-date
qty
unit-cost
vend-num
Table poitem:
---------------
po-num
po-line
po-release
item
unit-cost
vendor
I have linked po-num, po-line and po-release for the 2 tables. in the
query,I need item, unit-cost, vend-num and rcvd-date is from April to
September.
I need to get the latest cost for each item from each vendor between April
and September.
the total for rcvd-date is max. item and vend-num are group by.
the problem lies with unit-cost - if I use max, the max cost will be
displayed, but the max cost may not be at the latest date. this is the same
for last.
e.g.: item A
MaxOfrcvd-date vend-num unit-cost
8/24/04 50737 69
8/30/04 19012 70
8/23/04 19012 73.17
I am supposed to get 2 outputs for this item: 50737 with cost as 69 and
19012 with cost as 70.
pls advise.
tables:
Table rcpt:
--------------
po-num
po-line
po-release
rcvd-date
qty
unit-cost
vend-num
Table poitem:
---------------
po-num
po-line
po-release
item
unit-cost
vendor
I have linked po-num, po-line and po-release for the 2 tables. in the
query,I need item, unit-cost, vend-num and rcvd-date is from April to
September.
I need to get the latest cost for each item from each vendor between April
and September.
the total for rcvd-date is max. item and vend-num are group by.
the problem lies with unit-cost - if I use max, the max cost will be
displayed, but the max cost may not be at the latest date. this is the same
for last.
e.g.: item A
MaxOfrcvd-date vend-num unit-cost
8/24/04 50737 69
8/30/04 19012 70
8/23/04 19012 73.17
I am supposed to get 2 outputs for this item: 50737 with cost as 69 and
19012 with cost as 70.
pls advise.