2006.02.24 05:27 PM

Adding CASE to Access SQL

A reader named Marshall left a comment on my earlier Access SQL TRANSFORM Statement post helpfully following up on a point I'd made regarding the lack of support for SQL Server's CASE function in Access SQL. In his comment, Marshall tried to illustrate how one might use a custom VBA function instead to represent a particular CASE and then use the function in an Access SQL statement. I say "tried" because TypePad's crappy comment system ate a large part of his sample (sorry, Marshall), but I got the gist of his comment, and I appreciate the feedback.

Marshall's comment got me thinking about a general purpose VBA function I'd written years back to provide in-line CASE-like functionality in Access SQL statements, without having to write separate custom functions for each CASE. I couldn't find the original function, but it was pretty easy to reproduce. It's actually two functions, one for each of the CASE formats supported by SQL Server: simple and searched. These then call a single function that provides the actual CASE logic.

Here are the VBA functions, along with a private Enum to make things more readable:

Private Enum CaseTypes
  CaseTypeSimple = 1
  CaseTypeSearched = 2
End Enum

Public Function CaseSimple(ByVal Value As Variant, ParamArray WhenExpResultPairs() As Variant) As Variant

  CaseSimple = CaseBase(CaseTypeSimple, Value, WhenExpResultPairs)

End Function

Public Function CaseSearched(ParamArray WhenExpResultPairs() As Variant) As Variant

  CaseSearched = CaseBase(CaseTypeSearched, Empty, WhenExpResultPairs)

End Function

Private Function CaseBase(ByVal CaseType As CaseTypes, ByVal Value As Variant, ByVal WhenExpResultPairs As Variant) As Variant
  
  Dim lNumPairs   As Long
  Dim lPairIndex  As Long
  Dim lIndex      As Long
  Dim bFound      As Boolean

  On Error GoTo Oops

  lNumPairs = (UBound(WhenExpResultPairs) \ 2) + (UBound(WhenExpResultPairs) Mod 2)

  For lPairIndex = 1 To lNumPairs

    lIndex = (lPairIndex - 1) * 2

    If CaseType = CaseTypeSimple Then
      bFound = Nz(Value = WhenExpResultPairs(lIndex), False)
    ElseIf CaseType = CaseTypeSearched Then
      bFound = CBool(Nz(WhenExpResultPairs(lIndex), False))
    Else
      GoTo Oops
    End If

    If bFound Then
      CaseBase = WhenExpResultPairs(lIndex + 1)
      Exit Function
    End If

  Next lPairIndex

  If UBound(WhenExpResultPairs) Mod 2 = 0 Then
    CaseBase = WhenExpResultPairs(UBound(WhenExpResultPairs))
  Else
    CaseBase = Null
  End If

  Exit Function

Oops:
  CaseBase = "#ERROR"

End Function

After dropping all that into an Access database module, you can start using the CaseSimple and CaseSearched functions in your Access SQL statements. Let's look at some examples using the stores table in the SQL Server pubs database. For testing purposes, I reproduced this table in an Access database, and in both SQL Server and Access set the first row's zip to NULL.

Let's start with the simple CASE format. Using this format, the CASE compares an expression to a set of simple expressions to determine the result.

Here's one in SQL Server:

SELECT 
  stor_id, 
  zip, 
  CASE zip 
    WHEN '92789' THEN 'west' 
    WHEN null THEN 'missing' 
    WHEN '98014' THEN 'northwest' 
    ELSE '*' 
  END as [Simple Case] 
FROM stores;

It produces the following results:

Note that NULL comparisons don't work with this format, as NULLs are compared using IS and this CASE format uses a simple "=" comparison.

Using our new CaseSimple function, here's the same query in Access:

SELECT 
  stor_id, 
  zip, 
  CaseSimple(zip, 
    '92789', 'west', 
    null, 'missing', 
    '98014', 'northwest', 
    '*'
  ) as [Simple Case] 
