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 5096
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]","YourTabl e");
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******@examp le.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]","YourTabl e");
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.Con tractStart) 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**********@ho tmail.com (neptune) wrote in
news:c5******** *************** ***@posting.goo gle.com: 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 errors).
The GROUP BY specifies a column that does exist in my db (and is also the
only field that is select from the db).
Where am I going wrong? Because my select uses hashes (which I provide with
|
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 I put it in there, I will get no
duplicates (because it is the identity field). The whole point of
this is to find dups.
Thanks for any help.
|
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 combination
of them all.
What worked for me was the following:
1. Put a Break Point in MFC source file DLGDATA.CPP on line 338 which
is:
TRACE0("Warning: skipping non-radio button in group.\n"); (Copyright
|
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 summary of money for
all groups the report footer. When there is at least one record with
money >0, group and report footers are appropriate and look OK.
When all records are with 0 money, footers look silly. I’d like
not to print them. I have placed...
|
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 know how to
count the groups, then hide the report footer if there's only one
group, but my problem is I want to hide the group footer (sub-total),
not the report footer (because the report footer references what the
grand total is for, which is...
| |
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 GROUP BY it is not giving me the
desired effect that I would like?
For example, let's say I would like to group by the first 5 columns (i.e.
V1,V2,V3,V4,V5) and let's say the first 3 rows of my view or table contain
the values 1,2,3,4,5 for...
|
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 looking at the child
node sequence (see sample below). note that when i do an xsl-message on
the group-by expression inside the for-each-group, it has exactly what
i'd like to group by listed, but i guess it doesn't like to have a
sequence of...
|
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 first name or last name) belong
to the same group.
John Smith 1234
Smith John 1234
S John 1234
J Smith 1234
John Smith and Smith John falls in the same group Number as long as
|
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 ITEM_SEQ NR_ITEMS PERSONS_SEQ
1 A 2 aa
1 A 2 bb
|
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 Users groups.
I see this problem happen in Internet Explorer 6 and 7 under windows XP.
Under the Users group the component fails when it tries to call for the
first time a method in that component then the JavaScript throws the
following...
|
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, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
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 Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
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, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
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 presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |