Selecting Only the First Row in a Query.

[Marcus]

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
******

[Trevor Best]

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]

[Nunya Biznas]

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.

[Trevor Best]

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]

[Marcus]

How does one use first in Access? I tried

SELECT FIRST Name Address FROM Table .....

and it doesn't work.

Marcus
*********

[Trevor Best]

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]

[Marcus]

I know this is not an Access question, but does anyone know the
equivalent of the "First" aggregate in Oracle?

Marcus
********

[Anne Nolan]

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
********

[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
*********