SQL Query, Wrong Results

[gRegulator]

Hi, I have the following SQL query. For some reason, the results it produces are very wrong. What I am trying to do is get a count of the types of offences committed by people (grouped by male and female) for clients admitted during a given time period. My query looks like this:

PARAMETERS [Forms]![frm_dateparameter_q31]![txtStart] DateTime, [Forms]![frm_dateparameter_q31]![txtEnd] DateTime;
SELECT qry_q31_offence_types.Offence, -Sum([Male]) AS [Male Clients], -Sum([Female]) AS [Female Clients]
FROM qry_q31_offence_types
WHERE (((qry_q31_offence_types.Start) Between [Forms]![frm_dateparameter_q31]![txtStart] And [Forms]![frm_dateparameter_q31]![txtEnd]))
GROUP BY qry_q31_offence_types.Offence;

And it is based upon the following query:

SELECT 'Offence Against Person' AS Offence, Male, Female, NZ([Probation Start],[CS Start]) as [Start], NZ([Probation Expiry],[CS Expiry]) as [End]
From tbl_offenders 
WHERE [Offence Against Person] = True

UNION SELECT 'Offence Against Property', Male, Female, NZ([Probation Start],[CS Start]) as [Start], NZ([Probation Expiry],[CS Expiry]) as [End]
From tbl_offenders 
WHERE [Offence Against Property] = True

UNION SELECT 'Impaired Driving', Male, Female, NZ([Probation Start],[CS Start]) as [Start], NZ([Probation Expiry],[CS Expiry]) as [End]
From tbl_offenders 
WHERE [Impaired Driving] = True

UNION SELECT 'Other Criminal Code', Male, Female, NZ([Probation Start],[CS Start]) as [Start], NZ([Probation Expiry],[CS Expiry]) as [End]
From tbl_offenders 
WHERE [Other CCC] = True;

UNION SELECT 'Drug Offence', Male, Female, NZ([Probation Start],[CS Start]) as [Start], NZ([Probation Expiry],[CS Expiry]) as [End]
From tbl_offenders 
WHERE [Drug Offence] = True

UNION SELECT 'Other Federal Statute', Male, Female, NZ([Probation Start],[CS Start]) as [Start], NZ([Probation Expiry],[CS Expiry]) as [End]
From tbl_offenders 
WHERE [Other Federal Statute] = True

UNION SELECT 'Liquor Offence', Male, Female, NZ([Probation Start],[CS Start]) as [Start], NZ([Probation Expiry],[CS Expiry]) as [End]
From tbl_offenders 
WHERE [Liquor Offence] = True

UNION SELECT 'Other Provincial Statute', Male, Female, NZ([Probation Start],[CS Start]) as [Start], NZ([Probation Expiry],[CS Expiry]) as [End]
From tbl_offenders 
WHERE [Other Provincial Statute] = True

UNION SELECT 'Municipal By-Law Offence', Male, Female, NZ([Probation Start],[CS Start]) as [Start], NZ([Probation Expiry],[CS Expiry]) as [End]
From tbl_offenders 
WHERE [Municipal By-Law Offence] = True;

Does anyone have suggestions on why I may be getting incorrect answers from my query?

Any help is greatly appreciated!

Thanks,

Greg

 

[kav123]

Can you clarify what do you mean when you say you are getting wrong results. I mean is the sum you are getting is wrong??

 

[gRegulator]

Yeah the sum is wrong. For example, If i enter the start date ([Forms]![frm_dateparameter_q31]![txtStart]) as 4/1/2005 and the end date ([Forms]![frm_dateparameter_q31]![txtEnd]) as 3/3/2006, I get values that are 2 , 3, etc, where the number should actually be more like 200 or so. For shorter date periods, it appears as though the data may be correct, but everytime there is a difference in the year, it is really messed up. I tried to run it for dates in 2007, when there should be very low numbers, and there are still numbers around 100, which is way off.

I am trying to find the sum of all offences committed by males and females who have been admitted between a time period. The admission date is qry_q31_offence_types.Start.

I hope this makes it a little clearer what I am trying to accomplish, thanks for your help!