FROM stores;

It produces the same results (although without an explicit formatting directive, NULLs are simply rendered as blanks):

Now for the searched CASE format. Using this format, the CASE evaluates a set of Boolean expressions to determine the result.

Here's one in SQL Server:

SELECT 
  stor_id, 
  zip, 
  CASE 
    WHEN zip = '92789' THEN 'west' 
    WHEN zip Is Null THEN 'missing' 
    WHEN zip >= '96745' THEN 'northwest' 
  END as [Searched Case] 
FROM stores;

It produces the following results:

In this case it was possible to successfully perform a NULL comparison using IS.

Finally, using our new CaseSearched function, here's the same query in Access:

SELECT 
  stor_id, 
  zip, 
  CaseSearched(
    zip = '92789', 'west', 
    zip Is Null, 'missing', 
    zip >= '96745', 'northwest', 
    '*'
  ) as [Searched Case] 
FROM stores;

It produces the same results:

With both functions you can exclude the final value, which is equivalent to the SQL Server CASE ELSE clause, to return a NULL after failing to find a match.

I should point out that these functions (at least in this form) haven't been used in a production system, and I am not sure whether they work in versions of Access prior to 2003 (though I think they should). So, if you decide to use them, you should test very thoroughly and be very careful. Good luck.


Comments

Thanks so much for sharing this code to allow for cases. It works great. Thanks again. Anna

Anna | 2006.03.19 06:46 PM

Anna, you're welcome!

ewbi.develops | 2006.03.19 09:59 PM

What are the advantages of using the above method over for example IIF or SWITCH statements in Access??

G | 2006.03.20 06:14 AM

Good question, G. I should have discussed it above.

The functions above weren't really offered as improvements to IIF and Switch, just alternatives more closely mirroring the layout and behavior of SQL Server's CASE function. If CaseSimple and CaseSearched have an advantage over IIF and Switch it would mostly be in simplicity and clarity, perhaps with some subtle performance improvements for the simple case.

Implementing something more than a simple binary choice with IIF quickly gets messy, as each subsequent condition has to be nested. For instance, here are the two queries above using IIF:

SELECT
stor_id,
zip,
IIF(zip = '92789', 'west', IIF(IsNull(zip), 'missing', IIF(zip = '98014', 'northwest', '*'))) as [Simple Case]
FROM stores;

SELECT
stor_id,
zip,
IIF(zip='92789', 'west', IIF(IsNull(zip), 'missing', IIF(zip>='96745', 'northwest', '*'))) AS [Searched Case]
FROM stores;

That's a lot parentheses for a simple example having only three conditions and one fallback. It's not hard to imagine how more complex conditional statements would quickly become unmanageable.

