Dcount or Totals

[mpaulbattle]

I have two tables, tblPractices and tblProviders. In tblPractices is a field, NumProv, which indicates how many providers are ACTIVE for the practice. Unfortunately this field is not always updated when we make changes to the tblProviders, Active/Inactive field.




I have went into query design and used both tables with Totals. I pulled all fields from tblPractices and only the Active/Inactive from tblProviders. All are set to Group by except NumProv(tblPractices) is set to Count and Active/Inactive (tblProviders) has Criteria set to "Active". When I run this query it shows me all Practices with only Active providers. Unfortunately, we have some practices in pending status so those providers are marked as Inactive, and thus the practice is no populated.

I am trying to have NumProv automatically calculate the number of Active providers based on the tblProviders Active/Inactive and also show practices in pending with providers marked Inactive.

I have also used the DCount

=DCount("[Num Prov]","tblProviders","[Active/Inactive]='Active'")

but it returns 0 for each record.

Am I missing something?

[ssanfu]

Care to post your dB for analysis?

Only need enough records to demonstrate the problem.
Change/delete sensitive data, do "Compact & Repair", then zip it.

Also post the expected results so we can check the query results.

[mpaulbattle]

TestNumProv.zip

I hope i did this correct.

If you notice when you open the MPL form, the Practice Mnemonic ACCK is not showing, but on the Providers list it does. That is a practice that is pending but the provider is not actively using the application.

[orange]

What is the "business" that this database is intended to support?
It helps readers if you give some overview description of the database, what the tables represent and what the specific issue is. It also helps with communications if you use an example with real data showing the input and expected output, and the process(es) involved.

What is a Practice? What is a Provider? How are they related?

[mpaulbattle]

It's a Customer/Client database. Where we as the client are maintaining our customers information.
We are trying to calculate the number of Active Providers that are associated with a Practice/Facility but we also want to show the Practices that are Pending, Closed/Cancelled, or In Progress if the Active Providers equals 0.
See my example of ACCK above.

tblPractices = Customer demographics
tblProviders = Providers associated to the Practice

[orange]

Part of my concern with the database relates to the query qryPractices which shows a relationship between the tables but it doesn't use the PK???
I think there is some basic misunderstanding or misconception regarding the database structure.

[mpaulbattle]

We are also looking at using this Iif expression: IIf([Active / Inactive]='Active',[Num Prov],0)
We are assuming our True Part is incorrect, how can we get it to give us the number providers returned?

[mpaulbattle]

Part of my concern with the database relates to the query qryPractices which shows a relationship between the tables but it doesn't use the PK???
I think there is some basic misunderstanding or misconception regarding the database structure.

Sorry
but when I copied the tables I forgot to remove the ID under tblPractices and make the PracticeID the PK. This is how it is in our Master Database.

[orange]

Based on your Providers table, here is a count of Providers by Active/Inactive.
Note: There are a number of Null values.

Active / Inactive	CountOfProviderID
	24
Active	844
Inactive	320

[mpaulbattle]

Here is the updated version with the correct PK

[orange]

Why is mnemonic in both tables?

[orange]

Based on latest database, here is count of PracticeID by PracticeStatus

Practice Status	CountOfPracticeID
Active	313
Closed/Cancelled	35
In Progress	12

[mpaulbattle]

Based on your Providers table, here is a count of Providers by Active/Inactive.
Note: There are a number of Null values.

Active / Inactive	CountOfProviderID
	24
Active	844
Inactive	320

Orange,
I would need the count per practice. For example, the first practice only has 1 provider so in the Num Prov it would read
Mnemonic Num Prov
AACC 1
ACCC 4

Those are just the active providers for each practice. ACCC has 8 providers listed in tblProviders but four are inactive. ACCK has 1 provider and she is marked as inactive, but when we run the query it shows

Mnemonic Num Prov
AACC 1
ACCC 4
ACLA 1

We are trying to get it to show ACCK 0

[mpaulbattle]

Why is mnemonic in both tables?

Orange,

Mnemonic is in both tables because when we created the providers table we needed to be able to match them to the practices they belong to. We plan on removing the mnemonic field from tblProviders.

[orange]

Based on latest database, here is count of PracticeID by PracticeStatus
Practice Status CountOfPracticeID
Active 313
Closed/Cancelled 35
In Progress 12

I have attached a txt file with counts. Don't know if it's helpful.