Show Zero Appointments

[EliteDesk@aol.com]

Count Zero

I have an schedule database using Access 2000...

I have tblPeople with pkPeopleID,fldFName, fldLName,...
and tblAppointments with fkPeopleId, fldDate, fldTime,...

I created a query that counts the number of appointments per person for a paticular day. It works fine BUT It does not show the people with zero appointments. I NEED to be able to show the user who has zero appointments. HELP, before I go bald pulling my hair out! I hope I provided enough info and that it makes since. Thank you for your time in advance. Cathi

I tried join properties using include all records from tlbPeople and only... from tblAppointments, but it did not work.

 

[Louie]

Did you do a left join?

Post the Select if you want more help.

 

[EliteDesk@aol.com]

I just went to view then View Join Properties, then choose option two, which is stated above. I do not know what a LEFT join is. Cathi


SELECT DISTINCTROW tblPeople.pkPeopleID, tblPeople.fldFName, tblPeople.fldLName, tblPeople.fldTypeID, tblAppointments.fldDate, tblPeople.fldNotActive, First(tblAppointments.fkPeopleID) AS [First Of fkPeopleID], First(tblAppointments.fldDate) AS [First Of fldDate], Count(*) AS [Count Of tblAppointments]
FROM tblPeople INNER JOIN tblAppointments ON tblPeople.pkPeopleID = tblAppointments.fkPeopleID
GROUP BY tblPeople.pkPeopleID, tblPeople.fldFName, tblPeople.fldLName, tblPeople.fldTypeID, tblAppointments.fldDate, tblPeople.fldNotActive
HAVING (((tblAppointments.fldDate)=[forms]![frmCalendar]![TheDate]));

 

[Louie]

Sorry I didn't see the last line in your original message.
Instead of inner join your query should read Left Join:


SELECT DISTINCTROW tblPeople.pkPeopleID,
tblPeople.fldFName,
tblPeople.fldLName,
tblPeople.fldTypeID,
tblAppointments.fldDate,
tblPeople.fldNotActive,
First(tblAppointments.fkPeopleID) AS [First Of fkPeopleID], First(tblAppointments.fldDate) AS [First Of fldDate],
Count(*) AS [Count Of tblAppointments]
FROM tblPeople LEFT JOIN tblAppointments ON tblPeople.pkPeopleID = tblAppointments.fkPeopleID
GROUP BY tblPeople.pkPeopleID, tblPeople.fldFName, tblPeople.fldLName, tblPeople.fldTypeID, tblAppointments.fldDate, tblPeople.fldNotActive
HAVING (((tblAppointments.fldDate)=[forms]![frmCalendar]![TheDate]));

 

[EliteDesk@aol.com]

Louie,
I just did a copy and paste of the select you posted and I still get the same results. No one listed with zero appointments. Any suggestions would be greatly appreciated. Cathi

 

[Louie]

I guess I'm a little confused on your Group By fields. Looks like you are trying to count duplicate records. You are using First() on several fields yet you are grouping on those same fields. Maybe if I saw some data and expected results I could help more.

 

[EliteDesk@aol.com]

I'm counting total jobs for each person on a paticular day. It only shows appointment data for the first appointment, and gives me a total of appointments for that day. I attached the results I'm getting now. I want to also see the people that have no appointments. Now, the names of the employees with no appointments do not show in the query. If you know of a better way to do this, I'm open for any suggestions. Cathi

 

[Louie]

To save typing I'v shortened some names ( cCount ). Try to use this approach:

SELECT DISTINCTROW tblPeople.pkPeopleID, tblPeople.fldFName, tblPeople.fldLName, tblPeople.fldTypeID, tblPeople.fldNotActive, (Select Count(*) From tblAppointments Where (pkPeopleId= tblPeople.pkPeopleId And fldDate=[TheDate]) Group by pkPeopleID ) AS cCount
FROM tblPeople;

 

[EliteDesk@aol.com]

Lou,
I'm sorry, I really do not understand, how to modify the "code" , but I just copied and pasted. Your example is very close. It shows all employees even with no appointments, but intead of a total of jobs per person, it shows total jobs for that day. Thanks so much for the time you have already spent helping. Cathi

 

[Louie]

Cathi, I thought you only wanted appointments for a specific date. If you remove the date criteria, you should get all the data.

SELECT DISTINCTROW tblPeople.pkPeopleID, tblPeople.fldFName, tblPeople.fldLName, tblPeople.fldTypeID, tblPeople.fldNotActive, (Select Count(*) From tblAppointments Where (pkPeopleId= tblPeople.pkPeopleId ) Group by pkPeopleID ) AS cCount
FROM tblPeople;

 

[EliteDesk@aol.com]

Louie
I'm sorry, for not being clear in the last post, I DO want a paticular date, but I need the count of appointments per employee not the total of jobs for the day. For example:

Jack has 2 jobs for Today
Sue has 3 jobs for Today
Tim had 0 jobs for Today
...
I do NOT need 5 total jobs for today.

Thanks for all you help.... I hope that explains it better.

 

[EliteDesk@aol.com]

Louie
Thanks for everything I figured it out, I couldn't have done it without your help.

SELECT DISTINCTROW tblPeople.pkPeopleID, tblPeople.fldFName, tblPeople.fldLName, tblPeople.fldTypeID, tblPeople.fldNotActive, (Select Count(*) From tblAppointments Where (pkPeopleId= tblPeople.pkPeopleId ) Group by pkPeopleID ) AS cCount
FROM tblPeople;

I just needed to change the Group by to fkPeopleID