SQL Union, Summary Query: Date Parameter 1

[gRegulator]

Hi,

I have created a Union Query which selects data from my main table 'tbl_offenders'. This returns all data that is stored in the table according to the following fields:

SELECT 'Probation' AS ConditionDescription, Male, Female
FROM tbl_Offenders
WHERE [Probation] = True

UNION ALL SELECT 'Conditional Sentence', Male, Female
FROM tbl_Offenders
WHERE [Conditional Sentence] = True

UNION ALL SELECT 'Fine Option Program', Male, Female
FROM tbl_Offenders
WHERE [Fine Option Program] = True

UNION ALL SELECT 'Community Service', Male, Female
FROM tbl_Offenders
WHERE [Community Service Order] = True;

A part of my table tbl_offenders also includes a start date for each of these types of dispositions. Those fields as titled [Probation Start], [Conditional Sentence Start], [Fine Option Program Start], [Community Service Order Start].

I then have made a summary query which tabulates the data for me. The SQL coding for that is as follows:

SELECT qry_offenders.ConditionDescription AS Disposition, -Sum([Male]) AS [Male Clients], -Sum([Female]) AS [Female Clients]
FROM qry_offenders
GROUP BY qry_offenders.ConditionDescription;

I would like the option of filtering the data by way of a parameter that includes the start date and only by the dates specified. Does anyone have an idea of how to include the dates in this query?

Thanks in advance!

 

[Golom]

SELECT 'Probation' AS ConditionDescription, Probation_Start As [StartDate], Male, Female
FROM tbl_Offenders 
WHERE [Probation] = True

UNION ALL SELECT 'Conditional Sentence', Conditional_Start, Male, Female
FROM tbl_Offenders 
WHERE [Conditional Sentence] = True

UNION ALL SELECT 'Fine Option Program', FineOptionStart, Male, Female
FROM tbl_Offenders 
WHERE [Fine Option Program] = True

UNION ALL SELECT 'Community Service', CommunityServiceStart, Male, Female
FROM tbl_Offenders 
WHERE [Community Service Order] = True;

Include a parameters specification like
PARAMETERS [Start Date] Date, [End Date] Date;

Then simply add a WHERE clause to your summary query along the lines of
WHERE StartDate BETWEEN [Start Date] AND [End Date]
or
WHERE StartDate >= [Start Date]

 

[gRegulator]

Hi Golom, Thanks for your reply...

Would the Parameters go after the Where Statement of each? Or would it just be placed at the end of the whole thing? Also, would i write different parameters for each one e.g.

SELECT 'Probation' AS ConditionDescription, Male, Female
FROM tbl_Offenders
WHERE [Probation] = True
PARAMETERS [Probation Start] Date;

UNION ALL SELECT 'Conditional Sentence', Male, Female
FROM tbl_Offenders
WHERE [Conditional Sentence] = True
PARAMETERS [CS Start] Date;

Thanks again, hopefully you can just clarify a little for me please

 

[Golom]

The PARAMETERS line is the first line in the summary query that runs off the results of the union query.

PARAMETERS [Start Date] Date, [End Date] Date;
SELECT qry_offenders.ConditionDescription AS Disposition, -Sum([Male]) AS [Male Clients], -Sum([Female]) AS [Female Clients]
FROM qry_offenders
Where StartDate Between [Start Date] AND [End Date]
GROUP BY qry_offenders.ConditionDescription;

You could restrict the UNION query by putting the PARAMETERS statement at the beginning of the UNION and including an appropriate WHERE clause in each of the SELECTs in the UNION.

 

[gRegulator]

Ok that all makes sense, Thanks!

I just have one more question if you don't mind...

I have included the Start Dates in the qry_offenders as :

SELECT 'Probation' AS ConditionDescription, Male, Female, [Probation Start] as [Start]
FROM tbl_Offenders WHERE Probation = True

UNION ALL SELECT 'Conditional Sentence', Male, Female, [CS Start] as [Start]
FROM tbl_Offenders WHERE [Conditional Sentence] = True

etc.

However, Fine Option Program and COmmunity Service do not have specific Start Dates, Rather they are conditions of either probation or community service, so i tried:

UNION ALL SELECT 'Fine Option Program', Male, Female, [Probation Start] as [Start]
FROM tbl_Offenders WHERE [Fine Option Program] = True

UNION ALL SELECT 'Fine Option Program', Male, Female, [CS Start] as [Start]
FROM tbl_Offenders WHERE [Fine Option Program] = True

UNION ALL SELECT 'Community Service', Male, Female, [Probation Start] as [Start]
FROM tbl_Offenders WHERE [Community Service Order] = True;

UNION ALL SELECT 'Community Service', Male, Female, [CS Start] as [Start]
FROM tbl_Offenders WHERE [Community Service Order] = True;

But that just returned duplicate records. In the table, tbl_offenders, either [Probation Start] or [CS Start]will have a date in it and the other will be null. Is there any way to code this so it will choose one or the other withour returning duplicate records?

Thanks again

 

[Golom]

Try

UNION ALL 
SELECT 'Fine Option Program', Male, Female, 
       NZ([Probation Start],[CS Start]) As [Start]
FROM tbl_Offenders WHERE [Fine Option Program] = True

UNION ALL 
SELECT 'Community Service', Male, Female, 
       NZ([Probation Start],[CS Start]) As [Start]
FROM tbl_Offenders WHERE [Community Service Order] = True;

 

[gRegulator]

WOW!

Thanks again so much. That worked awesome!

I wish I could give you another 10 stars for helping me, this has really been of great assistance.

Thanks again!

Greg