Grouping query help

I know I can do this using a sub query but I'm trying not to Basically I got 
three feilds of data.  One of the feilds is a dat feild and I'm trying to 
group the data on the most recent date.  For example:  In the data below, I 
would like the query to display for each category, the actual rate on the 
most recent date (for that particular category).

Category	Date	   Rate
CIT	4/1/2004	     4
CIT	7/1/2004	    4.25
CIT	8/9/2005	    6.5
L30	11/10/2006    5.32
LAS	5/24/2004        4
LAS	7/1/2004	4.25
LAS	2/2/2005	5.5
LAS	3/22/2005	5.75
STD	5/24/2004	4
STD	7/1/2004	4.25
STD	9/21/2004	4.75
STD	11/10/2004	5

Desired Result
CIT	8/9/2005	    6.5
L30	11/10/2006    5.32
LAS	3/22/2005	5.75
STD	11/10/2004	5

0
Utf
10/11/2007 6:50:01 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
688 Views

Similar Articles

[PageSpeed] 35

Try this ---
SELECT YourTable.Category, Max(YourTable.YourDate) AS MaxOfDate, 
YourTable.Rate
FROM YourTable
GROUP BY YourTable.Category, YourTable.Rate;

-- 
KARL DEWEY
Build a little - Test a little


"Bdavis" wrote:

> I know I can do this using a sub query but I'm trying not to Basically I got 
> three feilds of data.  One of the feilds is a dat feild and I'm trying to 
> group the data on the most recent date.  For example:  In the data below, I 
> would like the query to display for each category, the actual rate on the 
> most recent date (for that particular category).
> 
> Category	Date	   Rate
> CIT	4/1/2004	     4
> CIT	7/1/2004	    4.25
> CIT	8/9/2005	    6.5
> L30	11/10/2006    5.32
> LAS	5/24/2004        4
> LAS	7/1/2004	4.25
> LAS	2/2/2005	5.5
> LAS	3/22/2005	5.75
> STD	5/24/2004	4
> STD	7/1/2004	4.25
> STD	9/21/2004	4.75
> STD	11/10/2004	5
> 
> Desired Result
> CIT	8/9/2005	    6.5
> L30	11/10/2006    5.32
> LAS	3/22/2005	5.75
> STD	11/10/2004	5
> 
0
Utf
10/11/2007 8:34:01 PM
Hey Karl,

Didn't work unfortunetly.  Here's the SQL with the actual feild and table 
names:

SELECT dbo_PrimeTable.sBankCode, Max(dbo_PrimeTable.dtEffectiveDate) AS 
MaxOfDate, 
dbo_PrimeTable.drate
FROM dbo_PrimeTable
GROUP BY dbo_PrimeTable.sBankCode, dbo_PrimeTable.dRate;

It's still listing out every record in the table and not grouping.
"KARL DEWEY" wrote:

> Try this ---
> SELECT YourTable.Category, Max(YourTable.YourDate) AS MaxOfDate, 
> YourTable.Rate
> FROM YourTable
> GROUP BY YourTable.Category, YourTable.Rate;
> 
> -- 
> KARL DEWEY
> Build a little - Test a little
> 
> 
> "Bdavis" wrote:
> 
> > I know I can do this using a sub query but I'm trying not to Basically I got 
> > three feilds of data.  One of the feilds is a dat feild and I'm trying to 
> > group the data on the most recent date.  For example:  In the data below, I 
> > would like the query to display for each category, the actual rate on the 
> > most recent date (for that particular category).
> > 
> > Category	Date	   Rate
> > CIT	4/1/2004	     4
> > CIT	7/1/2004	    4.25
> > CIT	8/9/2005	    6.5
> > L30	11/10/2006    5.32
> > LAS	5/24/2004        4
> > LAS	7/1/2004	4.25
> > LAS	2/2/2005	5.5
> > LAS	3/22/2005	5.75
> > STD	5/24/2004	4
> > STD	7/1/2004	4.25
> > STD	9/21/2004	4.75
> > STD	11/10/2004	5
> > 
> > Desired Result
> > CIT	8/9/2005	    6.5
> > L30	11/10/2006    5.32
> > LAS	3/22/2005	5.75
> > STD	11/10/2004	5
> > 
0
Utf
10/11/2007 11:27:00 PM
Sorry, did not test before posting.   You can do it in two queries ---

    Bdavis ---
SELECT dbo_PrimeTable.sBankCode, Max(dbo_PrimeTable.dtEffectiveDate) AS 
MaxOfDate
FROM dbo_PrimeTable
GROUP BY dbo_PrimeTable.sBankCode;

