Access 2007 Skip to next records once value changes

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
CompTech810Asked:
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.

Dale FyeCommented:
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
Barry CunneyCommented:
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

Open in new window

0
CompTech810Author 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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

Dale FyeCommented:
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

Your issues matter to us.

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

Start your 7-day free trial
CompTech810Author 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.

SELECT T1.Administrators_POD_1_NF.Vendor_Nbr, T1.Administrators_POD_1_NF.Vendor_Name, T1.Administrators_POD_1_NF.Po_Nbr, T1.Administrators_POD_1_NF.Po_Date
FROM T1.Administrators_POD_1_NF AS T1
ORDER BY T1.Administrators_POD_1_NF.Vendor_Name, T1.Administrators_POD_1_NF.Po_Date DESC;
0
Dale FyeCommented:
Assuming that your table (or query) is [Administrators_POD_1_NF], try:

SELECT T1.Vendor_Nbr, T1.Vendor_Name, T1.Po_Nbr, T1.Po_Date
FROM [Administrators_POD_1_NF] AS T1
ORDER BY T1.Vendor_Name, T1.Po_Date DESC;
0
CompTech810Author 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
CompTech810Author 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.