Prev: Access 2002 Runtime in Windows 7
Next: SQL String for union query on dynamic number of tables
From: mcolson on 27 Apr 2010 08:47
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';
From: Salad on 27 Apr 2010 10:14
mcolson wrote:
> 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';

You could use an IIF() statement. Ex:
InnerCount:IIF("Loc = 1 And Type = 2",1,0)
FinalAssembly:IIF("Completed = True",1,0)
Then make it a Totals (View/Totals) query and Sum the column(s)

From: David W. Fenton on 27 Apr 2010 19:09
mcolson <mcolson1590(a)gmail.com> wrote in
news:6d6ffd6f-4037-4b13-abbc-5d82277113a6(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.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/
From: mcolson on 28 Apr 2010 10:27
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-5d82277113a6(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.
>
> --
> David W. Fenton                  http://www.dfenton.com/
> usenet at dfenton dot com    http://www.dfenton.com/DFA/

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.
From: Roger on 28 Apr 2010 10:41
On Apr 28, 8:27 am, mcolson <mcolson1...(a)gmail.com> wrote:
> 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-5d82277113a6(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.
>
> > --
> > David W. Fenton                  http://www.dfenton.com/
> > usenet at dfenton dot com    http://www.dfenton.com/DFA/
>
> 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.- Hide quoted text -
>
> - Show quoted text -

select count(iif(location = 'Inner Mass Spec.',
iif(epaName = 'leak1',mfg_sn,0),0)) as innerCount
, count(iif(location = 'Final Assembly (Box)', mfg_sn, 0)) as
finalAssembly
from ....
 |  Next  |  Last
Pages: 1 2
Prev: Access 2002 Runtime in Windows 7
Next: SQL String for union query on dynamic number of tables