Subreports with counts (1 Viewer)

[CanWest]

I have a report that is causing me some grief. There is a main report that has about 7 subreports in it. Each one of these subreports is based on a query that does a count on categories or codes and reports the number of each based on counting an ID field.

Here is my problem. If the one of the counts is 0 it does not show. I absolutely have to have these show.

I have included a test.mdb to show what I am talking about. If you open the report rpt_AHREEmploymentTrainingStats you will see there are several subreports. The second one down is a stat report on disability counts. With the current recordset for that report there are no clients whose disability is addictions. There fore it does not show. I need it to show with a count of 0,

Any help would be greatly appreciated

 

[vbaInet]

I don't have the time to open your db but can you tell me what the calculations are? And are you sure one of them is returning a 0 and not just because it's Null or doesn't have any records?

 

[CanWest]

I don't have the time to open your db but can you tell me what the calculations are? And are you sure one of them is returning a 0 and not just because it's Null or doesn't have any records?

There really are no calculations. The underlying query of the report does a count of disability categories. It works fine except when it finds a category that the count is 0. I need that to show

Here is the code

SELECT tbl_Disabilities.DisabilityCategory, Count(tbl_Disabilities.DisabilityID) AS CountOfDisabilityID
FROM tbl_TempClientResults INNER JOIN tbl_Disabilities ON tbl_TempClientResults.ClientID = tbl_Disabilities.ClientID
WHERE (((tbl_Disabilities.PrimaryDisability)=Yes))
GROUP BY tbl_Disabilities.DisabilityCategory;

 

[vbaInet]

Give me some examples of categories that should be 0.

 

[bob fitz]

As far as I can see "addictions" does not appear in any of your tables.

 

[vbaInet]

There are 2 bob First two records in the Disability table.

However, does Addictions meet your Yes criteria?

 

[GinaWhipp]

Hmm, in your present set-up there does not seem to be a Disability Category table. That would be what I would use to show ALL disabilities whether active in the Client side or not. May I suggest adding one? I also suggest adding some relationships to your database to help maintain integrity across the tables when doing data entry.

 

[GinaWhipp]

Hmm, I see it took me too long to type! Oh well, at least three heads better than one...

 

[vbaInet]

Hmm, in your present set-up there does not seem to be a Disability Category table. That would be what I would use to show ALL disabilities whether active in the Client side or not. May I suggest adding one? I also suggest adding some relationships to your database to help maintain integrity across the tables when doing data entry.

One for Disability and another for Disability Code too.

I would rethink that table design too because there seems to be lots of repetition. Anyway, to answer your question you need to change the JOIN.

 

[bob fitz]

There are 2 bob First two records in the Disability table.

However, does Addictions meet your Yes criteria?

Quite right. I really should get these glasses changed.

The SQL statement used for the sub report is using a second table which as far I can see is superfluous. If you remove the table or change the relationship you will get the desired result.

 

[vbaInet]

Quite right. I really should get these glasses changed.

You need to go to Specsavers
Sorry Gina, that one is a UK joke.

 

[bob fitz]

You need to go to Specsavers
Sorry Gina, that one is a UK joke.

Indeed. And I would if I could see it

 

[GinaWhipp]

@bob
Can't remove the other table because Addicton is not Active and it looks like they're using that table to pull only Active ones.

 

[CanWest]

Thanks for all the input Guys. In this case I am dealing with an existing database that would screw up way to much to change the table structure. Addictions is the one that has a Zero Count in this example but it could be any of the categories and even more than one. And I need a line in the report that says ''0 Addictions'

The problem is as you can see if you run the query by itself it only counts DisabilityCategories that have a count greater than 0. I need it to show up regardless of what the count is.

 

[CanWest]

@bob
Can't remove the other table because Addicton is not Active and it looks like they're using that table to pull only Active ones.

In this example that is correct. This table is used to gather a group of clients based on many different criteria, this one being 'Active"

 

[vbaInet]

I'll make reference to previous posts:

And I need a line in the report that says ''0 Addictions'

The problem is as you can see if you run the query by itself it only counts DisabilityCategories...

...does Addictions meet your Yes criteria?

WHERE (((tbl_Disabilities.PrimaryDisability)=[B][COLOR="Red"]Yes[/COLOR][/B]))

Anyway, to answer your question you need to change the JOIN.

 

[GinaWhipp]

Well, then there is a problem because you can't pull it out of no where. So, somewhere you need to get all of the Disability Categories. If you don't have a table you run a Make Table query that you can incorporate that has the Categoreis but there is no way to adjust your present query.

 

[CanWest]

Ok I have made a change to the query. It now includes the Disability Categories table.

This has ALL of the categories in it. I still can not get the addiction category to show up.

Here is the code and I will attach the new database.

SELECT tbl_DisabilityCategoryList.DisabilityCategory, Count(tbl_Disabilities.ClientID) AS CountOfClientID
FROM tbl_TempClientResults INNER JOIN (tbl_Disabilities INNER JOIN tbl_DisabilityCategoryList ON (tbl_Disabilities.DisabilityCategory = tbl_DisabilityCategoryList.DisabilityCategory) AND (tbl_DisabilityCategoryList.DisabilityCategory = tbl_Disabilities.DisabilityCategory) AND (tbl_Disabilities.DisabilityCategory = tbl_DisabilityCategoryList.DisabilityCategory)) ON tbl_TempClientResults.ClientID = tbl_Disabilities.ClientID
GROUP BY tbl_DisabilityCategoryList.DisabilityCategory;

 

[GinaWhipp]

Looking now...

 

[GinaWhipp]

Where does tbl_TempClientResults come from?