473,718 Members | 2,094 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 63480
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*******@yaho o.ca> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.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*******@yaho o.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1218
by: Stew | last post by:
Hi, I have a table to log sessions and user (connect_id) id's, start time etc - see below +---------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------------+-------------+------+-----+---------+-------+ | connect_id | varchar(12) | | | | | | session_id | varchar(32) | | | | |
2
31521
by: karthik | last post by:
Hi, I having problems returning only the first row of data for each GROUP for the query below. Can someone please help? Thanks. SELECT bid, xact_date, xact_time FROM badge_history bh GROUP BY bid, xact_date, xact_time ORDER BY bid, xact_date DESC, xact_time DESC So if the data returned by the above query is:
2
18697
by: Dom | last post by:
I need to run a query based on a query in Access. The second query has a number of conditions which all work well, but there is one more contition I need to set to make it run properly. the first query returns a number of data items, consisting, among other fields, of a Company_ID and a Rating_Date. For most companies, the latest value of Rating_Date is equal to a value in a seperate table (tblVariables) which logs the last time a...
8
2225
by: Henrik Larsson | last post by:
Hi, I need help with selecting the following rows from a table looking like this: ID IP Query 1 -> 1 2.2.2.2 (ie first IP 1 1.1.1.1 <- Query 2 for each ID) 1 3.3.3.3 (ie second IP for each ID) -> 2 5.5.5.5 2 6.6.6.6 <-
2
2754
by: colm | last post by:
I have a string surname and want to select the first two letters to use in a code field how can i check if the second item in string is an apostrophe and filter it out ' in names such as o'brien and use the third item in string so i get ob in other words if surname = brown code =br but if surname = o'brien code = ob colm
6
2836
by: Michael R | last post by:
Please help me with this. I have a form in which I would like to present results from a CrossTab query in a subform. I don't need the query to be bounded to a master field. Also, I have a parameter in the query as one of the main form controls. Please look at the query: PARAMETERS !! Long; TRANSFORM Sum(LoansSumAmountAndYearAndCity.Amount) AS SumOfAmount SELECT LoansSumAmountAndYearAndCity.Customers.Id,...
9
2749
by: Boblert | last post by:
How do I select the first word of a variable? name = raw_input("What's your full name? ") print "Hey, %s, how you doing?" % name So say I only wanted it to print the first name? How would I do this not knowing the amount of letters in it?
3
3786
by: khursheed | last post by:
Dears 1- I want to make a query to select first n records from a table. The table has no field like autonumber. 2- How can I use a variable in query. for example n=100 n is a variable SELECT * FROM table WHERE numericfield>n
2
1651
by: jgentes | last post by:
I have a query that I would like to use a union statement in to grab the number of replies to a specific thread. The initial topic thread is in a different table, which I am grabbing in the initial query... I would prefer to do this in the single query, however I supposed I could do a separate loops and grab the number of replies with a totally distinct query SELECT id,title,category,message FROM forum UNION(SELECT count(msg_id) FROM...
1
1770
by: ziycon | last post by:
Ok, i got the main query working, my issue is that the GROUP BY isn't working, its should only show one of each location: SELECT _teamdb_country.name FROM _teamdb, _teamdb_country WHERE _teamdb.country NOT IN (0) AND _teamdb.country = _teamdb_country.id ORDER BY _teamdb_country.name ASC It should look like: France Germany UK But its outputting this: France Germany
0
8827
marktang
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...
0
8724
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 effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9358
Oralloy
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, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9208
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9121
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,...
0
9053
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
6654
isladogs
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...
0
5971
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();...
3
2123
bsmnconsultancy
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.