Also note that in both queries every IIF condition will be evaluated for every row, whereas with CaseSimple the checks will stop with the first match (CaseSearched is more like IIF and Switch in this regard, as the comparisons are evaluated in the query with only the results passed to the function, so there's no savings). This over-evaluation isn't a big deal in these simple examples, but could make difference for other more complex comparisons or comparisons involving custom VBA routines.

Here are the same queries using Switch, which my functions most resemble:

SELECT
stor_id,
zip,
Switch(zip = '92789', 'west', zip is Null, 'missing', zip = '98014', 'northwest', True, '*') AS [Simple Case]
FROM stores;

SELECT
stor_id,
zip,
Switch(zip='92789', 'west', zip Is Null, 'missing', zip>='96745', 'northwest', True, '*') AS [Searched Case]
FROM stores;

Note that for the simple CASE example (the first query), both IIF and Switch can include a Null comparison, which isn't possible with SimpleCase or in SQL Server.

Beyond that it's really just a matter of taste.

Thanks for the comment.

ewbi.develops | 2006.03.20 11:25 AM

Thank you very much for the Access code to use the 'CASE' statement. It works really well!

Fran | 2006.04.10 10:20 PM

Fran, you're welcome. Thanks for the comment!

ewbi.develops | 2006.04.10 11:27 PM

Mehdi,

You are correct that Access SQL does not include a DECODE function like Oracle. You can use IIF, Switch, or the SQL Server CASE-like functionality I described above.

Access SQL also doesn't include a TRUNCATE command. You have to use a DELETE command to empty the table:

DELETE * FROM tablename

If the table includes any auto-increment columns, they will be reset following a compaction. Or, once the table is empty, you can reset them explicitly using:

ALTER TABLE tablename ALTER COLUMN columnname AUTOINCREMENT(seed, increment)

I believe you can substitute the words IDENTITY and SEED for AUTOINCREMENT.

Good luck.

ewbi.develops | 2006.04.25 12:27 PM

I want to ask how i can create a variant and appropriate to it a value in SQL view in Access ?

Peter Totev | 2006.10.11 03:48 AM

Peter,

If possible, give me a little more info about what you want to do. Do you mean you have a variable in some VBA routine that you want to set equal to some result from a query? If so, are you wanting to process a set of rows, or does the query return a single scalar value (e.g., Count(*))?

ewbi.develops | 2006.10.11 01:59 PM

I would like to know what the maximum number of entries is for this snippet of code. I run into "expression too complex" after only 35 entries (17 pairs and the one "else" entry). I could get more variations covered with iif statements (using instr and other little tricks to cover multiple similar entries with a common result). Is there a way to expand the capabilities of this code?

If not I'm thinking about excluding all other entries and then using a union to add on more results from the same table that I am querying.

Example:
select case ([entry], n1, x1, n2, x2...n17, x17, null), sum [value]
from [db1]
groupby case ([entry], n1, x1, n2, x2...n17, x17, null)

union select case ([entry]..., null), sum [value]
from [db1]
groupby case ([entry]...,null)

There are probably 50 or so cases I would like to address and possibly more.

To email me just add "at hotmail" to the name used for this post.
Thank you.

patryuji | 2006.11.17 03:45 PM

Nevermind, it is 13 entries that I can do max. 28 total values entered (13 pairs of when/then, one else, and one showing what to evaluate).

patryuji | 2006.11.17 03:49 PM

Hi Patryuji,

That's exactly what I got, 13 values, one else, and the evaluation value. Here's the query I used:

SELECT CaseSimple(1,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10,11,11,12,12,13,13, 15), SUM(1)
FROM mytable
GROUP BY
CaseSimple(1,1,1,2,2,3,3,4,4,5,5,6,6,7,7,8,8,9,9,10,10,11,11,12,12,13,13, 15)

Note that it doesn't seem to matter whether the expression is repeated in the GROUP BY, this appears to be the limit for any single query. However, as you suggested, I also found that you can UNION together lots of queries that are each at the limit (I did a bunch and never hit a limit).

In the end, though, with this many options to manage, it seems prudent to go ahead and create a reference table and resolve the CASE lookup with a JOIN, if possible.

Good luck.

ewbi.develops | 2006.11.17 07:57 PM

Great idea! I had not thought of that.

I set up a table with the options required and then I was able to just use an "iif" statement to appropriately execute the join in the manner I required.

The code for implementation of "Case" is handy regardless.

patryuji | 2006.11.20 12:29 PM

I'm back again. For some reason this site comes up every time I need an Access trick. :) Of course, sometimes I have to write the trick but you always provide the little clue I need.

My latest needed was for a way to do an Oracle SQL GREATEST function in Access. This is actually an extension to SQL and non-compliant but handy none the less. For those who don't know what the greatest function does, it's a row level Max (i.e., on one row I have 6 values, a1, a2, a3, a4, a5, a6 and want the greatest of the values to appear in a different column at the end.)

Here's the code to make it happen (many thanks for the trick on open ended arrays, didn't realize I could do that in Access) :


