A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

"First" function returning garbage - even after sorting previous q



 
 
Thread Tools Display Modes
  #1  
Old February 9th, 2005, 12:15 AM
mrselfdestruct
external usenet poster
 
Posts: n/a
Default "First" function returning garbage - even after sorting previous q

Hi,

I'm running a database with some medical information in it where I need to
identify the first malignant diagnoses for each patient. I used a query to
filter the data, leaving only entries that are listed as malignant. In those
queries I ordered the data by patient number and then date of procedure.

When I run a third query, grouping by patient number and using the "first"
function for the rest of the fields, the query does NOT return the first
fields. The previous query that it's working from has already sorted the
data, though! Why is it still picking arbitrary entries? How do I get around
this?

Thanks for any help. I hope I explained it alright.
  #2  
Old February 9th, 2005, 05:06 AM
MGFoster
external usenet poster
 
Posts: n/a
Default

mrselfdestruct wrote:
Hi,

I'm running a database with some medical information in it where I need to
identify the first malignant diagnoses for each patient. I used a query to
filter the data, leaving only entries that are listed as malignant. In those
queries I ordered the data by patient number and then date of procedure.

When I run a third query, grouping by patient number and using the "first"
function for the rest of the fields, the query does NOT return the first
fields. The previous query that it's working from has already sorted the
data, though! Why is it still picking arbitrary entries? How do I get around
this?

Thanks for any help. I hope I explained it alright.


Don't use FRIST() or LAST(). In your case use Min(date_column) for the
first date and Max(date_column) for the last date.

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)
  #3  
Old February 9th, 2005, 08:45 PM
mrselfdestruct
external usenet poster
 
Posts: n/a
Default

Thanks, man. That's what I ended up doing. Still, I find that the first and
last functions need some serious reworking. Being that inconsistent just
doesn't make any sense.

"MGFoster" wrote:
Don't use FRIST() or LAST(). In your case use Min(date_column) for the
first date and Max(date_column) for the last date.

--
MGFoster:::mgf00 at earthlink decimal-point net
Oakland, CA (USA)

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Fill-in field - returning to a previous field. Lorrie Mailmerge 1 December 13th, 2004 08:39 PM
Nesting Address function within Index function mserber General Discussion 3 November 5th, 2004 03:01 AM
Function to calculate a team's form over previous 3 games only Jock General Discussion 1 October 7th, 2004 06:37 AM
Why this is not returning to the previous control Tru Dixon General Discussion 3 August 12th, 2004 12:02 PM


All times are GMT +1. The time now is 01:09 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.