I have two tables that are in my query, Practices and Providers.
When a provider leaves a practice we mark them as "Inactive", and of course "Active" when they are still working with us. We are having to manually update the Practice table to reflect the number of current "Active" providers, but with the number of practices/providers we are managing, doesn't always reflect accurately. I am trying to create a query in our Access database to Automatically calculate and pull the number of "Active" providers into our MPL form (which is where all the Practice information is shown). The problem we are coming across is if a Practice is closed or doesn't not currently have any providers listed as "Active", it is hiding there information entirely. I would like it to show a 0 in the "Number of Providers" column of the MPL, as we still need to have access to that information and see if for any referencing.
I have done the group by and count in the query using the Table Providers and Practices. The fields in my Query are Mnemonic and Num Prov from the Practice table and Active/Inactive from the Providers table. I have tried multiple functions/formulas in the criteria under Num Prov and Active/Inactive but it's all still hiding practices with NO "Active" persons.
Sorry if this is extremely confusing, I tried to write it as simple as I could with giving you as much info. as possible!
PLEASE HELP!