I must be missing something obvious. I have 3 fields and sample data.
[Account#] [Contract#] [ContractStart]
7890 26592 7/15/2003
7890 13645 10/6/1999
7890 58741 6/21/1992
I want to select only the most recent record. It is sorted descending
by [ContractStart] in the query and subquery. In the query Total, I
select Group By [Account#], First [Contract#] and First
[ContractStart]. For some reason I am getting record 2, not 1. If I
select Last instead of First, I still get record 2. What am I not
thinking about? 6 5082
On 30 Apr 2004 12:26:13 -0700, neptune wrote: I must be missing something obvious. I have 3 fields and sample data.
[Account#] [Contract#] [ContractStart] 7890 26592 7/15/2003 7890 13645 10/6/1999 7890 58741 6/21/1992
I want to select only the most recent record. It is sorted descending by [ContractStart] in the query and subquery. In the query Total, I select Group By [Account#], First [Contract#] and First [ContractStart]. For some reason I am getting record 2, not 1. If I select Last instead of First, I still get record 2. What am I not thinking about?
Try:
Select YourTable.* from YourTable
Where [ContractStart] = DMax("[ContractStart]","YourTable");
You should get only the record(s) with the last date entered.
--
Fred
Please only reply to this newsgroup.
I do not reply to personal email.
neptune wrote: I must be missing something obvious. I have 3 fields and sample data.
[Account#] [Contract#] [ContractStart] 7890 26592 7/15/2003 7890 13645 10/6/1999 7890 58741 6/21/1992
I want to select only the most recent record. It is sorted descending by [ContractStart] in the query and subquery. In the query Total, I select Group By [Account#], First [Contract#] and First [ContractStart]. For some reason I am getting record 2, not 1. If I select Last instead of First, I still get record 2. What am I not thinking about?
On Fri, 30 Apr 2004 21:45:24 GMT, fredg <fg******@example.invalid> wrote: On 30 Apr 2004 12:26:13 -0700, neptune wrote:
I must be missing something obvious. I have 3 fields and sample data.
[Account#] [Contract#] [ContractStart] 7890 26592 7/15/2003 7890 13645 10/6/1999 7890 58741 6/21/1992
I want to select only the most recent record. It is sorted descending by [ContractStart] in the query and subquery. In the query Total, I select Group By [Account#], First [Contract#] and First [ContractStart]. For some reason I am getting record 2, not 1. If I select Last instead of First, I still get record 2. What am I not thinking about?
Try: Select YourTable.* from YourTable Where [ContractStart] = DMax("[ContractStart]","YourTable");
You should get only the record(s) with the last date entered.
Technically, that's almost a valid answer, but calling domain lookup functions
is very inefficient, particularly from within a query. I'd use a subquery
instead.
Select YourTable.* from YourTable
Where [ContractStart] = (SELECT MAX(LastRec.ContractStart) FROM YourTable As
LastRec WHERE LastRec.[Account#] = YourTable.[Account#]);
neptune wrote: I must be missing something obvious. I have 3 fields and sample data.
[Account#] [Contract#] [ContractStart] 7890 26592 7/15/2003 7890 13645 10/6/1999 7890 58741 6/21/1992
I want to select only the most recent record. It is sorted descending by [ContractStart] in the query and subquery. In the query Total, I select Group By [Account#], First [Contract#] and First [ContractStart]. For some reason I am getting record 2, not 1. If I select Last instead of First, I still get record 2. What am I not thinking about?
I did the same thing as you. I had to enter the records in a different
order than the order presented above to get the results you received.
Since I assume you have more than three records, this is what I did. I
created a Totals query on account number and contract start date.
Grouped on Account, Max on Start (no need to sort desc). I called it
query1.
I then created a new query, Query2, where I added Query1 and the table.
Then I drew relation lines between the AccountNumber and Date fields
for both. Now I get the correct results.
Your sort descending in the first query is worth squawt.
First/Last/Group don't help. bs**********@hotmail.com (neptune) wrote in
news:c5**************************@posting.google.c om: I must be missing something obvious. I have 3 fields and sample data.
[Account#] [Contract#] [ContractStart] 7890 26592 7/15/2003 7890 13645 10/6/1999 7890 58741 6/21/1992
I want to select only the most recent record. It is sorted descending by [ContractStart] in the query and subquery. In the query Total, I select Group By [Account#], First [Contract#] and First [ContractStart]. For some reason I am getting record 2, not 1. If I select Last instead of First, I still get record 2. What am I not thinking about?
"FIRST" does not mean what you seem to think it means. FIRST means
the first record in the set of records, and is dependent on sort
order. Indeed, I've given up using it because the results seem
semi-random. Using a TOP 1 query is more reliable, but has its own
limitations.
The reason you're getting record 2 might be because of the contract
number, which is the lowest of them all. As I said, I don't fully
understand the purpose or behavior of FIRST, and so, I avoid using
it.
--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
> Since I assume you have more than three records, this is what I did. I created a Totals query on account number and contract start date. Grouped on Account, Max on Start (no need to sort desc). I called it query1.
I then created a new query, Query2, where I added Query1 and the table. Then I drew relation lines between the AccountNumber and Date fields for both. Now I get the correct results.
I do have more than 3 records with many account#s, so this was the
only way that worked. Thanks. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Randell D. |
last post by:
Folks,
I have a SELECT that returns with multiple records - It works when I have a
LIMIT clause but when I try to include a GROUP BY clause, the select returns
nothing (ie no records, no...
|
by: Robby McGehee |
last post by:
I need this to work:
SELECT FROM WITH (NOLOCK) where ='a' GROUP BY , HAVING COUNT () > 1
The problem is that I get an error that needs to be in the GROUP
BY clause or aggregate function. if...
|
by: Rick |
last post by:
After being frustrated with this issue on several occasions I think I
found the secret sauce for solving the issue after reading a few
different messages about what others thought and trying a...
|
by: Galina |
last post by:
Hello
I have a report, which lists records. Each record has money paid
field. Money paid can be 0 or not 0. I calculate and print summary of
money for a group in the group footer, as well as...
|
by: Darin |
last post by:
I have a report that sub-totals on a group, then grand-totals at the
report footer. If there's only one group, the sub-total and grand
total are redundant, so I only want to show one of them. I...
|
by: Roy Gourgi |
last post by:
Hi,
Is there a way to group rows by some columns in such a way that I can
clearly see them in a distinguished fashion when I look at a view or table.
This is my code below but when I try using...
|
by: jon|k |
last post by:
hi all--
i need to do a transformation that removes duplicates (among other
things). to accomplish that, i'm trying to use for-each-group, but it
doesn't work. i need to select for duplicates by...
|
by: jacob.dba |
last post by:
I have a table with first name, last name, SSN(social security number)
and other columns.
I want to assign group number according to this business logic.
1. Records with equal SSN and (similar...
|
by: Rudolf Bargholz |
last post by:
Perhaps some kind soul could help me out with an SQL I have been trying all
day to get to work, where one colum is just not summing up the way I want it
to.
I have the following data
GRP_SEQ ...
|
by: =?Utf-8?B?V2lsbWVyIEhlcm5hbmRleg==?= |
last post by:
An ActiveX component created using ATL under Visual Studio 2003, and it is
used in a ASP, runs perfectly under administrator or power users group,
however this same object does not run under the...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |