help with Group By query

[Ted]

Hi all, I could really use some help here. I'm trying to run a query to pull
the last commission used by all of our brokers. I know this isn't that
complicated but i'm having a major brain fart this morning.

I have a table ALLCOMANIESDATA, trying to pull BRK and BRK% using POSTDATE
but its not working bc I'm using a Group By query and its grouping by BRK%
so I'm getting too many records:

SELECT BRK, [BRK%], Last(ALLCOMPANIESDATA.POSTDATE) AS LastOfPOSTDATE FROM
ALLCOMPANIESDATA GROUP BY BRK,[BRK%];

Any ideas. I feel like this should have taken 5 minutes not an hour of
staring at my screen. lol

TIA
Ted

 

[Michel Walsh]

LAST is not intended to mean LATEST, so the methodology is probably wrong.

If you can think of your problem like a library where you want to know the
latest borrower of each book, then you can use one of the four methods
presented at http://www.mvps.org/access/queries/qry0020.htm




Vanderghast, Access MVP

 

[John Spencer]

You could try the following.

SELECT A.BRK, A.[BRK%], A.PostDate
FROM ALLCOMPANIESDATA as A INNER JOIN
(SELECT BRK, Max(PostDate) as LastDate
FROM ALLCOMPANIESDATA
GROUP BY BRK) AS B
ON A.BRK = B.BRK AND A.PostDate = B.LastDate

LAST will not necessarily give you the latest of anything. It will give you
the value from the LAST record retrieved for the group - which will often be
the last record stored, but not always.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

Your query is selecting BRK, [BRK%], and Last(ALLCOMPANIESDATA.POSTDATE).
It will pull all combinations of these three fields, not just the last
Postdate with BKR & BKR% as one record.
Use two queries. First select the primary key and
LastALLCOMPANIESDATA.POSTDATE) and then use that query joined on primary key
in the second query for the rest of the fields.

 

[Ted]

Thank you Michel. I did have that wrong what I should have used is Max.
Would that work?

LAST is not intended to mean LATEST, so the methodology is probably wrong.

If you can think of your problem like a library where you want to know the
latest borrower of each book, then you can use one of the four methods
presented at http://www.mvps.org/access/queries/qry0020.htm




Vanderghast, Access MVP

Hi all, I could really use some help here. I'm trying to run a query to
pull the last commission used by all of our brokers. I know this isn't
that complicated but i'm having a major brain fart this morning.

I have a table ALLCOMANIESDATA, trying to pull BRK and BRK% using
POSTDATE but its not working bc I'm using a Group By query and its
grouping by BRK% so I'm getting too many records:

SELECT BRK, [BRK%], Last(ALLCOMPANIESDATA.POSTDATE) AS LastOfPOSTDATE
FROM ALLCOMPANIESDATA GROUP BY BRK,[BRK%];

Any ideas. I feel like this should have taken 5 minutes not an hour of
staring at my screen. lol

TIA
Ted

 

[Ted]

Thank you John. You're right about the Last. I meant to say Max. I'll try
using you're query w the Max function instead of Last. Thanks for your help

You could try the following.

SELECT A.BRK, A.[BRK%], A.PostDate
FROM ALLCOMPANIESDATA as A INNER JOIN
(SELECT BRK, Max(PostDate) as LastDate
FROM ALLCOMPANIESDATA
GROUP BY BRK) AS B
ON A.BRK = B.BRK AND A.PostDate = B.LastDate

LAST will not necessarily give you the latest of anything. It will give
you the value from the LAST record retrieved for the group - which will
often be the last record stored, but not always.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi all, I could really use some help here. I'm trying to run a query to
pull the last commission used by all of our brokers. I know this isn't
that complicated but i'm having a major brain fart this morning.

I have a table ALLCOMANIESDATA, trying to pull BRK and BRK% using
POSTDATE but its not working bc I'm using a Group By query and its
grouping by BRK% so I'm getting too many records:

SELECT BRK, [BRK%], Last(ALLCOMPANIESDATA.POSTDATE) AS LastOfPOSTDATE
FROM ALLCOMPANIESDATA GROUP BY BRK,[BRK%];

Any ideas. I feel like this should have taken 5 minutes not an hour of
staring at my screen. lol

TIA
Ted

 

[Ted]

That worked! Thank you very much for your time John. have a good one!

You could try the following.

SELECT A.BRK, A.[BRK%], A.PostDate
FROM ALLCOMPANIESDATA as A INNER JOIN
(SELECT BRK, Max(PostDate) as LastDate
FROM ALLCOMPANIESDATA
GROUP BY BRK) AS B
ON A.BRK = B.BRK AND A.PostDate = B.LastDate

LAST will not necessarily give you the latest of anything. It will give
you the value from the LAST record retrieved for the group - which will
often be the last record stored, but not always.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Hi all, I could really use some help here. I'm trying to run a query to
pull the last commission used by all of our brokers. I know this isn't
that complicated but i'm having a major brain fart this morning.

I have a table ALLCOMANIESDATA, trying to pull BRK and BRK% using
POSTDATE but its not working bc I'm using a Group By query and its
grouping by BRK% so I'm getting too many records:

SELECT BRK, [BRK%], Last(ALLCOMPANIESDATA.POSTDATE) AS LastOfPOSTDATE
FROM ALLCOMPANIESDATA GROUP BY BRK,[BRK%];

Any ideas. I feel like this should have taken 5 minutes not an hour of
staring at my screen. lol

TIA
Ted