SELECT dbo_PrimeTable.*
FROM dbo_PrimeTable INNER JOIN Bdavis ON (dbo_PrimeTable.dtEffectiveDate = 
Bdavis.MaxOfDate) AND (dbo_PrimeTable.sBankCode = Bdavis.sBankCode);

-- 
KARL DEWEY
Build a little - Test a little


"Bdavis" wrote:

> Hey Karl,
> 
> Didn't work unfortunetly.  Here's the SQL with the actual feild and table 
> names:
> 
> SELECT dbo_PrimeTable.sBankCode, Max(dbo_PrimeTable.dtEffectiveDate) AS 
> MaxOfDate, 
> dbo_PrimeTable.drate
> FROM dbo_PrimeTable
> GROUP BY dbo_PrimeTable.sBankCode, dbo_PrimeTable.dRate;
> 
> It's still listing out every record in the table and not grouping.
> "KARL DEWEY" wrote:
> 
> > Try this ---
> > SELECT YourTable.Category, Max(YourTable.YourDate) AS MaxOfDate, 
> > YourTable.Rate
> > FROM YourTable
> > GROUP BY YourTable.Category, YourTable.Rate;
> > 
> > -- 
> > KARL DEWEY
> > Build a little - Test a little
> > 
> > 
> > "Bdavis" wrote:
> > 
> > > I know I can do this using a sub query but I'm trying not to Basically I got 
> > > three feilds of data.  One of the feilds is a dat feild and I'm trying to 
> > > group the data on the most recent date.  For example:  In the data below, I 
> > > would like the query to display for each category, the actual rate on the 
> > > most recent date (for that particular category).
> > > 
> > > Category	Date	   Rate
> > > CIT	4/1/2004	     4
> > > CIT	7/1/2004	    4.25
> > > CIT	8/9/2005	    6.5
> > > L30	11/10/2006    5.32
> > > LAS	5/24/2004        4
> > > LAS	7/1/2004	4.25
> > > LAS	2/2/2005	5.5
> > > LAS	3/22/2005	5.75
> > > STD	5/24/2004	4
> > > STD	7/1/2004	4.25
> > > STD	9/21/2004	4.75
> > > STD	11/10/2004	5
> > > 
> > > Desired Result
> > > CIT	8/9/2005	    6.5
> > > L30	11/10/2006    5.32
> > > LAS	3/22/2005	5.75
> > > STD	11/10/2004	5
> > > 
0
Utf
10/12/2007 4:17:00 AM
Reply:

Similar Artilces:

office 2000 [excel] cannot open previously OK file. Help
When trying to open a Microsoft Excel Work Sheet that I had accessed modified and saved to only yesterday [Office 2000] a window pops up with: "name.xls cannot be accessed. The file may be resd-only, or you may be trying toaccess a read only location. Or, the server the document is stored on may not be responding." Older files stored on the same mediums ;HD, Flash memory or 3.5in. diskette are accessed with no problem. This is on a network drive? ...

Excel and some disappeared sheets
Anybody has the same or similar experiences : - I worked with excel file / closed as usually - new day opened and unfortunatelly some very important sheets disappeared / why ? Don=B4t know - I had about 7-9 sheets just now only 3 ones are there but not so important as another ones - the file has original size / I quickly looked into file by normal text editor only for reading and all relevant data are there, however if I open file by Excel, NO original sheets or data are there. - this file is VERY IMPORTANT for me - why happened ? Don=B4t know since I ask anybody for help Thanks in advan...

Queries and Charts
Does anyone know why the expressions in queries work fine for reports but not charts? ...

Pass parameter from FORM to QUERY
I have an append query that I trying to call from a cmd btn...but it prompts me for the parameter(ie QuoteID)... How do I tye it into my call: Dim stQueName As String stQueName = "Quote Query" DoCmd.OpenQuery stQueName, acNormal, acEdit On Apr 13, 12:56 pm, jlt...@hotmail.com wrote: > I have an append query that I trying to call from a cmd btn...but it > prompts me for the parameter(ie QuoteID)... > How do I tye it into my call: > > Dim stQueName As String > stQueName = "Quote Query" > DoCmd.OpenQuery stQueName, acNormal, acEd...

Changing query execution sequence
Hi all, I got a spreadsheet which would execute a bunch of queries. It's noted that the queries are executing in the sequence of when it was added to the spreadsheet. Does anyone out there know of a way to switch the order without deleting and recreating them? Thanks! Wing ...

OL2002 Problem : HELP, SOS, MAYDAY
Hi, I have an IMAP account and OL2002 as client. I have checked for my account ( tools, send/receive settings, define send/receive groups, <my_account>, edit) download item description only. It works only the first time I start OL2002, but it does not work anymore for subsequent automatic/manual send/receive operations. Please could someone really help me ? Thank you in advance, Patrick ...

