what is the syntax for "SELECT DISTINCT column-name?"

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm constructing an SQL and can not find the correct way to select rows not
repeating the value (DISTINCT).
 
Yeah, but it *must* be one of those cases where the Jorge was just too close
to the problem, and didn't think to read his own post.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
I'm constructing an SQL and can not find the correct way to select rows not
repeating the value (DISTINCT).
Click to expand...

My apologies for the jocund reply to the answer to your question. I
suspect the problem is that you were looking at the query grid, not
the SQL view; it's FAR from obvious in the grid! You need to either go
to SQL view and edit in the DISTINCT keyword, or right mouseclick the
background of the tables, view Properties, and set the Unique Values
property to True.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
Thank you for your response.

My problem was that I selected columns from SALES table and needed to select
the corresponding address from MAIL table.
MAIL has multiple rows with the same key. I was not able to concatenate
using DISTINCT ADDRESS from MAIL and kept getting Syntaz error.

I created two queries: One for the SALES and the other from the MAIL with a
subquery (based the SALES criteria).

I still will like to know the way to have colums from both tables in a
single query.
 
Jorge,

OK, I understand now.

You need to create a DISTINCT query on the MAIL table, and use that query in
place of the MAIL table. Then join it to the SALES table as you would any
other table.

SELECT sales.customerid, sales.field1, sales.field2, mymail.address
FROM sales
INNER JOIN (SELECT DISTINCT address, customerid FROM mail) AS mymail
ON mymail.customerid = sales.customerid

This of course, is a very simplistic example. If you were to advise us of
the columns in each table, and the specific columns you want to return, we
may be able to provide you with a more accurate resolution.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Microsoft Access 2003 VBA Programmer's Reference
http://www.wiley.com/WileyCDA/WileyTitle/productCd-0764559036.html
 
Back
Top