Public Function greatest(ParamArray ListOfValues() As Variant) As Variant
Dim lNumValues As Long
Dim vValue As Variant
Dim lCount As Long

On Error GoTo Oops

vValue = Null
lNumValues = UBound(ListOfValues)
If lNumValues >= 1 Then
vValue = ListOfValues(1)
End If
For lCount = 2 To lNumValues
If ListOfValues(lCount) > vValue Then
vValue = ListOfValues(lCount)
End If
Next lCount

greatest = vValue

Exit Function

Oops:
greatest = "#ERROR"


End Function

Marshall | 2007.01.31 05:34 PM

Hi Marshall,

I'm traveling today but I'll try to take a look first thing tomorrow morning. In the meantime, can you also give me an example of your using this function in a SQL statement and also check whether you're dealing with any null values?

Thanks.

ewbi.develops | 2007.02.01 10:10 AM

In my case what I have is a table that looks like:

Idx autonumber
Code varchar(10)
AL money
AK money
CA money
CT money
etc (all 50 states plus the territories)

What I needed was to be able to group the values into ceilings BY CODE. The states are arranged by the government into "regions" (basically make believe groupings since CO is somehow related to FL) but there are only 4 of them so that gives ~12 states per region.

The select looks like:
select code, greatest(AL,FL,SC,TX,..) as Region3, greatest(AK,CA,OR,WA..) as Region4 etc
from tblPrices

and then my results look like:
ABC111,100.00,101.50
ABC112,75.25,74.99
etc

where the raw data looks like:
1,ABC111,98,99,97,100,...
2,ABC112,70,75,85,...

You can achieve the same thing with a case but it's a lot of typing when you are trying to deal with 12 or more items. Of course, Oracle also supports a LEAST function, but the code is identical, just reverse the comparator.

As far as handling the possibility of null values, you may be correct that there is an issue with the ListOfValues(lCount)>vValue line. I don't have access to MS Access at present so I'm not sure how to do a null test in VBA. Any suggestions? In Delphi it would be IsNull... other than that one place, I think the rest will be fine since I never work with the actual value. I've used it on several thousand rows in my environment with no issues, but I doubt there were nulls in the data.

Actually, there may well be an easier way to do this with Access and I'm just unfamiliar with it. I wouldn't claim to be an expert, just sort of stuck working with an existing project.

m

Marshall | 2007.02.02 08:30 PM

For someone like me, with poorest experience with sql statements, this site comes like 'manna' from heaven! I didn't even know that CASE statements where possible. Then I found out that they existed and had to find out that they weren't available for Access. And then I found this site! You have no clue how desperately I was looking for exactly this! This opens for me a whole new world of possibilities. I don't want to emphasize to much, but I’m am really grateful for sharing this -to me so valuable- piece of code!

Thanks again
Daniele Rom

Daniele | 2007.04.15 07:15 PM

Marshall, just noticed I never got back to you, sorry. Thanks for sharing that suggestion and explanation. After reading your explanation it makes perfect sense - that helper function really clarifies the query. Good idea!

ewbi.develops | 2007.04.15 08:40 PM

Daniele, I'm glad you found something of value here. Thanks so much for the positive feedback.

ewbi.develops | 2007.04.15 08:42 PM

