Parameter Query to look up a single entity 1

[mraetrudeaujr]

I am trying to modify an SQL query to prompt the enduser via a 'parameter' query to isolate a particular station. Right now I have the following SQL query returning all stations, but I want to give the enduser the flexibility to look up this information by station. I tried experimenting with putting the [STATION] in various places within the SQL statements, but it isn't working. Where would I put the parameters(s)? Here is the code:

SELECT [STATION],[Enter Beginning Date],[Enter Ending Date],
     Abs(Sum([OLD_1326_VIOLENT_FELON])) AS [OLD 1326 VIOLENT FELON], 
     Abs(Sum([OLD_1326_REPEAT_FELON])) AS [OLD 1326 REPEAT FELON], 
     Abs(Sum([OLD_1326_TRIPLE_FELON])) AS [OLD 1326 TRIPLE FELON], 
     Abs(Sum([OLD_1326_SUPERVISED_RELEASE])) AS [OLD 1326 SUPERVISED RELEASE], 
     Abs(Sum([MEETS_OLD_CASID])) AS [OLD CASID]
FROM [tbl_CRIMINAL CASE TRACKING]
WHERE STATION Is Not Null And Date BETWEEN[Enter Beginning Date]And[Enter Ending Date]
GROUP BY [STATION],[Enter Beginning Date],[Enter Ending Date] 
UNION ALL SELECT "Total",[Enter Beginning Date],[Enter Ending Date], 
     Abs(Sum([OLD_1326_VIOLENT_FELON])), 
     Abs(Sum([OLD_1326_REPEAT_FELON])), 
     Abs(Sum([OLD_1326_TRIPLE_FELON])), 
     Abs(Sum([OLD_1326_SUPERVISED_RELEASE])), 
     Abs(Sum([MEETS_OLD_CASID]))
FROM [tbl_CRIMINAL CASE TRACKING]
WHERE STATION Is Not Null And Date BETWEEN[Enter Beginning Date]And[Enter Ending Date];

All of my previous threads have either given me the results that I have wanted or at least enabled me to figure it out for myself...so at least I am learning

Thanks in advance.

 

[hneal98]

I am not sure I totally understand what you are asking, but if it is what I think it is, you might try adding this to the where clause:

 and STATION = [Enter Station]

 

[cghoga]

Try...

...WHERE STATION = [Enter Station Name] And Date BETWEEN[Enter Beginning Date]And[Enter Ending Date];

Hope htis helps.

 

[PHV]

SELECT [STATION],[Enter Beginning Date],[Enter Ending Date],
Abs(Sum([OLD_1326_VIOLENT_FELON])) AS [OLD 1326 VIOLENT FELON],
Abs(Sum([OLD_1326_REPEAT_FELON])) AS [OLD 1326 REPEAT FELON],
Abs(Sum([OLD_1326_TRIPLE_FELON])) AS [OLD 1326 TRIPLE FELON],
Abs(Sum([OLD_1326_SUPERVISED_RELEASE])) AS [OLD 1326 SUPERVISED RELEASE],
Abs(Sum([MEETS_OLD_CASID])) AS [OLD CASID]
FROM [tbl_CRIMINAL CASE TRACKING]
WHERE STATION Is Not Null And Date BETWEEN [Enter Beginning Date] And [Enter Ending Date][highlight] AND STATION=[Enter Station][/highlight]
GROUP BY [STATION],[Enter Beginning Date],[Enter Ending Date]
UNION ALL SELECT "Total",[Enter Beginning Date],[Enter Ending Date],
Abs(Sum([OLD_1326_VIOLENT_FELON])),
Abs(Sum([OLD_1326_REPEAT_FELON])),
Abs(Sum([OLD_1326_TRIPLE_FELON])),
Abs(Sum([OLD_1326_SUPERVISED_RELEASE])),
Abs(Sum([MEETS_OLD_CASID]))
FROM [tbl_CRIMINAL CASE TRACKING]
WHERE STATION Is Not Null And Date BETWEEN [Enter Beginning Date] And[ Enter Ending Date];

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

 

[mraetrudeaujr]

Yep. The WHERE clause is where I was trying to add it, but I didn't have the proper order/syntax. I went ahead and used PHV's suggestion because he is familiar with the SQL that I submitted --- he helped me on it before.

I did have a problem with it, though. My 'totals' kept coming back to all of the Stations totals, with the Station results beng correct. So I put the "AND STATION=[Enter Station]" after the second WHERE clause and it worked! Thanks PHV and everybody else.

Al

 

[PHV]

But in this case (one station only) you don't need the UNION ...

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

 

[mraetrudeaujr]

Ahaa...I just knew that I could get rid of some of this SQL code! Thanks for adding this, I'll do it now and check the results.

Al