Consulting

Results 1 to 6 of 6

Thread: SQL help, ms access

  1. #1

    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
    Last edited by lienlee; 05-31-2010 at 10:53 AM.

  2. #2
    VBAX Regular
    Joined
    Oct 2004
    Location
    Belgium
    Posts
    25
    Location
    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.

  3. #3
    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.

  4. #4
    VBAX Regular
    Joined
    Oct 2004
    Location
    Belgium
    Posts
    25
    Location
    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.

     
    select count(Name)
    from table where age = 29
    In your case, your first statement would work in an sql-server like this:

     
    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:

    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.

  5. #5
    VBAX Regular
    Joined
    Oct 2004
    Location
    Belgium
    Posts
    25
    Location
    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

  6. #6
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •