Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    May 2006
    Posts
    386

    Unanswered: Need help on How to Calculate ID number in a query?

    Dear All, I have a query which is based on two tables. One table is called tbl_SpecialitiesList and the other table is called 'tbl_employees'. Now, most employees have the same Specialities which means that there are more than one employees that have the same specialities e.g. 10 employees are Nurses, 21 employees are carpenters, I want to count the total number of Employees that have the same Specialities e.g. how many doctors, how many carpenters and so on. I want to do this in a query then run a report based on the query. I have tried using
    Sum
    and total but it did not work for some reason. I dont know what I am doing wrong.

    Any advice and help would be much appreciated.
    Emi-UK
    Love begets Love, Help Begets Help

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    select s.col_specialty
         , count(*) as how_many_employees
      from tbl_SpecialitiesList as s
    inner
      join tbl_employees as e
        on e.col_specialty = s.col_specialty
    group
        by s.col_specialty
    i used the col_ prefix on the columns because you used the tbl_ prefix on tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    May 2006
    Posts
    386
    Hi there, Thank you for your kind response. I have tried your instructions but it just did not work. could you please confirm what do ' s ' and e' stand for? I assumed that ' s' would be specialty and e would be employee but even then i tried it but it did not work. I will apprecaite your further assistance and advice.
    Emi-UK
    Love begets Love, Help Begets Help

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    s and e are table aliases, used to make writing—and reading—the query easier

    i did the best under the circumstances -- you did not supply very much information about your columns or tables or how they are related, so i guessed

    if my guesses were wrong, whose fault is that, eh

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    May 2006
    Posts
    386
    My apologies, you are right, i failed to provide enough information. I have attached the tables and queries that I have put together and my aim is to have an additional column giving me the total number of Employees for each speciality e.g. 2 doctors 3 consultants etc. I hope this will enable you to help me further. Thank you for your time.
    Attached Files Attached Files
    Emi-UK
    Love begets Love, Help Begets Help

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    perhaps that may benefit someone else who is willing to help you, but as for me, i can't open it

    good luck
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    May 2006
    Posts
    386
    oh, i dont know why you couldn't open it. Just in case you could spare few more minutes, I have attached it once again.
    Attached Files Attached Files
    Emi-UK
    Love begets Love, Help Begets Help

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the reason i can't open it is because i don't have the right version of msaccess

    i think the shoe is on the wrong foot -- you should not be making people do work to get to the point where they can help you, you should be doing all the work to make it easy for us
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    May 2006
    Posts
    386
    I am sorry for any inconvenience this might have caused. I did not mean to insist. Thank you for your assistance and time.
    Emi-UK
    Love begets Love, Help Begets Help

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    If you let us know the relevant table and column names I've sure we can muddle through. Rudy can write SQL in his noggin don't cha know

  11. #11
    Join Date
    May 2006
    Posts
    386
    Hi pootle, Thank you for your response. Ok, below is the current structure I have. I normally view queries in design view as I am not very good with sql. however, I have copied the codes from its sql views which is as follows:

    SELECT tbl_employees.employeeID, tbl_employees.speciality1ID, tbl_employees.speciality2ID, tbl_employees.employeeID, speciality.id AS id
    FROM [Tbl_SpeicalitiesList] INNER JOIN tbl_employees ON tbl_SpecialitiesList.id = tbl_employees.Speciality1ID
    ORDER BY tbl_employees.employeeID DESC;
    Basically I need to have a separate column to show number of employees per speciality e.g. 10 doctors or 2 nurses.

    I hope this will enable you to help me.
    Emi-UK
    Love begets Love, Help Begets Help

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT s.id
         , ( SELECT COUNT(*)
               FROM tbl_employees
              WHERE speciality1ID = s.id ) as specialty_1_count
         , ( SELECT COUNT(*)
               FROM tbl_employees
              WHERE speciality2ID = s.id ) as specialty_2_count
      FROM [Tbl_SpecialitiesList] AS s
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    try this...
    Code:
    SELECT specialty, Count([specialty]) AS NoInSpeciality
    FROM tbl_employees
    group by specialty;
    should work

    looking at your data the tbl_speciality is redundant.. its doesn't do anything, unless you are using it as a constraint in tbl_employee.. if so you should be using the ID of the speciality not the description of the speciality

    the realtionship doesn't enforce RI integrity.... so you could have any value for speciality in employee which may or may not be in specaility. bear inmind that to most computers DoCtor is not the same as doctor, or DOCTOR or any other variant on spelling, worse still because you are suign a test field someone could add a trailing space which on the face of it would seem similar to Doctor but would be sen as a different speciality.


    if you have reservations then have a look at Paul Litwins bit on normalisation on Rudy's site

    Poots has a reference to another good piece on this key fundamental concept... you are nearly there, I just don't think you've grasped the reason why you use substitute (surrogate) keys for values such as this.....

  14. #14
    Join Date
    May 2006
    Posts
    386
    Hi Healdem, thank you for all your efforts and assistance. Yes the tbl_speciality is redundant which I now have removed. Your suggestion did work and it produces the result that I was expecting. THANK YOU FOR YOUR HELP and everyone else kind advice and cooperation. the query is working now.
    Emi-UK
    Love begets Love, Help Begets Help

  15. #15
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Emal
    Hi Healdem, thank you for all your efforts and assistance. Yes the tbl_speciality is redundant which I now have removed. Your suggestion did work and it produces the result that I was expecting. THANK YOU FOR YOUR HELP and everyone else kind advice and cooperation. the query is working now.
    sorry, bit of a misunderstanding there....
    I think tbl_Speciality is required...
    the problem is that you are assigning a text value to the speciality.. so you are relying on the users typing accurate information. If you normalise the data I'd expect the speciality (or role) if you prefer to be a separate table, and you use the id of that role in the employee table for EACH employee. Using a number is more efficient for indexing.

    you need to define a relationship between the tbl_employee & Speciality (as you already had done, but using the ID not the description / text type). The relationship should have been using RI/ Referential Integrity checks. the purpose of the RI constraint is to make sure that the user cannot enter a role which is mispelled, mistyped or not in the defined list. you can use the available roles in a listbox in the data entry form. You need to take a view on whether you

    if you leave the design as its currently designed you have several potential problems
    1) users can mistype roles (eg DOCTOR, doctor or whatever) so records may look similar but aren't
    2) users can enter new specialities Eg Dr, Doctor, SHO whcih may all have the same value
    3) if you decide to change Doctor to say SHO you have to run an update query and update each record.

    Ok in your current model its fairly irrelevant as you arent' storing anythign especilly complex in your table speciality.

    but say you chnaged the sepciality to be role

    you may want to include some form of sub classification say oncology, surgery, Oby Gyn, A&E whatever
    you may want to expnad it further to say include other medical staff, SRN, Theatre, Ward Sister... etc.....

Posting Permissions

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