SQL help, ms access

SELECT count (distinct Subject_Area.Subject_Area_Name) as "number", Subject_Domain.Subject_Domain_Name
FROM Data_Model, (Subject_Domain INNER JOIN Subject_Area ON Subject_Domain.Subject_Domain_ID = Subject_Area.Subject_Domain_ID) INNER JOIN Conceptual_Entity ON Subject_Area.Subject_Area_ID = Conceptual_Entity.Subject_Area_ID


it keeps saying i have a syntax error(missing operator) in query expression 'count(distinct subject_area.subject_Area_name)
im trying to count each distinct subject area name for each subject domain name

Hi,

first remark. If you use a count, sum, avg or other function you must have a group by part in your sql-statement.

I'm familiar with SQL but your sql-statement doesn't make sence. I don't understand your statement.

You have tables: data_Model, Subject_Domain, Subject_Area, ...
Perhaps it's better to give the tables-structure first. How many tables are you trying to join?

Globally I think you just need a group by and a count, the distinct won't be necessary.

Quote:

---

Originally Posted by Gollem

Hi,

first remark. If you use a count, sum, avg or other function you must have a group by part in your sql-statement.

I'm familiar with SQL but your sql-statement doesn't make sence. I don't understand your statement.

You have tables: data_Model, Subject_Domain, Subject_Area, ...
Perhaps it's better to give the tables-structure first. How many tables are you trying to join?

Globally I think you just need a group by and a count, the distinct won't be necessary.

---

[vba]SELECT Subject_Domain.Subject_Domain_Name, Subject_Area.Subject_Area_Name, Count(Distinct [Subject_Area.Subject_Area_Name]) AS CountOfSubject_Area_Name
FROM Data_Model, Subject_Domain INNER JOIN (Subject_Area INNER JOIN Conceptual_Entity ON Subject_Area.Subject_Area_ID = Conceptual_Entity.Subject_Area_ID) ON Subject_Domain.Subject_Domain_ID = Subject_Area.Subject_Domain_ID
GROUP BY Subject_Domain.Subject_Domain_Name, Subject_Area.Subject_Area_Name
ORDER BY Subject_Domain.Subject_Domain_Name DESC;
[/vba]
im looking for the count of distinct subject area names.

im inner joining subject domain subject area and conceptual entity
data model is not inner joined to anything

when i count it counts everything. I just wish to count distinct names only.

Hi,

I understand what you are trying to do, I did some research and testing.
I've learned something, your combination distinct and count on a field doesn't seem to work in Access but in an sql-server database it seems to work.

I've learned something.
I've said that with a count you always need a group by ... this is wrong apparently. If you only want to count some records using 1 field it's no problem. ex.

Code:

---


select count(Name)
from table where age = 29

---

In your case, your first statement would work in an sql-server like this:

Code:

---


SELECT count (distinct Subject_Area.Subject_Area_Name) as number,
FROM Data_Model, (Subject_Domain INNER JOIN Subject_Area ON Subject_Domain.Subject_Domain_ID = Subject_Area.Subject_Domain

---

or with the domain_name=> number of distinct areas for each domain:

Code:

---

SELECT count (distinct Subject_Area.Subject_Area_Name) as number, Subject_Domain.Subject_Domain_Name
FROM Data_Model, (Subject_Domain INNER JOIN Subject_Area ON Subject_Domain.Subject_Domain_ID = Subject_Area.Subject_Domain
group by Subject_Domain.Subject_Domain_Name

---

Are you using access?

If this feedback helps to solve the issue, let me know the outcome. Interesting case.

Hi,

I found a solution for access. But I'm not sure if it will solve your problem.
You can use an extra query. 1 query : select distinct .....
Then in your new query select count(*) from query

See this thread: http://www.keyongtech.com/83635-sql-...count-distinct

Quote:

---

Originally Posted by Gollem

Hi,

I found a solution for access. But I'm not sure if it will solve your problem.
You can use an extra query. 1 query : select distinct .....
Then in your new query select count(*) from query

See this thread: http://www.keyongtech.com/83635-sql-...count-distinct

---

Hi,

This way only counts the number of distinct items. It doesnt count the number of each distinct items.

For example

Business can have 4 marketing teams, 3 accounting teams
itll only count that theres 2 different types of teams. not how many marketing eachs and accounting teams.

I've solved my problem though