Counts of recds per field for each value of another field, on a rep. 3

[and]

Hi - can anyone help? I am trying to produce a report showing various record counts from my DB.

Each record in my table is a Client.
Table name: client_table
Field names:
ClientID (PK)
HighQlevel (has 9 possible text values... none, level1, level2, level3, level4..etc...)
Gender - male/female
BME - yes/no
Disab - y/n
LoneP - y/n
HomeL - y/n
Crim - y/n
Refugee - y/n
Asylum - y/n

What I need is to get a total count of the number of records for each field at each HighQlevel, where:
Gender = female
BME = yes
Disab = yes
..etc etc

I need to see zero counts too, for example, if there are Zero females at HighQlevel = level1, I want to see a count of 0.

I'm trying to produce a resulting table such as this:

http://www.diminishingfish.com/sample_query_table.jpg

that I want to show on a MS Access Report....

How oh how?

Andrew.

 

[glalsop]

Here's one way to go about it:

Insert a group for HighQlevel, and set it up to show the group header and the group footer. Then use a series of text boxes in the group footer to display your counts.

So to display the count of males and females for each group:

=Sum(IIf([Gender] = "Male",1,0))
=Sum(IIf([Gender] = "Female",1,0))

To count BME, Disab, and other Yes/No fields:

=Sum(IIf([BME],1,0))
=Sum(IIf([Disab],1,0))

and so on for all of the yes/no fields.

If you need to actually count the yes/no fields per gender, then just add gender as a second level of grouping and put the text boxes in the group footer for that field.

Good luck

-Gary

 

[and]

Thanks Gary. Sounds promising. I'll give that a whirl and get back to you when I invariably run in to trouble...

Andrew.

 

[and]

Hi,

Thanks for your advice Gary. That method does give me mostly what I am after, except...

I have grouped on field HighQLevel - a field with a value list of 12 different possible values. At the moment, if one of these values has not been chosen at all in any record in the DB, then the report does not list this value of HighQLevel as one of the groups in my report....

Is there a way to get it to list all the values that HighQlevel can have, with my report showing zeros for all counts at this level?

Thanks if anyone can help me on this...!

Andrew.

 

[glalsop]

Where are the 12 possible values stored? If they are in a table, then create a left-outer join between that table and your current data source, and use that result set as your report's record source.

Good luck

-Gary

 

[and]

The field is stored in my main table, but the 12 values are entered into the lookup row source for that field.

Do you mean I should create a small 'reference' table containing the row sources for this field?

How do I a 'left-outer join' in my query to use as the rpt record source?

...Sorry, newbie....

Thanks for your help Gary,

Andrew.

 

[glalsop]

Yeah, create another table, and store the values in that table (make sure to use the same data type and length as the main table). Now you can create a relationship between the tables. This is better than typing in a value list as the row source virtually 100% of the time.

Then, go to the query design grid, and add the new table first, then add client_table. Create the link between the HighQLevel Field (if Access doesn't do it for you). Then right-click the link and select 'Join Properties', then select the second option -

Include ALL rows from 'NewTableName' and only those records from 'client_table' where the joined fields are equal.

Save the query, and change your report's record source from clien_table to the new query. This should include all of your HighQLevel choices, even if none of them have ever been used.

Good luck

-Gary

 

[and]

Nice one Gary. That does just what I want....atlast!....
One more question. Is there a way I can put the values of HighQLevel on the report in a particular order...ie the order that they are in in the new table I created to hold the row sources? (Not a-z ascending or descending but a particular order that the user needs to read them in on the report....)

?

 

[glalsop]

An easy way to do something like that is to create a SortOrder (number) field in the HighQLevel table. Set the sort order from 1 to 12, in the order you would like to sort. Then in the report, instead of using the HighQLevel field as the grouping field, use the SortOrder field to group, but display the HighQLevel field. Now if the order you want to display the fields in changes, it is as easy as changing the SortOrder field in the table.

-Gary

 

[and]

Thanks again Gary. That worked just fine.
Cheers for your help and easy to understand explainations.

: )

Andrew.

 

[and]

HI Gary - I hope you might still pick this thread up....I have another question.

I want to add a date selection criteria into the query that drives my report that you helped me with. This is fine. My user inputs 2 dates into unbound fields in a form, which selects records with values of the field registrationdate that fall within the range specified.

However, I still want the dataset generated by my query (that drives my report) to show all possible values of the Field HighQLevel (which I group my report with), so that even if a certain value of the field has never been selected in the DB, the report still shows a group for it (with zero counts). But when I include the date criteria in the query this won't happen. I have an 'Is Null' criteria on my PK clientID so that it 'catches' all the other values of HighQLevel listed in the dataset thanks to the JOIN (option2), but if there is an actual record from my Client_table that falls outside of the date range, then if this was the only instance of that value of HighQLevel occuring in the dataset, then it is removed and that value then doesn't have a group on my report.

Sorry, far too wordy an explaination. I hope you might get what I am saying. My report would be ok just having groups for those values that actually occur, would just be easier for the user if it listed all values all the time even if no records occured.


**************************
SQL of my query as follows:

SELECT Client_Table.ClientID, Client_Table.Registrationdate, HighQLevelval.HighQLevelID, HighQLevelval.HighQLevel, Client_Table.Gender, Client_Table.Disab, Client_Table.LoneP, Client_Table.Homeless, Client_Table.CriminalConv, Client_Table.AsylumSeeker, Client_Table.Refugee, Client_Table.EthnicityID

FROM HighQLevelval LEFT JOIN Client_Table ON HighQLevelval.HighQLevelID = Client_Table.HighQLevelID

WHERE (((Client_Table.Registrationdate) Between [forms]![stats_form]![DateX] And [forms]![stats_form]![DateY])) OR (((Client_Table.ClientID) Is Null));

********************

Thanks,
All help very much appreciated...!

Andrew.

 

[glalsop]

Andrew -

The problem is that Client_Table.ClientID will not be null if there is at least one occurrence of that field (which you seem to have realized already). It also won't be selected because it is outside of the date range.

A work around is to scrub the Client_Table data against nothing but the date range in the form.

So create a query, qryClientData, and add only one table, Client_Table. Add all of the fields that you are interested in, and reference the dates on your form in this query.

Now you will only have the client data that falls between your date parameters.

Now you can remove Client_Table from the query that serves as your report's record source and replace it with qryClientData. If you use the same SQL statement that you posted above, replacing Client_Table with qryClientData, I think you will get the desired results.

Good luck

-Gary

 

[and]

Thanks Gary!.
Just what I needed to do.

Thankyou.

Andrew.