By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,164 Members | 1,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,164 IT Pros & Developers. It's quick & easy.

SQL Select without double entries

P: n/a
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
Share this Question
Share on Google+
2 Replies


P: n/a
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

P: n/a
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 discussion thread is closed

Replies have been disabled for this discussion.