I have seen that there are various posts regarding this topic, but unfortunately, none of them have answered my question.
I can get a distinct count to work on one table and even two or more tables when only one column from a table is required, eg
from TableA INNER JOIN (select distinct(a03) from TableB) as TableB on TableA.l03 = TableB.a03);
However, it doesn't seem to work when I want more than one column returned from the table
from TableA INNER JOIN (select distinct(a03), a05 from TableB) as TableB on TableA.l03 = TableB.a03);
I have tried using two TableB tables but that doesn't work - I still get all the duplicate rows. Any ideas on how to solve this would be sooooo helpful. I am losing the will to live with this. So wish we used lovely Oracle
Thanks for your reply. I have tried to count(*) method before to no avail. I acutally need to join quite a few tables together eventually, but am trying to break it down so that I can see where it is needed. TableA will be needed once I get this distinct query working.
In Oracle a simple count(distinct a03) would work, without the need for subqueries.
You can use that syntax in Access too however you need to set the ANSI compatibility level. If you have lots of other queries, then this will not be an option as it can break other stuff.
However, that would be invalid in your query anyway.
Please can you confirm or correct this description of what you want to do:
You want to get a count of distinct values for a03 used for each value of a05 in tableB, and join this result to some other tables in your database.
What I have is a list of Students L03 who may have 1 or more courses A05, but I only want to do a count of the distinct number of students. I will need field A05 to link to another table that I will require later on. I ultimately want a distinct count of the students without being concerned how many courses they are on.
Hope this makes sense. I'm confusing myself as I'm typing!
You cannot use the DISTINCT predicate in this way. DISTINCT means that everyting you select must be different. If you have more than one column in your SELECT clause it probably means that every row is different unless two or more rows contain identical data.
ID Name Class
Row1: 1 Mike English1
Row2: 2 John Math2
Row3: 3 John Sciences1
SELECT DISTINCT Name, Class FROM Table1 WHERE (((Name) = "john"));
is the same as
SELECT Name, Class FROM Table1 WHERE (((Name) = "john"));
Both will return Name and Class from Row2 and Row3 because
"John Math2" is different from "John Sciences1"
You have to create a first query (the DISTINCT one) that selects only one row with the DISTINCT predicate, then join this query to another one that returns the values you are looking for.