Getting only last date from a table with multi suppliers 1

[cityrock]

Hello.
Maybe you can help me.
I got a table, that includes a couple of vendors, all selling same items.
I need to get a query that will show me only the last vendor that sold me a specific item.
Using the design grid, I cannot groupby, and give Date as last, since I will get the last date from each vendor. Not only the last vendor with the last date.
Any help??
Tnx in advance.
Eli

 

[KenReay]

Group on vendor and MAX on date, should do it Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
UK
kenneth.reaySPAMNOT@talk21.com
remove SPAMNOT to use

 

[cityrock]

Still doesnt work. It gives me the item a couple of times, the latest date of every vendor. All I need is the item, once with the last vendor I bought it from.
can it work??

 

[SgtJarrow]

You probably don't need to group by vendor....just use the LAST (or MAX) for the date, and maybe groupby on the items, and it will select the last date, and will therefore also show the last vendor. Please remember to give helpful posts the stars they deserve! This makes the post more visible to others in need!

Robert L. Johnson III, A+, Network+, MCP
Access Developer/Programmer
robert.l.johnson.iii@citigroup.com

 

[cmmrfrds]

See thread701-373963 and model after.

Make vendor a correlated subquery and group by item with a max on date. The problem you are experiencing is that when any field is an aggregate then all fields in the query must be aggregates or in the group by. The subquery will get you around that problem, since you can't group on vendor or you will get too many records.

 

[scriverb]

cmmrfrds, I am intrigued by your response. Sounds like the way to go but when I put it to the test I am getting the error: "can have Aggregate function is WHERE CLAUSE" This error seems to be refering to the Sub Select where we are matching the Max(A.DateSold) = B.DateSold.

Any ideas? I setup a little table to test this and I can't seem to get past this.

Advise please. I used your model in the mentioned thread. Bob Scriver

 

[cmmrfrds]

This works.
select A.id, max(A.adate) as maxdate,
(select salary from table1 B
where A.id = B.id and max(A.adate) = B.adate) as salery
from table1 A
group by A.id

This will error.
select A.id,
(select salary from table1 B
where A.id = B.id and max(A.adate) = B.adate) as salery
from table1 A
group by A.id

The max(A.adate) needs to be part of the outer select, which is the first pass of the data, or it will not be available for the subquery. If this doesn't help, paste in your sql statement.

 

[scriverb]

My Table tblSales: Item text(20), Vendor text(30), DateSold Date/Time

Query SQL:
SELECT A.Item, Max(A.DateSold) AS MaxDateSold, (Select Vendor from tblSales B where A.Item = B.Item and Max(A.DateSold) = B.DateSold) as Vendor
FROM tblSales A
GROUP BY A.Item;

Returning Error:
Can't have aggregate function in where clause (A.Item = B.Item and Max(A.DateSold) = B.DateSold)

Following your instructions I believe the above is correct. I still get the error listed above. Max(A.DateSold) is part of the initial select and also included in the WHERE clause of the sub select. This is where the error is showing up. I am using ACCESS 97 if that may be the problem.????

Bob Scriver

 

[cityrock]

Tnx all.
Basicly I solved the problem (or at least it looks like it), in the design I Maxed the dated, and asked for first of vendor. Thats how I got one vendor, with the latest date. The results look like what I expected, hopefully I dont have errors. SO it was simple, without a sub query. If you think its an error, please tell me.
Tnx again for all the help.
Eli

 

[scriverb]

I don't believe that will hold true for all cases. You just happened to have the First Vendor have the Max Date so it looks like it is accurate. But, what if the first vendor has the earliest date selling the product. You will get that vendor with the Max date from another vendor.

We will keep working on this to get it right for you. Bob Scriver

 

[cmmrfrds]

scriverb, you are right the subquery only works in SQL Server which is where I tested the code. Sorry, I have found most times the same SQL will work in both. A derived query will work in Access as well as SQL Server - a more generalized solution.

SELECT dt.Item, dt.maxdatesold, A.vendor
FROM tblSales AS A INNER JOIN [Select b.item, Max(B.DateSold) AS MaxDateSold from tblSales as B
Group by b.item]. AS dt ON dt.Item = A.Item
WHERE maxdatesold = A.datesold;

 

[cityrock]

You are right... I checked out some more numbers, and it gives me the first Vendor, even if the date is for another vendor.. back to step one... lets see what is possible. Help is still needed..
thank you again
Eli

 

[scriverb]

Eli, your solution is detailed in cmmrfrds post above. The original Thread and sample code did not work in ACCESS but the code that he provides here does. I tested it out and it works as you need it. You will have to modify it to fit your table names etc. but that should be your solution.

Cmmrfrds, here is a star for you as I will tuck this sql example into my bag of tricks for the future.

Good luck with your project. Bob Scriver