| Author |
 Topic  |
|
|
brendita
Starting Member
38 Posts |
 Posted - 12/09/2005 : 11:39:19
|
This query returns part of what I need:
SELECT DISTINCT ci.Complex_Name
FROM CaseInformation c
INNER JOIN CommunityInformation ci
ON c.complex_ID = ci.Complex_ID
WHERE c.Date_entered BETWEEN '11/01/2005' AND '12/01/2005'
But I also need to return the address, city, etc for each of those Communities. But when I add those to the query, it returns multiple rows. How can I return more information without getting multiple rows for each distinct Community?
Thanks for your time! |
|
|
jhermiz
Flowing Fount of Yak Knowledge
USA
3564 Posts |
Posted - 12/09/2005 : 11:46:07
|
You can't...the query is doing exactly what you are telling it to do, distinct works on a row not a column basis.
Someone asked the same question yesterday. The reason it returns multiple rows is because the COMPLETE row is NOT distinct, adding those other fields makes it UNIQUE.
What you want to read over is GROUP BY vs. DISTINCT as well as aggregate functions.
Keeping the web experience alive -- http://www.web-impulse.com |
 |
|
|
brendita
Starting Member
38 Posts |
Posted - 12/09/2005 : 13:00:44
|
| I've been looking for some articles GROUP BY vs. DISTINCT but can't find any that solve my problem. Can you lead me to an article? |
|
|
|
jhermiz
Flowing Fount of Yak Knowledge
USA
3564 Posts |
Posted - 12/09/2005 : 13:40:32
|
But your question all and all doesn't make sense, you want a unique name from case information with multiple rows...
You cant do that because you have a one to many relationship, your problem is not a data problem, it is a problem with presentation. Dont worry about getting multiple rows of the same data as a data issue, worry about it as a presentation issue.
On a report you would create a group by for the case information and then in the details section you would place all your "many side" data. That way you ONLY see ONE Complex_name and all related data.
Sound right ?
Keeping the web experience alive -- http://www.web-impulse.com |
|
|
|
uuthanh
Starting Member
3 Posts |
Posted - 04/18/2007 : 17:18:29
|
Brendita,
Did you figure out how to do it? I got similar problem.
I googled all day, but found nothing beside your post.
I tried to extract out: unique customer records from a trouble ticket table. Simple like this:
TicketID (primary)
CustomerID
CustomerName
CustomerAddress
CustomerCity
CustomerState
CustomerZip
CustomerPhone
CustomerRate
If I use DISTINCT then, I can only get back 1 column (CustomerID)
If I use GROUP BY, then I have to group by all the fields above in order to select all. I wish I can GROUP BY only CustomerID, but it let me select all above fields.
How about you, jhermiz? Can you help?
Thanks so much for any clue. |
|
|
|
eltombro
Starting Member
United Kingdom
1 Posts |
Posted - 04/30/2007 : 11:08:55
|
Finally I've found someone with the same problem!
I'm trying the following:
SELECT DISTINCT value1, value2 FROM table WHERE id = ...
Basically I'm trying to retrieve values where 2 columns are distinct. Unfortunately I can't seem to be able to get the second value, value2, and assign it to an ASP variable from my recordset...
Correction: I debugged my query by finally finding out how to run it in Access 2007. Nothing wrong with the query erm in fact it was my subsequent query which was flawed... I'd got the wrong column name for an id column. 
To conclude, selecting distinct values will return them all (distinctly) and are assignable as variables from a recordset. Sorry!  |
Edited by - eltombro on 04/30/2007 12:08:57 |
|
|
| |
Topic |
|
Using DISTINCT with multiple columns?
Forum Locked
