Grouping in Access 1

[TomPose]

I posed this question and received a response that worked well... as far as it went. I should have included a little more. Let me start with the old question and add a little more to it.


I have a pass through query that supplies data of the following format:

Item Date Indx

1 Nov 3 3
1 Nov 6 4
1 Nov 4 5
2 Nov 3 6
2 Nov 2 7
2 Nov 7 8

The change to the question below is that I also need to have the indx number returned to me.

I would like to know how I can pick the highest date for each item while ignoring the others. ie, I want to have Item 1, Nov 6 & item 2, Nove 7 and ignore all the others. I need this to happen on the fly. The reason for this is that there is other information that is date dependant and I would like to be able to build a query using this info while linking it to another table.

Thanks in advance! -- Again
Tom

 

[Junior1544]

your using a passthrough query you said... this can be done within the sql building the query... being that it'll be done on the fly, it'll be a good idea to use sql for it... that'll be the fastest way...

sorry i can't help more, but i don't know sql well enough...

--James
junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[JeremyNYC]

Tom,

Hmm. I just made this query on one of my tables and it worked fine. Does this type of query not work for you?

SELECT BankID, Max(AccountID) AS MaxOfAccountID
FROM tblAccount
GROUP BY BankID;

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[Junior1544]

Jeremy covering my butt again junior1544@jmjpc.net
Life is change. To deny change is to deny life.

 

[JoeF]

I have had a similar problem. I wanted to get the top 3 or 4 records by a grouping, but I had to write several queries to do that. Run a query taking the max by grouping to get the top record and create a variable that says "top record", then Run a second query with the top record filtered out in order to get the second record and have that new variable now say "second record", etc. This method is very slow and inneficient, does anyone know a better way.

I know if you want the top 5 records, you can use the "top values" filter, but if you just want the first 2 or 3, you can't use that.

 

[JeremyNYC]

Joe,

You can use Top for as many or as few records as you want.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developers' section of the site for some helpful fundamentals.

 

[TomPose]

Thanks to all. I have used a little of each and come up with both grouping and multiple queries. Thanks again.

Tom