Help With Margin Formula
Hello, I need help with a margin forumla (calculated from retail). Say I have a cost of $10.00, and I need the formula to calculate a 40% margin from retail. So the retail should end up at $16.67. Not sure how to get from $10.00 to $16.66, I just know the cost and the margin I need to make. Thanks JR =A1/(100%-40%) -- Kind regards, Niek Otten "JR" <gaspower@aol.com> wrote in message news:eGszf.424$2O6.53@newssvr12.news.prodigy.com... > Hello, > I need help with a margin forumla (calculated from retail). Say I have a > cost of $10.00, and I need the formul...

GETPIVOTDATA data_field help
When I enter a reference to a cell, eg A2, as the value for the data_field item in the GETPIVOTDATA I get a #REF error. However, when I instead type the value of A2, eg 2003, manually into the GETPIVOTDATA formula, it works. Can anyone please help--I want this formula to vary based on what is in A2. Cheers This function is so variable in action, and difficult to handle, that have given it up in favour of VLOOKUP. In fact, having no problem i use with pivot tables -- Message posted from http://www.ExcelForum.com Try copying the headings from the pivot table, and pasting them into the ce...

Exchange server crashed, please help....! Need to restore two priv.edb and pub.edb files into one....!
Hi Guys, I was wondering if I could get some help with the following problem we are having on our company. Here is the scenario; Our Windows NT 4.0 SP4a server running Exchange 5.5 SP4 crashed (Server 1) due to the exchange database reaching its 16 Gig's max limit. I went ahead and moved some mailboxes' e-mails to a few .pst files in order to make some space. This worked ok. Then, I decided to build another exchange server (Server 2) to moved some mailboxes and alleviate the load. Once the server was ready and configured as part of the current exchange site, I went ahead and move...

Help!!!!
I am running Exchange 5.5 on NT 4.0 sp6a. Norton Anti- virus deleted my edb.log file. I have done an on-line restore with Arcserve 2000 and now my directory service won't start. getting 1166 internal processing error. I have been to support and could find anything that helped. Thanks in advance You may want to run eseutil /mh <drive and path to your dir.edb and include the dir.edb > c:\edb.dmp. Find this file and see if your dir is also inconsistent. If so you will need to restore your dir. -- Nan Bennett (MSFT) Microsoft Exchange Support Please do not send e-mail...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

multiple iterations of same query
Running Access 2003 on Vista. I have a query that chooses 15 records at random. This works fine. What I want to do is run the same query for approx 110 different users, each with their own randomly selected 15 records. Obviously, I want to avoid manually running the same query that many times. How can I get around this? Post your query SQL. -- Build a little, test a little. "Nathan" wrote: > Running Access 2003 on Vista. > > I have a query that chooses 15 records at random. This works fine. > > What I want to do is run the same query fo...

I really need help with a formview problem
I have a formview that I place a toolbar onto. In my OnInitialUpdate(), after I create the toolbar, I say GetParentFrame()->RecalcLayout(); ResizeParentToFit(FALSE); ResizeParentToFit(TRUE); The formview is still not sized properly. It looks like the problem is the toolbar on the top of the formview. It is not being taken into account in sizing the formview. The difference at the bottom of the form that is not displayed is the size of the toolbar. Any suggestions? Should work if your toolbar was created (and docked) back in your CMainFrame::OnCreate() method. ----- William Gower ...

Add a secondary administration group error c1041721
I have added a new administration group for a sub site in Japan and now I am getting the following error: The action could not be completed because the Microsoft Exchange Information Store service is unavailable. Operation: Retrieving mount information ID no: c1041721 Exchange Sytem Manger I get this error when I try and drill down to the new admin group to the First Storage Group. I only get this message on machines on my side of the network. If I remote to the mail server in the new site I am able to drill all the way down and see the mailboxes. I can also see our main site informat...

I configured news group in outlook 2003 but couldn't see it
I configured news group in outlook 2003 following the Help. I didn't finish the server configuration and see all news subscribed. However after I closed the configuration, I couldn't see the Go => News menu. What's worse is that the Customize Command panel, the "News" command is available in the "Go" Categories anymore. Appreciate if anyone can help! Ben wrote: > I configured news group in outlook 2003 following the Help. I didn't > finish > the server configuration and see all news subscribed. However after I > closed &g...

