473,327 Members | 1,979 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

SQL Select without double entries

Hello,

I am having a table looking like this:

-------------------------------------
PersID | Name | RoomID |
-------------------------------------

Some of the data rows have identical PersID and Name, but differ in
the RoomID.

Now I want to create a select statement, which gets only the first
row, if there are more
than one row where PersID and Name are the same.

E.g.:

-------------------------------------
PersID | Name | RoomID |
-------------------------------------
1 |John | 234
2 |Alice | 345
2 |Alice | 456
3 |Bob | 789

The result table of my select statement should be:
-------------------------------------
PersID | Name | RoomID |
-------------------------------------
1 |John | 234
2 |Alice | 345
3 |Bob | 789
How do I do that? I

Regards,
Martin
Dec 10 '07 #1
2 2089
In query design view, depress the Total button on the toolbar.
Access adds a Total row to the design grid.

In the Total row under PersID and Name, choose Group By.
Under RoomID, choose Min.

Hopefully you don't really have a field called "Name." Since most things in
Access have a Name property, Access will get confused and give you the wrong
info. For example, if you use a text box on a form or report, it may
actually show the name of the report instead of the contents of the Name
field.

Name and Date are the most common problem field names. For a list of the
others, see:
http://allenbrowne.com/AppIssueBadWord.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ma**********@googlemail.comwrote in message
news:94**********************************@o6g2000h sd.googlegroups.com...
Hello,

I am having a table looking like this:

-------------------------------------
PersID | Name | RoomID |
-------------------------------------

Some of the data rows have identical PersID and Name, but differ in
the RoomID.

Now I want to create a select statement, which gets only the first
row, if there are more
than one row where PersID and Name are the same.

E.g.:

-------------------------------------
PersID | Name | RoomID |
-------------------------------------
1 |John | 234
2 |Alice | 345
2 |Alice | 456
3 |Bob | 789

The result table of my select statement should be:
-------------------------------------
PersID | Name | RoomID |
-------------------------------------
1 |John | 234
2 |Alice | 345
3 |Bob | 789
How do I do that? I

Regards,
Martin
Dec 10 '07 #2
Hello Allen,

thanks for your answer.

On 10 Dez., 12:48, "Allen Browne" <AllenBro...@SeeSig.Invalidwrote:
Hopefully you don't really have a field called "Name." Since most things in
Access have a Name property, Access will get confused and give you the wrong
info.
Of course not! This was just a simple example to make my problem
clear.
Regards,

Martin
Dec 10 '07 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: callmebill | last post by:
I'm relatively new to javascript, and I'm trying to decide whether the following (and if so, clues on how to do it): I'd like to create two HTML multiple-select boxes. The first would be a list...
5
by: 73blazer | last post by:
Hello, I'm looking for a SQL syntax to put a variable name into the from clause. Specifically I have a colume in a table that is table name, and I want to use that result in the from clause in a...
2
by: marco | last post by:
Dear List, as it seems, MS SQL as used in Access does not allow a select INTO within a UNION query. Also, it seems that a UNION query can not be used as a subquery. Maybe my (simplified)...
3
by: John E. Fox | last post by:
Dear All, Is there a nice way to select every entry in a list box without having to click and drag across every line Thanks John Fox
2
by: unabogie | last post by:
I have a table with entries tied to a membership database. The problem is that I want to select a limit of sixteen entries per member, per day, where some members have 16+ entries per day. I...
3
by: Beholder | last post by:
I hope that someone can help me with the following: Short background explenation: I have a shrfepoint page (newform.aspx) in a item list. On this page is a lookup column that displays a lookup...
9
by: P3Eddie | last post by:
Hello all! I don't know if this can even be done, but I'm sure you will either help or suggest another avenue to accomplish the same. My problem may be a simple find duplicates / do something...
7
by: Brian | last post by:
I'm trying to run a select max() on a primary key/unique/non-identity column then + 1, all while running an insert into.. select statement. For some reason, the select max isn't quite doing the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

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.