dbTalk Databases Forums  

Duplicate a sql case statement in Access

Go Back dbTalk Databases Forums Databases comp.databases.ms-access Duplicate a sql case statement in Access

comp.databases.ms-access comp.databases.ms-access


Discuss Duplicate a sql case statement in Access in the comp.databases.ms-access forum.



Reply
 
Thread Tools Display Modes
  #1  
Old   
mcolson
 
Posts: n/a

Default Duplicate a sql case statement in Access - 04-27-2010 , 07:47 AM





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';

Reply With Quote
  #2  
Old   
Salad
 
Posts: n/a

Default Re: Duplicate a sql case statement in Access - 04-27-2010 , 09:14 AM





mcolson wrote:
Quote:
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)

Reply With Quote
  #3  
Old   
David W. Fenton
 
Posts: n/a

Default Re: Duplicate a sql case statement in Access - 04-27-2010 , 06:09 PM


mcolson <mcolson1590 (AT) gmail (DOT) com> wrote in
news:6d6ffd6f-4037-4b13-abbc-5d82277113a6 (AT) c36g2000yqm (DOT) googlegroups.co
m:

Quote:
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/

Reply With Quote
  #4  
Old   
mcolson
 
Posts: n/a

Default Re: Duplicate a sql case statement in Access - 04-28-2010 , 09:27 AM


On Apr 27, 6:09*pm, "David W. Fenton" <XXXuse... (AT) dfenton (DOT) com.invalid>
wrote:
Quote:
mcolson <mcolson1... (AT) gmail (DOT) com> wrote innews:6d6ffd6f-4037-4b13-abbc-5d82277113a6 (AT) c36g2000yqm (DOT) 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.

Reply With Quote
  #5  
Old   
Roger
 
Posts: n/a

Default Re: Duplicate a sql case statement in Access - 04-28-2010 , 09:41 AM


On Apr 28, 8:27*am, mcolson <mcolson1... (AT) gmail (DOT) com> wrote:
Quote:
On Apr 27, 6:09*pm, "David W. Fenton" <XXXuse... (AT) dfenton (DOT) com.invalid
wrote:





mcolson <mcolson1... (AT) gmail (DOT) com> wrote innews:6d6ffd6f-4037-4b13-abbc-5d82277113a6 (AT) c36g2000yqm (DOT) 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 ....

Reply With Quote
  #6  
Old   
David W. Fenton
 
Posts: n/a

Default Re: Duplicate a sql case statement in Access - 04-28-2010 , 07:27 PM


mcolson <mcolson1590 (AT) gmail (DOT) com> wrote in
news:1060d770-09e2-40e1-8d53-f0452b507835 (AT) 5g2000yqj (DOT) googlegroups.com:

Quote:
On Apr 27, 6:09*pm, "David W. Fenton"
XXXuse... (AT) dfenton (DOT) com.invalid> wrote:
mcolson <mcolson1... (AT) gmail (DOT) com> wrote
innews:6d6ffd6f-4037-4b13-abbc-5d82
277113a6 (AT) c36g2000yqm (DOT) 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/

Reply With Quote
Reply

« Previous Thread | Next Thread »



Thread Tools
Display Modes
Linear Mode Linear Mode

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off



Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.


Contact Us - dbTalk Databases Forums - Archive - Top