Prev: Access 2002 Runtime in Windows 7
Next: SQL String for union query on dynamic number of tables
From: David W. Fenton on 28 Apr 2010 20:27
mcolson <mcolson1590(a)gmail.com> wrote in
news:1060d770-09e2-40e1-8d53-f0452b507835(a)5g2000yqj.googlegroups.com:

> On Apr 27, 6:09�pm, "David W. Fenton"
> <XXXuse...(a)dfenton.com.invalid> wrote:
>> mcolson <mcolson1...(a)gmail.com> wrote
>> innews:6d6ffd6f-4037-4b13-abbc-5d82
> 277113a6(a)c36g2000yqm.googlegroups.co
>> m:
>>
>> > How would I right a case statement like this in Access?
>>
>> > SELECT COUNT(CASE WHEN Location = N'Inner Mass Spec.' AND
>> > EpaName = N'leak1' THEN MFG_SN END) AS InnerCount,
>> > � � � � COUNT(DISTINCT CASE WHEN Location = N'Final Assembly
>> > � � � � (Box)'
>> > THEN MFG_SN END) AS FinalAssembly
>> > FROM ChartMES.dbo.RecourceActualEPA_Report
>> > WHERE Timestamp > '20100422050000'
>> > � �AND Timestamp < '20100422143000';
>>
>> Something like that generally indicates to me that you are
>> storing data in your SQL statement. The choices in the CASE
>> statement should be stored in a data table.
>>
>> If you're not willing (or are unable) to do it properly, you
>> might want to look at the Switch() and Choose() functions in VBA.
>
> I'm not sure what you mean storing data in my SQL statement. I'm
> looking to predefine a query that I can call. The timestamps will
> end up being parameters that are passed to the query.

With the CASE statement (or the Access alternatives) you've got a
branching structure that returns a particular value given certain
conditions. That's a mapping between a pair of values, which is
data. And the only place the data for those pairs of values is found
is in your SQL statement. That's what I mean by "storing data in a
SQL statement".

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
First  |  Prev  | 
Pages: 1 2
Prev: Access 2002 Runtime in Windows 7
Next: SQL String for union query on dynamic number of tables