Well, sometimes life is cruel.
This msgbox appeard:"Undefined function 'CaseSearched' in expression" !
So i googeld again and had to find this:
"You can't run a query with a function you have written
that resides in the Access database using ADO from a
VB6 application. I spent a lot of time trying to get that
to work a long time ago, only to find that you can't do
it. It's very vexing."
(from: http://aspalliance.com/groups/microsoft_public_dotnet_languages_vb_data/ng-946255_Replace_in_access_queries.aspx)
Well my exact case!!!
This guy does say something of a solution nonthless, but
far too complicated for me.
Maybe you can help me...Please do help me!
Daniele

Daniele | 2007.04.16 10:45 PM

Thank you very much for this code, it helped me a lot!
Greetings from Germany

Susanne | 2007.04.17 12:24 AM

Oh Daniele, I'm sorry. I didn't think to mention that custom VBA functions like this are only available to queries executed within Access.

The only way to programmatically execute these queries from outside of Access is via COM automation of Access itself, which is a pretty heavy-handed way to execute a query. However, if you want to pursue this approach, here's some sample code (for brevity, it assumes you set a reference to the Access and DAO object libraries in your VB6 project):

Dim a As Access.Application
Set a = New Access.Application
a.OpenCurrentDatabase "c:\temp\sql.transform.mdb", False
Dim rs As DAO.Recordset
Dim sql As String
sql = "SELECT stor_id, zip, CaseSearched(" & _
"zip = '92789', 'west', " & _
"zip Is Null, 'missing', " & _
"zip >= '96745', 'northwest', " & _
"'*'" & _
") AS [Searched Case] " & _
"FROM stores;"
Set rs = a.CurrentDb().OpenRecordset(sql, dbOpenForwardOnly)
Do Until rs.EOF
Debug.Print rs.Fields(0) & vbTab & rs.Fields(1) & vbTab & rs.Fields(2)
rs.MoveNext
Loop
rs.Close
Call a.Quit(acQuitSaveNone)

Good luck.

ewbi.develops | 2007.04.17 01:03 AM

Hi Susanne, you're welcome!

ewbi.develops | 2007.04.17 01:05 AM

I'm currently using ado-connection. this would also go?

I can't see the difference to your 'sample code' compared to mine.
But know, that as i had to work before without the 'CASE' function, and i'll
continue to work without it. It would of been something really great, but
i also don't want bother you to much! So thank you for your help and good luck with your
excelent site!

Dim strsql as string

strsql = "SUM(CaseSearched (mnth=1, sales)) AS jan, SUM(CaseSearched (mnth=2, sales)) AS feb, SUM(CaseSearched (mnth=3, sales)) AS mar
FROM prodsales
GROUP BY product"
Call conn
adorec.Open strsql, adoconn

in the vb-module i have:(just an extract)
Option Explicit
Public adoconn As New ADODB.Connection
Public adorec As New ADODB.Recordset
Public strdbpath As String

Public sub conn
If adoconn.State = adStateOpen Then
adoconn.Close
End If
If adorec.State = adStateOpen Then
adorec.Close
End If

strdbpath = App.Path & "\Company.mdb"
adoconn.Provider = "Microsoft.jet.oledb.4.0" '3.51"
adoconn.Properties("Jet OLEDB:Database Password") = "" & Password & ""
adoconn.Open strdbpath
end sub


P.S. IF! you have time to waste:)...well then help is obviously very appreciated;)

| 2007.04.18 05:07 AM

Hi Daniele,

The difference between your code and the sample I posted is that the sample I posted fires up an instance of Access via COM automation, opens a database using this same COM automation, then retrieves a handle to the Access instance's DAO (as opposed to ADO) DBEngine to execute the query. This is what allows the query to utilize custom VBA functions (i.e., CaseSearched) that only the Access VBA implementation knows how to execute.

What your code does is open a database using ADO and then executes a query using that connection. When done that way, the JET DB engine has no way to execute custom VBA logic stored in the database, as the VBA runtime environment is hosted by Access, not JET.

Of course, as I mentioned, automating Access like this is a pretty heavy-handed approach: it's slow and requires a lot of memory, and it's not something you'd want an app to execute lots of times (minimally you'd want to use one instance of Access, instead of opening and closing it each time). And, in fact, it may not even be possible to use this approach, depending on your deployment requirements, because it requires Access be installed on the machine running the code (so, for instance, deployment of the VB code to a server for component-based execution is probably out).

Hope that helps. Good luck.

ewbi.develops | 2007.04.19 12:02 AM

