Thank you to both John and Bill for taking the time to help me. I did what both of you wrote. However, what confuses me is if I add more criteria into the query, for some reasons, the list becomes longer than the original Max(DateInput).
For example, If I just group the "Ticker" and Max (DateInput), I would have 2 records. But if I add the 3rd criteria such as Name, or SharesOwned, then I get more than 2 records.
My question is should I stick with only 2 criteria for the first queries and use it as a base to run another query if I want to get more related data?
Again, much grateful for your answers!
Tim
--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Tim-
>
> SELECT Ticker, DateInput, Value, MemberName
> FROM MyTable
> WHERE MemberName = "John"
> AND DateInput =
> (SELECT Max(DateInput) From MyTable As T2
> WHERE T2.Ticker = MyTable.Ticker AND
> T2.MemberName = MyTable.MemberName)
>
> John Viescas, Author
> Microsoft Access 2010 Inside Out
> Microsoft Access 2007 Inside Out
> Microsoft Access 2003 Inside Out
> Building Microsoft Access Applications
> SQL Queries for Mere Mortals
> http://www.viescas.com/
> (Paris, France)
>
>
>
> -----Original Message-----
> From: timdbui <timdbui@...>
> Reply-To: <MS_Access_Professionals@yahoogroups.com>
> Date: Tuesday, October 23, 2012 5:51 PM
> To: <MS_Access_Professionals@yahoogroups.com>
> Subject: [MS_AccessPros] Max Query Question
>
> Hi Bill,
>
> Thanks so much for your last reply! It was very helpful. Can you help me
> with another query? I have a table that has 4 columns: A:Ticker,
> B:DateInput, C:Value, D:MemberName.
>
> Say, the members put in different values for XYZ on different dates:
>
> 1/1/2012, XYY, 10, John
> 2/2/2012, XYZ, 12, John
> 5/3/2012, XYZ, 14, John
> 2/3/2012, ABC, 20, Bill
> 5/3/2012, ABC, 22, Bill
>
>
>
> How can I create a query in Access to get the latest value that John put
> in for XYZ. I want to have only 1 answer for each stock as the result of
> the query, which are:
>
> 5/3/2012, XYZ, 14, John
> 5/3/2012, ABC, 22, Bill
>
>
> Thanks again for your helps! Tim
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (4) |
Tidak ada komentar:
Posting Komentar