Having 0 populate for Query

[rch918]

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!

[ranman256]

if a practice is 'closed' then is should default to 'Inactive'.
But, it sounds like you need an OUTER join query.
This will link to the other table and still count the items.
(if i understand your question)

[rch918]

That sounds good, although i've been working on an access database for over a year now, the query/functions/formulas still stump me. I'm not sure what an OUTER join query is!

[orange]

Can you post a copy of your database --remove anything confidential first --- as a zip file?
Please provide instructions to get to the specific issue.

It is possible that your database structure doesn't support your business set up. It's also possible as ranman says that join may resolve the issue.

[rch918]

TestNumProv.zip

Design view of qryPractices > When you run the query, it does not show 'Mnemonic' "ACLA" which only has 1 Provider who is marked Inactive - which you can see on the tblProviders. We need this provider/practice to show when the query is run but with 0 in the num prov column.

I have tried the following functions in the Criteria field of 'Num Prov' in the query with no luck. I have even got to the point of duplicating the Practices where 1 line shows Active and a 2nd line shows the Inactive...I do not want the duplication.

IIf([Num Prov]=0,0,(DCount("[Mnemonic]","[tblProviders]","[active/inactive]='Active'")))

Nz([Num Prov],0)

IIf([Active/Inactive]="Inactive",Nz(Count([Num Prov]),0))
__________________________________________________ __________________________________________________ _______________________________

-- If Practice XXXX has 10 providers total listed under it, 6 = "Active" and 4 = "Inactive", I want the 'Num Prov' to only show 6 (the number of "Active)
--- If Practice ZZZZ has 6 providers total listed, and ALL 6 are "Inactive", I was the 'Num Prov' for ZZZZ to show 0.

[orange]

Just on my way out for a few hours.

How are providers and practices related?

[rch918]

The providers work in the practices - they're apart of the practice.

[orange]

My view is you have some structure issues. But you know what all this means and readers don't.

In each table you have an ID and ProviderID/PracticeID. You don't need both.
You don't need mnemonic in both tables.

I would recommend you name your tables with the PK ProviderID autonumber, PK PracticeID autonumber.
It seems a Practice may have Many Providers.

So your tables would be related in 1 to Many relationship.
Also you should avoid field and object names with embedded spaces.

I also see FacilityID with no reference to same.

Your post is confusing in that you have not described what you are trying to do in business terms. I'm sure other readers do not understand Providers, Facilities, Practices as clearly as you.

It is often good if you provide a sample of a typical day at the office to relate your post to things we can understand.

Good luck.

[rch918]

What I do: I help train and support an electronic medical records application for over 300 doctor practices and 1100+ physicians. On the training side we have to call practices to schedule training sessions, so we have created an access Database (Contact Manager Template) to store the practices information (tblPractices). Some of the training's can be provider specific, hence the tblProviders. The tblProviders houses all of their credentials such as type of doctor, DEA numbers, NPI numbers, etc (which has been removed) so we can easily access that information to add them to an invoice for our financial department.

The extra ID fields in both tables were by accident after we re uploaded the tables because there were mistakes on the first one; and that the mnemonics field were on both because we needed to match the practices and we plan on removing it once we can figure out how to resolve this issue.

We added FacilityID because we will eventually add tblFacilities which will house the Addresses, Phone Numbers, etc. for each location for every practice (one practice can have multiple locations). Each doctor will be assigned a facilityID for their primary location of their choosing.

WHAT WE ARE TRYING TO ACCOMPLISH:

On the tblPractices the [Num Prov] has the number of Active doctors, PAs, and NPs working in the practice. As of now we are entering that number manually. Unfortunately, we forget to update that field sometimes so the number isn't always accurate. Management uses this number to report to their bosses how many physicians are currently using our application.

We want this field to reflect the numbers of Providers listed as "Active" per practice from the tblProviders. The current query gives us that number, but if a practice has 0 active providers it hides that record. We would like it to show a 0 instead of hiding the field.

My thinking is because we are looking for Active it is only showing active. Is there a function to say IIf Active, [Num Prov] but show Inactive ones too?

I hope this helps you better understand what I am trying to do.

[/TABLE]

[orange]

Where exactly are you in the design and development of this application? I can assure you from experience that getting your tables and relationships designed and tested to math and support your business requirements is critical. You seem to be developing without a plan or a holistic view of the area of the business involved. Fields really shouldn't appear by mistake, but reality often trumps theory.

You can include a facilities table in your data model/database schema to ensure you have the fields you need for whatever purpose.

Good luck.

[rch918]

Orange,

We actually have a working database we're currently using. This is a request to have how many active providers auto populate per practice. The database I shared on the forum is a test database I created that includes the tables and forms we are working with. I can add the tblfacilities and tblmodules but it would just be extra information I believe that is not needed. Maybe I am wrong. Let me know and I can put it together.

The extra fields are because when we uploaded a copy of the tables they were sorted and we needed the mnemonic field to be in alphabetical order. When we resorted to copy to a new table we forgot to hide the PracticeID and ProviderID fields so when we pasted it into a new table it created the ID field along with the pasting of the PracticeID and ProviderID.

FYI- This project started off just asking me to copy our Excel Spreadsheet into Access so it could be accessed by multiple people at the same time. So I just imported the Worksheets they asked me to. It was then I started reading about relationships and how it could make it more efficient. I am still unsure regarding relationships but the tblPractices, tblProviders, etc were all recreated so I could build relationships. Our Excel Spreadsheet went all the way up to CP and we broke it down over four tables. I attached a screenshot of our current relationships