Nice code. I am trying to get this to work for me but ran into issues trying to get it to return the data I want.

Let me as you this: Could you use multiple criteria for your true/false argument? Lets use your zip example and add another column call "plus_four" to the table. Could I CaseSearched for both zip and plus_four fields in a one shot or would we need to nest another CaseSearched like your iif example?

Here is what I was thinking:

SELECT
stor_id,
zip,
CaseSearched(
zip = '92789', 'west',
zip Is Null, 'missing',
zip >= '96745', 'northwest',
(zip = '33701' and plus_four = '4313'), 'Downtown St.Pete',
'*'
) as [Searched Case]

Heywood | 2007.04.30 11:30 AM

Heywood,

Thanks for the comment. As a matter of fact, you can do exactly what you've described. The individual expressions appearing in the CaseSearched call are evaluated by Access/JET in the context of the query, with only the results being sent to the CaseSearched function for selection of the first non-false value. So, you can combine ANDs and ORs, and even use parantheses to enforce evaluation order, to form any valid SQL expressions you need.

Hope that helps. Good luck.

ewbi.develops | 2007.04.30 12:48 PM

Hi,
Is there a query that can update the field names in a query. I have 65 fields to adjust, partly because of the calendar year change. Last Year Cy=2006 & PY = 2005. It needs to be changed to CY=2007 and PY=2006. I tried using the CASE statement to adjust the final records in the TABLE (using case statement that made all 2005 => 2006, etc...), but i am limited to the amount of entries i can make. I have about 65 yrs to shift and change.

Winston | 2007.10.22 09:58 AM

Hi Winston,

I'm afraid I'm still a little too dense this morning (no coffee yet) to visualize what your query must be doing. Can you please post a snippet of the query (sans all 65 fields) to illustrate what "field names" you need to dynamically change?

Thanks.

ewbi.develops | 2007.10.22 10:17 AM

Function ChangeFieldName(TblName As String, OldFldName As String, NewFldName As String)
Dim Td As TableDef
Dim Db As Database
Dim DbPath As Variant
Dim FldPos As Integer
Dim rs As Recordset
Dim IdxName As String

'get back end path of linked table
DbPath = DLookup("Database", "MSysObjects", "Name='" & TblName & "' And Type=6")
If IsNull(DbPath) Then
Set Db = CurrentDb 'if local table
Else
Set Db = OpenDatabase(DbPath) 'if linked table
If Err <> 0 Then
'failed to open back end database
Exit Function
End If
'in case back end has different table name than front end
TblName = DLookup("ForeignName", "MSysObjects", "Name='" & TblName & "' And Type=6")
End If

'get table
Set Td = Db.TableDefs(TblName)
If Err <> 0 Then
'failed to get table
GoTo Done
End If

'change field name
Td.Fields(OldFldName).Name = NewFldName

ChangeFieldName = True 'defaults to false if it fails to get here

Done:
If Not Db Is Nothing Then Db.Close
End Function


Sub CallChangeFieldName()
Dim Result As Boolean

'sample call:
Result = ChangeFieldName("Table1", "OldFieldName", "NewFieldName")
Debug.Print Result
End Sub


WOULD THIS WORK?

WINSTON | 2007.10.22 10:42 AM

Hi Winston, I suppose that'll work. However, in your original comment you asked: "Is there a query that can update the field names in a query". Your code is updating the field names in a table. If that's what you need and this does it, great! Thanks for sharing the code.

ewbi.develops | 2007.10.22 11:03 AM

Thanks for the advice so far... Here's a better overview of my situation:

I have about 65 Append Queries, each query consist of the following fields to append:

1. Company
2. ERROR: "2005RATIO.01"
3. Value: 05 Ratio1
4. D: 05 Test
5. Year 2005

For this append query i would like to update the fllowing fields to:

