Apr 15 2005, 10:17 AM
Im making a distinct query on a table that lists aide names, on some of the m weve done 7 waves of surveys.
I can do a distinct query
select distinct (psaname)
The problem with thi is that while it does give me the unique aides , thats all it does give me.
How do i return all the "unique " aides but also show allthe other columns in the table , BUT only for the unique aides?
This doesnt work:
select distinct (psaname), *
Im sure its something im just not seeing,
Any help would be great .,
Thx in advance
Apr 15 2005, 12:27 PM
What you need is some sort of aggregation and a group by clause in your query. So, for example, if you need the sum of the scores in all the surveys each of the distinct aides has taken you have to write:
select psaname, sum(score) from psa_survey group by psa_name;
Essentially, you are only choosing some rows from your table by specifying distinct, the database does not know which row to pull all the details from for a given psaname. So, you either have to select all the rows (lose the distinct) or you have to group the results so that the database knows what to do with details on multiple rows for a given psa_name.
May 10 2005, 02:45 PM
Can you give a sample of how this would read?
I'm new to SQL and I don't know what to put first, etc., punctuation, etc.
Thanks, if you can.
May 10 2005, 09:03 PM
The statement I posted in my previous post is a complete SQL statement. You can read about the full syntax of SQL statements and several examples in the access help files. In general, an SQL statement consists of several clauses (many of which are optional). Punctuation is quite minimal and consists of commas to separate fields. Access will add a semicolon at the end of your SQL statement. If you have more questions after reading the help files and running some experiments on your database, post back and I will try to answer in as much detail as I can.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here