When value equals 0, record disappears

[mpaulbattle]

I have been trying to figure this out for months. :banghead: I have a table, tblProviders. It has Mnemonic, Providers Name, and Active/Inactive.

I am trying to create a query to Providers, group by mnemonic where providers active. It works to give me the correct count, but if a practice has 0 active providers that record does not show. Is there a way to place a 0 and still have that practice show?

I have tried the IIf, Iif(IsNull, and NZ and still removes the record. If i remove the criteria "Active" it shows the 0 but it lists all providers associated with the mnemonic.

We would just like to see the number of active providers per practice.

I have reached out in other forums and the question that kept coming up is why would you like to see a practice with no active providers? The answer is, we need to see there information if they call back after the practice has closed.

Thank you in advance.

 

[MarkK]

Is there an INNER JOIN in the query? An INNER JOIN in a query will only show parent records that have children.

 

[Pat Hartman]

To continue that thought. Try changing the join type to Left (or right depending on how you organized the tables in the query). Open the QBE. Doublle click on the center of the join line to bring up the dialog. Read the three options and choose either 2 or 3 depending on which will do what you want.

 

[mpaulbattle]

it's just one table.

 

[ypma]

CountOfProvider Name Mnemonic Active / Inactive
1 FFPA Active
1 FIXX
2 FMCC Active
1 FMSC Active
4 FMSR Active

Not sure what you require in the active / inactive field ? currently this field is counted but is Blank . Have you considered using inactive as a default for this field .

Regards Ypma

 

[plog]

Is there a way to place a 0 and still have that practice show?

1st, you need decide on terms and stick with them. I see no data labeled as "practice", so I don't know exactly what you are referencing by that term. My guess is its a synonym for Providers, but that's only a guess because I don't know your business terms.

2nd, to get a list of all practices (regardless of other data) you would need a list of all practices. Do you have such a table? I am talking about a table where each practice is a unique record. tblProviders is not unique on any field except ID.

 

[mpaulbattle]

YPMA...no I have not thought about using Inactive as a default. Usually if we get a new practice that means we get a new active doctor. However we can't actually mark them active until the practice go live, meaning we have trained them on how to use the application.

PLOG...the term practice based on tblProviders refers to the Mnemonic field. Each practice we refer to with Mnemonics, i.e. Allergy & Asthma Care Center would be AACC.

Also I do have another table called tblPractices which houses the Practice Name. I added it to the test database and also added the mnemonics to that table just in case. I know mnemonics should not be in two tables.

You will also see the provider count but that is done manually. Unfortunately the team that update that field forget to do that sometimes so when the reports are created the numbers are not accurate.

 

[arnelgp]

Check Query1.

 

[mpaulbattle]

Check Query1.

Yes. That is what I am looking for. When i get into work within the next hour I will test it in my MasterTestCopy db. Thanks.