469,326 Members | 1,500 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,326 developers. It's quick & easy.

Selecting Only the First Row in a Query.


Say a number of people have 2 or 3 addresses each in a query result.
How do I select only the first address displayed for each person.

For example:

John Taylor 33 Dundas Ave.
John Taylor 55 Shane Ave.
John Taylor 786 Edward St.
Ted Charles 785 Bloor St.
Ted Charles 90 New York Ave.

All I want is the first address that is displayed for each person which
is:

John Taylor 33 Dundas Ave.
Ted Charles 785 Bloor St.

Wondering if this is possible in Access?

Marcus
******

Nov 13 '05 #1
8 59469
Marcus wrote:
Say a number of people have 2 or 3 addresses each in a query result.
How do I select only the first address displayed for each person.

For example:

John Taylor 33 Dundas Ave.
John Taylor 55 Shane Ave.
John Taylor 786 Edward St.
Ted Charles 785 Bloor St.
Ted Charles 90 New York Ave.

All I want is the first address that is displayed for each person which
is:

John Taylor 33 Dundas Ave.
Ted Charles 785 Bloor St.

Wondering if this is possible in Access?

Marcus
******


Group by name, use first of address.

--
[OO=00=OO]
Nov 13 '05 #2
Just be careful with this. I have found that in Access 2002, 'first'
returns the first record as they are stored in the *table*. I tried an
intermediate query to sort them the way I wanted and 'first' still went
back to the original table to determine which record was first.

Nov 13 '05 #3
Nunya Biznas wrote:
Just be careful with this. I have found that in Access 2002, 'first'
returns the first record as they are stored in the *table*. I tried an
intermediate query to sort them the way I wanted and 'first' still went
back to the original table to determine which record was first.


It's a lottery, first isn't ANSI SQL AFAIK and to me it says "just pull
up the first thing you find".

If there's a particular address you want for a person then that address
should be able to be identified, e.g. in my suppliers database I have
many addresses and they each have a type like Head Office, Sales, etc so
if a particular address is required then that is part of the criteria of
the query, it's not left to chance. :-)

--
[OO=00=OO]
Nov 13 '05 #4
How does one use first in Access? I tried

SELECT FIRST Name Address FROM Table .....

and it doesn't work.

Marcus
*********

Nov 13 '05 #5
Marcus wrote:
How does one use first in Access? I tried

SELECT FIRST Name Address FROM Table .....

and it doesn't work.


Like all aggregate functions, e.g.

Select First(Name) from table.

Try it in the query grid, you'll see all the options in there.

--
[OO=00=OO]
Nov 13 '05 #6
I know this is not an Access question, but does anyone know the
equivalent of the "First" aggregate in Oracle?

Marcus
********

Nov 13 '05 #7
Perhaps

SELECT * FROM TABLE WHERE ROWNUM=1

will do what you want.

Anne

"Marcus" <to*******@yahoo.ca> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...
I know this is not an Access question, but does anyone know the
equivalent of the "First" aggregate in Oracle?

Marcus
********

Nov 13 '05 #8
ja
Try this:

SELECT top 1 table.*
FROM table;


On 25 May 2005 10:09:39 -0700, "Marcus" <to*******@yahoo.ca> wrote:
How does one use first in Access? I tried

SELECT FIRST Name Address FROM Table .....

and it doesn't work.

Marcus
*********


Nov 13 '05 #9

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

2 posts views Thread by karthik | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by harlem98 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.