Queries - Counting in a query

I am having difficulty with creating a query. My tables are listed below

Patient(PatientID, LName, FName, Company ID)
Insurance(Company ID, COmpany Name)
Doctor(Docor ID, LName, FName)
Visit(Doctor ID, Patient ID, Visit Date, VIsit time, Visit Duration)

I need to create a query that when I input the Doctor_ID it will list the insurance companies that the doctor deals with organized in ascending order based on the companies name and number of customers insured by the company. Each company can only show up in the query once. I can't get it to count correctly.

I created the query then tried to sort by count for patients but it gives me 3 and 2 patients for each company but there are only 3 patients total not 5.
Thanks for your help

Use Access query builder to build the query with joins to appropriate tables and the needed fields. Then switch to the SQL window and add the DISTINCT keyword. Ex:

SELECT DISTINCT [Company ID], ....

If still have problem, post the entire query for analysis.

thanks for your help so far. I don't understand SQL as this is for a class I am taking here is the SQL that is for my query. I still havent added the count function into the query below.

SELECT InsuranceCompany.Company_Name, Patient.Patient_ID
FROM (InsuranceCompany INNER JOIN Patient ON InsuranceCompany.[Company_ID] = Patient.[Company_ID]) INNER JOIN (Doctor INNER JOIN Visit ON Doctor.Doctor_ID = Visit.Doctor_ID) ON Patient.Patient_ID = Visit.Patient_ID
GROUP BY InsuranceCompany.Company_Name, Doctor.Doctor_ID, Patient.Patient_ID
HAVING (((Doctor.Doctor_ID)=[Enter Doctor ID]));

You need to do an aggregate calc, might not need the DISTINCT keyword with GroupBy on Company. Try this:

I pasted that into the SQL view and it says there was a syntax error in the group by clause

Sorry, looks I left a comma in place. Delete the comma just before HAVING.

thanks for your help so far but now it says:

You tried to execute a theory that does not include the specified expression 'Doctor.Doctor_ID=[Enter Doctor ID]' as part of an aggregate function

The HAVING clause is a paste of your original query. I had assumed it was working for you. Don't think I have ever used HAVING clause. Do you want to use this query in a report? I would eliminate the HAVING clause and use the WHERE clause of the DoCmd.OpenReport method to filter the records. Is the doctor ID input to a box on form by user and does that same form call the report to open? Then:
DoCmd.OpenReport "reportname", ,"Doctor_ID=" & Me.IDtextboxName

And you could let the report grouping/sorting features do the heavy lifting instead of trying to design the query. Are you using Access2007?