Solved

Posted on 2013-10-01
Medium Priority
222 Views
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!
0
Question by:CompTech810
• 4
• 3

LVL 50

Expert Comment

ID: 39536785
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

LVL 17

Expert Comment

ID: 39536790
``````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

LVL 2

Author Comment

ID: 39536919
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

LVL 50

Accepted Solution

Dale Fye earned 750 total points
ID: 39537022
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

LVL 2

Author Comment

ID: 39537660
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

LVL 50

Expert Comment

ID: 39537783

SELECT T1.Vendor_Nbr, T1.Vendor_Name, T1.Po_Nbr, T1.Po_Date
ORDER BY T1.Vendor_Name, T1.Po_Date DESC;
0

LVL 2

Author Comment

ID: 39537911
Thanks fyed!  That worked.

I haven't finished the whole query yet.  I will be off work until Monday, and will continue then.
0

LVL 2

Author Comment

ID: 39559789
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

Featured Post

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.