Calculate a series of fields in a query or report?? 2

[gRegulator]

Hi,

I need to summarize a set of data and I can't quite figure out how to put this in a single query or report.

For example:

I wish to calculate the number of clients that would appear in each of the following fields, i.e. male on probation, male on conditions, females on fines, etc.:

Male Female
Probation | | |
On Conditions | | |
Fine | | |
Community Service | | |

So is there anyway to calculate all of this data in a single report or query? Right now all of the data is stored in a master table named 'tbl_offenders'.

I really am stuck on this problem and would really appreciate any help if someone could point me in the right direction!

Greg

 

[PHV]

Without your table schema, it's hard to say ...
Have you tried to play with a CrossTab query or a PivotTable ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

could you describe tbl_offenders some? Do you really store the "condition" name in that table? or is there a Foriegn Key to tbl_offenders?

If your table is set up like:

tbl_Offenders
OffenderID
Sex
ConditionID

tbl_Conditions
ConditionID
ConditionDescription

then a query like this should work:

SELECT ConditionDescription, Count(Sex)
FROM tbl_Offenders
INNER JOIN tbl_Conditions on tbl_Offenders.ConditionID = tbl_Conditions.ConditionID
GROUP BY ConditionDescription



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[lespaul]

correction:

SELECT ConditionDescription, Sex, Count(Sex)
FROM tbl_Offenders
INNER JOIN tbl_Conditions on tbl_Offenders.ConditionID = tbl_Conditions.ConditionID
GROUP BY ConditionDescription, Sex

Then use this as the source for the cross tab query PHV mentioned.

 

[gRegulator]

Hi thanks for your quick responses

All of the fields mentioned above are located as check boxes in tbl_offenders. Therefore they are all yes/no. I basically just want the form/query/report to summarize the data and give me a total in each row? Sorry for being so vague before, I hope this clarifies things a little. I have tried playing around with both crosstab queries and pivottables, but they are quite tricky to me as I have not really used them much in the past.

Leslie, where would I put that code you mentioned? would it work if these are listed as check boxes?

Thanks again so much !

 

[PHV]

So, create a normalization query named, say, qry_offenders:
SELECT 'Probation' AS ConditionDescription, Male, Female
FROM tbl_Offenders WHERE Probation = True
UNION ALL SELECT 'On Conditions', Male, Female
FROM tbl_Offenders WHERE [On Conditions] = True
UNION ALL SELECT 'Fine', Male, Female
FROM tbl_Offenders WHERE Fine = True
UNION ALL SELECT 'Community Service', Male, Female
FROM tbl_Offenders WHERE [Community Service] = True
;
And know your summary query:
SELECT ConditionDescription, -Sum(Male) AS Male, -Sum(Female) AS Female
FROM qry_offenders
GROUP BY ConditionDescription;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gRegulator]

Thanks for your help

PHV, you have been a great help to me recently, and I just want to let you know I really appreciate it.

Thanks Again,

Greg