need help on this query

[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.

 

[Les]

Hi Elsie,
Try the following:

1) Keep your current query, except eliminate the unit cost
from the query.

2) Create a new query, using query from step l and linking
to rcpt table, by vendor num and maxdate. Select vendor
num, maxdate and unit-cost. This should give you the unit
cost for the latest date for the vendor num.

-----Original Message-----
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

 

[Elsie]

Thanks for your help, Les.
I split the original query into 2 queries:
the 1st one contains: MaxOfrcvd-date, item, cost
the 2nd contains: MaxOfrcvd-date, item, vend-num
and then i have a 3rd query which links the 2 queries by item and
MaxOfrcvd-date.

this is the same as the method you suggested?