Select case in Access (1 Viewer)

[lcline]

Thanks in advance for your help!!

Please help me get the synatax correct for the Case statement in access. I can get it to work in SQL Server 2000, but not in Access. I keep getting a Syntax error.

Select otherStuff, (Case When Station = 'B' Then Sum(dbo_z_dtbl_1604_Pareto.Num_Defects) End) as B
From MyTable
Group By Line

thanks,
lee

 

[dcx693]

Are you sure that Case construct is valid in Access? I've never seen it before.

 

[lcline]

I am not sure!

I have used it in queries used in asp's and with queries in SQL Server 7 and 2000. But I have not got it to work with Access.

If it does not work in Access, is there another way to return a recordset with values based on certain conditions??

Thanks,
Lee

 

[dcx693]

Lee, the usual way is to use the WHERE or HAVING clause of the SELECT statement like this:
SELECT fields FROM datasource WHERE condition GROUP BY fields;

Here's an example of the usage:
SELECT Mytable.Station, Sum(Mytable.NumDefects) AS SumOfNumDefects
FROM Mytable
WHERE (((Mytable.Station)="B"))
GROUP BY Mytable.Station;

 

[dcx693]

Lee, I also find it usually easier to use Access' query design view to create most queries rather than typing in a SQL statement directly. Once you have something close to what you need, you can change to SQL view and tweak the code if you want.

 

[lcline]

Thanks for your quick response!!

Actually what I had hoped to do is do calcuations based on what station created the defect.

i.e.

Select MyTable.Station, Sum
(Case
When MyTable.Station = 'A' Then MyTable.Num_Defect * .25
When MyTable.Station = 'B' Then MyTable.Num_Defect * .35
When MyTable.Station = 'C' Then MyTable.Num_Defect * .45
Case Else
End) as CostOfDoingBusinessbyStation
From MyTable
Group By Station

I just hoped that there was a way in Access as I am trying to wean my uses from depending on me to create all reports via web app.

Thanks again,
Lee

 

[dcx693]

OK, try this. This query sums up the number of defects by station, then multiplies them by the percentages you listed.

SELECT Mytable.Station, Sum(Mytable.NumDefects) AS SumOfNumDefects, Sum([NumDefects]*IIf([Station]="A",0.25,IIf([Station]="B",0.35,0.45))) AS CostOfBusiness
FROM Mytable
GROUP BY Mytable.Station;

 

[lcline]

Thanks!!!
That'll work!

 

[raskew]

So long as your Station will always be “A”, “B”, “C” or further sequential characters,
you might also consider the Choose() function. It returns choices based on the index—
the index beginning with one. In your example:

If [Station] = “A” then ASC([Station])-64 would return 1: ASC(“A”) being 65
If [Station] = “B” then ASC([Station])-64 would return 2: ASC(“B”) being 66
If [Station] = “C” then ASC([Station])-64 would return 3: ASC(“C”) being 67

Borrowing on DCX693’s query:

SELECT Mytable.Station, Sum(Mytable.NumDefects) AS SumOfNumDefects, Sum([NumDefects]*choose(asc([Station])-64, 0.25, 0.35, 0.45) AS CostOfBusiness
FROM Mytable
GROUP BY Mytable.Station;

Not a lot of difference but it does tighten up the code if there are more than a few
options to consider, and does away with the dreaded nested Iifs.

 

[neileg]

Since I'm just a hacker, not a programmer, I claim no expertise in this at all. However Case is a valid operator in A2K, at least. Here's the example from the help file:

Dim Number
Number = 8 ' Initialize variable.
Select Case Number ' Evaluate Number.
Case 1 To 5 ' Number between 1 and 5, inclusive.
Debug.Print "Between 1 and 5"
' The following is the only Case clause that evaluates to True.
Case 6, 7, 8 ' Number between 6 and 8.
Debug.Print "Between 6 and 8"
Case 9 To 10 ' Number is 9 or 10.
Debug.Print "Greater than 8"
Case Else ' Other values.
Debug.Print "Not between 1 and 10"
End Select

This should easily do what you want, I think.

 

[dcx693]

neileg, the original poster asked about the Case keyword within SQL code, not within VBA.

 

[neileg]

dcx693, you are, in fact, correct.

I'll crawl back under my stone...

 

[dcx693]

Haha.

 

[jasnew1969]

how about this??

I have been looking for days for something close to what you are coming up with here.

3 Fields: Amount, T_Type, S_Date

T_type has 3 possiblities: 20, 21, 22

I need to do the sum amount for each of those within a certain time frame. something like Sum( case t_type=20,amount,0 end) as dep, sum (case t_type=21, amount,0 end ) as pdout, ect.....

can you help with this one... I have searched the web. I thing I found the end of it too..

Thanks

James

 

[dcx693]

You can use a totals query grouping first on T_type, then on amount, and show the count of S_date.

 

[jasnew1969]

how about this???

Let me see if I can make this more difficult and a little more realistic for our purpose. I have two tables:

Daily
scode sdate netsales
1489 04/01/2003 3800.55
Money
scode sdate amount ttype
1489 04/01/2003 1600.25 20
1489 04/01/2003 1000.00 20
1489 04/01/2003 1100.30 20
1489 04/01/2003 250.00 21
1489 04/01/2003 700.00 21
1489 04/01/2003 50.00 21

What I'm trying to do is combine this into one recordset that would contain a recordset like this:

scode sdate netsales ttype(20) ttype(21)
1489 04/01/2003 3800.55 3700.55 1000.00

Is it possible to achieve this result using just one query? This isn't a hypothetical dataset. This is a current project and am so far behind... Thanks for the reply and thanks for any light you can shine on this for me.

James
IT Supervisor
Castleberry Investments

 

[Pat Hartman]

You need three queries.

query1:
Select scode, sdate, Sum(amount) As SumOfAmount, ttype
From Money
Group by scode, sdate, ttype;

query2:
Select D.scode, D.sdate, Q.SumOfamount, Q.ttype
From Daily As D Inner Join query1 As Q On D.scode = q.scode AND D.sdate = q.sdate;

The third query is the crosstabl which should be based on query2.

 

[jasnew1969]

thanks

Thanks for the reply Pat. I was afraid it was impossible to do with one. Atleast now I know for sure.

Thanks again.

James

 

[Pat Hartman]

Why does it matter? All you need to run is the final query. You can use queries and tables interchangeable so there is no problem using the query as the recordsource for a form or report or even in another query.

 

[jasnew1969]

why?

the reason why is because I thought I might be able to get a little more performance from one than from multiple. I was looking for speed..