1. Company
2. ERROR: "2006RATIO.01"
3. Value: 06 Ratio1
4. D: 06 Test
5. Year: 2006

For each year I have an append query to report a ratio, as the one shown above it appends RATIO.01 for 2005, on top of that I have append queries for RATIO.02, ..., RATIO.13, for a total of 5 years (making it 65 append queries in total)

So what I wanted to do was create a query to help automate my process as much as possible to avoid having to update each single query each and changing the field names. I figure I can create one query which will do the process and take away the tedious step of entering every single query to update the fields.

Thanks

Winston | 2007.11.02 06:25 AM

I'm developing Application with Delphi and Access as database. Usually I have 2 Access (mdb) file, one for specification DB and the other is DB for "data" (user input).
Usually I'm add the spec tables to the "data" DB as linked tables.
I have a question, how to update the path of linked table in "data" DB when user move the path of spec database files ?
thanks for your response

Ismail | 2008.02.17 12:59 AM

Hi Ismail,

Do you mean how do you update the linked table paths programmatically? If so, then there are a couple of options, but it depends in part on what library you're using to access your JET/MDB databases: DAO, ADO/OLEDB, ODBC, ADO.NET and OLEDB?

ewbi.develops | 2008.02.18 10:47 AM

Probably stupid question, but how exactly do i insert this function in access to get it working?

Benjamin | 2008.05.18 07:53 AM

Hi Benjamin,

Most of my posts make lots of assumptions about what folks know, so questions like this help clarify things. There are a couple of ways to do this, but this is probably the easiest:

