Hello Experts!

Hoping someone can help me out here.  I have a database that contains VENDOR and POs LINE, TOTAL, there are more fields.

I only need the first PO per vendor, this is the latest PO for the VENDOR.
There could be multipe PO records for one PO, they are line items.   Exp.

VENDOR                  PO            LINE       TOTAL
1234                         8956         1             10.20
1234                         8956         2               9.58
1234                         7859         1               7.45
1234                         7859         2               1.20
4567                         4756         1               4.50
4567                         4237         1               2.20
7845                         5789         1               3.33

The results would be:

1234                       8956          SUM:   19.78
4567                       4756          SUM:     4.50
7845                       5789          SUM:     3.33

Greatly appreciate any help!
LVL 2
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
You don't indicate how you know which PO is the last (I assume it is the largest in the following)

SELECT T1.Vendor, T1.PO, SUM(T1.[Total]) as SumTotal
FROM yourTable as T1
INNER JOIN (SELECT Vendor, MAX([PO]) as MaxPO FROM yourTable) as T2
on T1.Vendor = T2.Vendor AND T1.PO = T2.PO
GROUP BY T1.Vendor, T1.PO
0
Commented:
``````SELECT
VENDOR
,PO
,TOTAL
FROM
(
SELECT
VENDOR
,PO
,TOTAL
,(SELECT COUNT(*) FROM PO_LINES_TABLE p WHERE p.VENDOR = tot.VENDOR AND tot.PO > p.PO) + 1 [Rank]
FROM
(
SELECT
VENDOR
,PO
,SUM(TOTAL) TOTAL
FROM
PO_LINES_TABLE
GROUP BY
VENDOR
,PO
) tot
) r
WHERE r.Rank = 1
``````
0
Author Commented:
fyed,

The way I know it is the latest on  is I am sorting first on VENDORNAME and then PODATE.  Sorry I should have put that in the question.

In your statement are you joining another table.  In mine all the fields are in the same table.
0
Commented:
Actually, I had a couple of errors in the join on my previous post.  You will notice that there is a subquery, which retrieves the Max(PODate) for each vendor.  You join that subquery to your main table to limit the records.

Since you are using the PODate to determine the latest PO for each Vendor, then you would use:

SELECT T1.Vendor, T1.PO, SUM(T1.[Total]) as SumTotal
FROM yourTable as T1
INNER JOIN (SELECT Vendor, MAX([PODate]) as MaxPODate
FROM yourTable
GROUP BY Vendor) as T2
on T1.Vendor = T2.Vendor AND T1.PODate = T2.MaxPODate
GROUP BY T1.Vendor, T1.PO
0

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
This is going to sound really stupid to an Access expert but when I try to use the table as T1 Access tries to look for T1.mdb.

I am using the statement in a access query.  I tried to start simple and then build from there but can't get past the T1 issue.

This is the statement I have.

0
Commented:

SELECT T1.Vendor_Nbr, T1.Vendor_Name, T1.Po_Nbr, T1.Po_Date
ORDER BY T1.Vendor_Name, T1.Po_Date DESC;
0
Author Commented:
Thanks fyed!  That worked.

I haven't finished the whole query yet.  I will be off work until Monday, and will continue then.
0
Author Commented:
Fyed I discovered a file on our system that had more of the detail I the format I was looking for.  Although I didn't finish with the query you provided I'm going to award you all points.  Thanks!
0
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.