help #2
Hi, I need help with the following: I need to plot a 2-D column graph comparing the profit margin (y-axis) for the 3 items (x-axis) for Malaysia and Singapore, using the data below; Malaysia Item Mfr's Agent's Retailer's Selling GST Profit Total Cost Charge Markup Price Margin Lipstick 9.85 15.76 12.81 38.42 1.54 27.03 105.40 Toner 11.90 19.04 15.47 46.41 1.86 32.65 127.33 Cleanser 15.90 25.44 20.67 62.01 2.48 43.63 170.13 Total 37.65 60.24 48.95 146.84 5.87 103.31 402.86 Singapore Item Mfr's Agent's Retailer's Selling GST...

problem opening up program/file
Hello, All. I am having some trouble opening up Word. It was working okay until I loaded some fonts to FontBook. Okay, now I removed all my fonts but I still get this error message about corrupt fonts I have on my computer. When I open Word, I see the blue starting-up window: Word:Mac 2004. Right underneath the product ID #, I see that it is initiating... when it gets to the point "Optimizing font menu performance" I start getting this error windows popping up one after another. (E.g. The font M TimesSmallText is corrupt and should be removed.) I keep on clicking okay for a whole lot...

Bank One Download is not working
We are trying to download transaction data directly from Bank One and the file is not being recognized by Money - i.e. the Bank One website states that the download is complete, but Money does not recognize it. Note that a file is being saved on our hard drive but we can't figure out how to read it. It is entitled mnyimprt.exe-(numerous numbers and letters follow). In microsoft.public.money, Miki wrote: >We are trying to download transaction data directly from >Bank One and the file is not being recognized by Money - >i.e. the Bank One website states that the downloa...

How to show query parameters on an Excel page header or worksheet?
Is it possible to display query parameters on a page header or on a worksheet? I have a worksheet that uses a query to retrive data from an ODBC database. The Query prompts for the Start Date and End Date. I would like to be able to print the worksheet and display the Start Date and End Date the user typed in. Thank you for your assistance. ...

Tricky ComboBox / Filter query
Here's one On Sheet1, from A1:A2931 I have dates, every day from 01.01.02 to 31.12.2009 (A1="01/01/2002", A2="02/01/2002", etc). On a Userform I have 2 ComboBoxes: 1 for month, one for year. Is it possible to use these to filter Sheet1 and leave only those dates chosen in the dropdowns (eg, December 2004 only)? TIA Alan ...

Query question 12-11-07
I have a question that I hope you all can help me with. When I run a query, the results come back with multiple lines of data for an order because of multiple critereas in another field. Here is an example of the data returned to my query: Order Status A In Process A Sent B In Process C In Process I would like to see only the data for orders that have not been shipped, and totally exclude data for orders that have been sent. In the example above, I would like my query results to show Orders B and C, but no data for A since it has al...

Credit Card Downloading HELP!
I had 2005 and this worked ok but not on 2006. I download my 20 credit card transactions in QIF format. Money says it has 20 to review so I click that and they simply do not appear in the register! The the heck?!? I've tried quite a few things already to no avail. Deleted history and temp, deleted the statement and previous transactions/statements, repaired my money file a few times, tried to import in a different file but it just doesn't show up. Suggestions please...tia In microsoft.public.money, lucas.allen@gmail.com wrote: >I had 2005 and this worked ok but not on 2006....

Errror Help: character ' ', hexadecimal value 0x2 is illegal in xml documents
what does this error mean? how can i fix this? thanks, raj * raj wrote in microsoft.public.dotnet.xml: >what does this error mean? how can i fix this? You have an octet 0x02 or perhaps a character reference &#x02; in the document. This is not allowed in XML 1.0; the latter is allowed in XML 1.1 but your version of the .NET framework probably does not support that, and most likely you have not declared this version either. In other words, your document includes random binary data and is there- fore not an XML document. You have to correct the document before you can successfully...

mail gateway help
we have barracuda for our mail firewall but you can send mail out through it. Where do you normally point your exchange server to send mail out? the internet router the pix firewall ? "=?Utf-8?B?RWRkaWU=?=" <Eddie@discussions.microsoft.com> wrote in news:EF923D10-571B-42D0-85E3-D45CB9FC2A1C@microsoft.com: > we have barracuda for our mail firewall but you can send mail out > through it. Where do you normally point your exchange server to send > mail out? the internet router the pix firewall ? > What exactly is your question? What version of exchange are you us...

Where is "Help" located in 2007?
I cannot find the "Help" section in Word 2007. Where can I find it? Thank you! Click the question mark ? top right of the Word window or press F1 which is the universal help call. -- <>>< ><<> ><<> <>>< ><<> <>>< <>><<> Graham Mayor - Word MVP My web site www.gmayor.com Word MVP web site http://word.mvps.org <>>< ><<> ><<> <>>< ><<> <>>< <>><<> "Todd" <Todd@discussion...