- open the Access database
- switch to the Modules section/tab using the database window
- click the New button (assuming this isn't Access 2.0, this will switch you to the VBA editor)
- paste the code above into the module window
- press Ctrl-S (or use the File menu's Save option) to save the module (you'll be prompted for a name)
- switch back to Access by pressing Alt-Q (or using the File menu's Quit option)

With that you're ready to start using the routines in your queries. Good luck!

ewbi.develops | 2008.05.18 01:06 PM

Great!

Thanks for helping me out, works great!

Thanks again,

Benjamin

Benjamin | 2008.05.19 02:56 PM

Hi, I just want to ask how to do this using an UPDATE STATEMENT? or how to combine with SELECT STATEMENT?

SELECT
stor_id,
zip,
CaseSearched(
zip = '92789', 'west',
zip Is Null, 'missing',
zip >= '96745', 'northwest',
'*'
) as [Searched Case]
FROM stores;

Is Select can be combined to an UPDATE Statement?

Thank you,

Marvin | 2008.08.04 01:30 PM

Hi Marvin,

I'm sure there is a way, but I'm not quite sure what you have in mind. Do you mean you wish to update a column on the stores table to have the zip-derived value you've calculated with CaseSearched in the SQL SELECT above, or do you mean to use the calculated CaseSearched value from this SQL SELECT to update some other table?

ewbi.develops | 2008.08.05 06:48 AM

Oh! anyway thx for the reply. Actually nothing to do with that codes because It's just my reference. I have my own codes which I want to enhance.

My project is how to combine these queries into 1 UPDATE Query:

Queries Named as follows:
1. instock query
2. outofstock query
3. lowstock query
4. discontinued query

All running to update my tables but needs to click 1 by 1.
So I need those in one query. I dont know what im going to use. I used a lot of statements but still Im really confused.

Example: I named this query as instock query

UPDATE Teledynamics INNER JOIN QualityMatters ON Teledynamics.[Item Number] = QualityMatters.vendor_partno SET QualityMatters.customfield1 = "IN STOCK"
WHERE (((Teledynamics.[Quantity Available])>10));

Another, lowstock query

UPDATE Teledynamics INNER JOIN QualityMatters ON Teledynamics.[Item Number] = QualityMatters.vendor_partno SET QualityMatters.customfield1 = "LOW STOCK"
WHERE (((Teledynamics.[Quantity Available])>0 And (Teledynamics.[Quantity Available])<10));

another one, outofstock query

UPDATE Teledynamics INNER JOIN QualityMatters ON Teledynamics.[Item Number] = QualityMatters.vendor_partno SET QualityMatters.customfield1 = "OUT OF STOCK"
WHERE (((Teledynamics.[Quantity Available])=0));

and the discontinued query

UPDATE Teledynamics INNER JOIN QualityMatters ON Teledynamics.[Item Number] = QualityMatters.vendor_partno SET QualityMatters.customfield1 = "Discontinued"
WHERE (((Teledynamics.[Quantity Available]) Like 'DISCONTINUED'));

oh! well, i need to use just 1 update query to run and update my two tables. I have 4 queries so I want it to be in only one query which is using UPDATE and SELECT Statements.

hope you can answer my developer!

Thank you and have a great day!

^_^

Marvin | 2008.08.05 07:36 AM

Anyway I want to know how will I convert those into a case statement to simplify my codes. SQL server case statement is different from MS Access case statement right?

Thanks! ^_^

Marvin | 2008.08.05 11:38 AM

Oh! for simple example:
How can I convert this into MS ACCESS CODE using UPDATE query?

If QA > 10 then Avail = "Instock"
ElseIf QA < = 10 then Avail = "Lowstock"
ElseIf QA = 0 then Avail = "Outofstock"
ElseIf QA = 'discontinued' then Avail = "discontinued"
End If

Thank you,

How to use case here?
QA field is in the first table
Avail field is in the second table
so i used inner join.

Marvin | 2008.08.06 01:54 PM

Well Marvin, if I've understood correctly, I think what you're shooting for is this:

UPDATE
Teledynamics
INNER JOIN
QualityMatters
ON
Teledynamics.[Item Number] = QualityMatters.vendor_partno
SET
QualityMatters.customfield1 = CaseSearched(
Teledynamics.[Quantity Available]>10, 'IN STOCK',
Teledynamics.[Quantity Available]>0 And Teledynamics.[Quantity Available]<10, 'LOW STOCK',
Teledynamics.[Quantity Available]=0, 'OUT OF STOCK',
Teledynamics.[Quantity Available]='DISCONTINUED', 'Discontinued'
)

Note that I haven't checked this, so it may be missing a comma or paren or something somewhere; also, I switched your LIKE clause to a simple equals check, as you didn't include a wildcard of any kind.

Hope this helps. Good luck.

ewbi.develops | 2008.08.12 11:16 PM

This is absolutely fantastic!!!!!! I spent all of yesterday cursing MS for making such a shitty system that wouldn't conform to regular sql standards. Thank you so much for this!! It saves me almost 10 minutes of manual labour in Excel after I retrieve my data!!!!! The world needs more people like you... :-)

Have a great weekend!!!

Adi | 2008.10.24 05:13 PM

It is amazing that your function is inherently recursive too!!!!!! I just used a derived column to derive another one!!!! Hurray!!!!!

I'm easily amused...

Adi | 2008.10.24 05:25 PM

Adi, you're too kind! Glad it was of some help.

ewbi.develops | 2008.10.24 08:37 PM

Hi I just would like to ask if you can give some samples on how to use this in an update query.

Thanks

Mike | 2009.01.22 06:38 PM

Sorry Mike, not being notified of comments again by TypePad. I suspect you've moved on by now, but if you still need some examples let me know.

ewbi.develops | 2009.05.18 12:13 PM

Thank you all for the information. It was very helpful.
In the end I had to create a conversion table since I have too many sets but I did learn a lot.

Quin | 2010.07.28 01:53 PM



Post a Comment

 
  (optional)
  (no html)
 
   


TrackBack

TrackBack URL:  http://www.typepad.com/services/trackback/6a00d8341c7bd453ef00d8341c7dc253ef

Listed below are links to weblogs that reference Adding CASE to Access SQL: