473,288 Members | 1,693 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,288 software developers and data experts.

Converting Access qrys with logical expressions

I'm upsizing an Access database. Got the data converted, working on
the front end, converting queries to views, but having trouble
converting queries that use logical expressions like the following:
SELECT OrderId,
Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems
FROM OrderDetails
INNER JOIN Items
ON (OrderDetails.ClientId = Items.ClientId)
AND (OrderDetails.ItemId = Items.ItemId)
WHERE (NOT (SitesCustomerTypeId = 2
AND ExpressBackorder =True
AND OrderUrgency = 1 ))
GROUP BY OrderId;

Can someone suggest a strategy to achieve the same result, ie
OrderId,ReadBackOrderItems that I can use in further joins?

Thanks in anticipation
Terry Bell
Jul 20 '05 #1
5 2275

"Terry Bell" <dr**********@hotmail.com> wrote in message
news:92*************************@posting.google.co m...
I'm upsizing an Access database. Got the data converted, working on
the front end, converting queries to views, but having trouble
converting queries that use logical expressions like the following:
SELECT OrderId,
Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems
FROM OrderDetails
INNER JOIN Items
ON (OrderDetails.ClientId = Items.ClientId)
AND (OrderDetails.ItemId = Items.ItemId)
WHERE (NOT (SitesCustomerTypeId = 2
AND ExpressBackorder =True
AND OrderUrgency = 1 ))
GROUP BY OrderId;

Can someone suggest a strategy to achieve the same result, ie
OrderId,ReadBackOrderItems that I can use in further joins?

Thanks in anticipation
Terry Bell


Are you asking how to rewrite the SUM expression? I don't know exactly what
the syntax above means, so this is a guess:

sum(case when BackOrderQtyAvailable > 0 then BackOrderQtyAvailable else 0
end * -1)

If this is wrong, then I suggest you post CREATE TABLE and INSERT statements
to create your tables and populate some sample data, along with the result
you expect to see from your query.

Simon
Jul 20 '05 #2
"Simon Hayes" <sq*@hayes.ch> wrote in message news:<41**********@news.bluewin.ch>...
"Terry Bell" <dr**********@hotmail.com> wrote in message
news:92*************************@posting.google.co m...
I'm upsizing an Access database. Got the data converted, working on
the front end, converting queries to views, but having trouble
converting queries that use logical expressions like the following:
SELECT OrderId,
Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems
FROM OrderDetails
INNER JOIN Items
ON (OrderDetails.ClientId = Items.ClientId)
AND (OrderDetails.ItemId = Items.ItemId)
WHERE (NOT (SitesCustomerTypeId = 2
AND ExpressBackorder =True
AND OrderUrgency = 1 ))
GROUP BY OrderId;

Can someone suggest a strategy to achieve the same result, ie
OrderId,ReadBackOrderItems that I can use in further joins?

Thanks in anticipation
Terry Bell


Are you asking how to rewrite the SUM expression? I don't know exactly what
the syntax above means, so this is a guess:

sum(case when BackOrderQtyAvailable > 0 then BackOrderQtyAvailable else 0
end * -1)

If this is wrong, then I suggest you post CREATE TABLE and INSERT statements
to create your tables and populate some sample data, along with the result
you expect to see from your query.

Simon


Thanks very much Simon you have given me the direction I needed.
For the record, here's my full converted code - with some side errors
fixed

SELECT Q845UndeliveredOrderDetails.OrderId, SUM(CASE WHEN
BackOrderQtyAvailable > 0 THEN 1 ELSE 0 END) AS ReadyBackOrderItems
FROM Q845UndeliveredOrderDetails INNER JOIN
Items ON (Q845UndeliveredOrderDetails.ClientId =
Items.ClientId) AND (Q845UndeliveredOrderDetails.ItemId =
Items.ItemId)
WHERE (NOT (SitesCustomerTypeId = 2 AND ExpressBackorder = 1 AND
OrderUrgency = 1))
GROUP BY Q845UndeliveredOrderDetails.OrderId;
So:
Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems ... in Access
SQL
becomes
SUM(CASE WHEN BackOrderQtyAvailable > 0 THEN 1 ELSE 0 END) AS
ReadyBackOrderItems ... in SQL

I also note that in Access you can say something like

WHERE IsBackOrder

and it evaluates IsBackOrder as a logical expression
whereas in sql server we need to say

WHERE IsBackorder = 1

Is that right?

Then I guess I need to think about NULL too ...

Also I notice in the query analyser it comes up with a message saying
it can't understand the CASE statement, but I can ignore that, can I,
as it seems to go ahead and execute the query anyway?

Once again thanks a million this has saved me lots of time

Terry Bell
Jul 20 '05 #3
<snip>
I also note that in Access you can say something like

WHERE IsBackOrder

and it evaluates IsBackOrder as a logical expression
whereas in sql server we need to say

WHERE IsBackorder = 1

Is that right?
Not quite - there is no Boolean data type in MSSQL, so how to evaluate
'true' or 'false' depends on the data type you've chosen. One common
solution is to use the bit data type, with 1 for true and 0 for false, in
which case your code above is correct (assuming true = 1).

Then I guess I need to think about NULL too ...
Yes - this is one reason why you often see requests for DDL (CREATE TABLE
etc.), as this makes it clear which columns allow NULL and which don't.
Something that seems to work fine may fail when NULLs are involved, so you
need to code for them if the data model allows them.

Also I notice in the query analyser it comes up with a message saying
it can't understand the CASE statement, but I can ignore that, can I,
as it seems to go ahead and execute the query anyway?
I have no idea without seeing the full error, but perhaps this is error 8153
"Warning: Null value is eliminated by an aggregate or other SET operation."?
If so, it's just a warning that the column you SUMmed on contains NULL data.

Once again thanks a million this has saved me lots of time

Terry Bell


You're welcome.

Simon
Jul 20 '05 #4
Generally just copy and paste from Access to Query Analyser. Check the
query runs correctly and then add CREATE PROCEDURE blah blah to the top
and run. This turns the script in to a stored procedure and loads it in
to the current database.

You might want to move all the restrictions to the WHERE clause other
wise you can get some interesting results if you are not very careful.

Sum((BackOrderQtyAvailable)* -1)

(BackOrderQtyAvailable > 0)
Adrian

Terry Bell wrote:
I'm upsizing an Access database. Got the data converted, working on
the front end, converting queries to views, but having trouble
converting queries that use logical expressions like the following:
SELECT OrderId,
Sum((BackOrderQtyAvailable>0)*-1) AS ReadyBackOrderItems
FROM OrderDetails
INNER JOIN Items
ON (OrderDetails.ClientId = Items.ClientId)
AND (OrderDetails.ItemId = Items.ItemId)
WHERE (NOT (SitesCustomerTypeId = 2
AND ExpressBackorder =True
AND OrderUrgency = 1 ))
GROUP BY OrderId;

Can someone suggest a strategy to achieve the same result, ie
OrderId,ReadBackOrderItems that I can use in further joins?

Thanks in anticipation
Terry Bell

Jul 20 '05 #5
On Thu, 16 Sep 2004 17:27:35 +0200, Simon Hayes wrote:
"Terry Bell" <dr**********@hotmail.com> wrote:

Also I notice in the query analyser it comes up with a message saying
it can't understand the CASE statement, but I can ignore that, can I,
as it seems to go ahead and execute the query anyway?


I have no idea without seeing the full error, but perhaps this is error 8153
"Warning: Null value is eliminated by an aggregate or other SET operation."?
If so, it's just a warning that the column you SUMmed on contains NULL data.


I don't think it's a null error -- I think he was editing his query in MS
Access's query editor, in an ADP file, rather than using SQL Server's Query
Analyzer. I've gotten that error from MS Access myself.

As Terry said, Access goes ahead and executes it anyway. It just can't
parse it properly to represent it in the graphical query editor.
Jul 20 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Arnold Shore | last post by:
How do I do that? This is for some bit-diddling, but the logical functions like AND require numeric expressions, per the book. AS
0
by: jwa6 | last post by:
We have recently went to access 2003 from access 97. And from word 97 to Word 2003. We have an Access application we use for ms word mail mergers to a 5160 mail label format. The data for the...
80
by: Christopher Benson-Manica | last post by:
Of course one can get the effect with appropriate use of existing operators, but a ^^ operator would make for nice symmetry (as well as useful to me in something I'm working on). Am I the only one...
7
by: Ryan Epinos | last post by:
How would i convert this on vb.net? Open DestinFile For Input As #25 If LOF(25) > 0 Then Close() GoTo AllreadyE End If
23
by: Reggie | last post by:
Hi and TIA. I developed several A2K dbs which are now being run on my clients computer which have been upgraded to Access 03. I'm not sure exactly what they mean but of you know or could point me...
9
by: marko | last post by:
/* code start */ int a = 0; /* expected evaluation and excution order with precedence in mind /* False(3) , True(1), False(2) */ if ( (a=1) == 0 || 0 != 1 && (a =2) == 1) putchar('T');...
2
by: deltaa5 | last post by:
Hi. i need a source code compute logical expressions in lex/yacc. for Example: (20>5) or (6<=6)--->true (1<=2) and (3==1) --->false.
1
by: =?Utf-8?B?VW5kZXJTZXZlbg==?= | last post by:
Two questions, Can a param. qry take a varaible passed to it from VB as the param and if so how, if not what is a good work around? Second, can a module be used to call an update qry? I've...
1
by: don.fleming | last post by:
We have a tool built for determing which Access version an mdb is that works nicely. Problem is, hundreds of PC's here are getting reimaged without it(!) Since I've been unable to solve that...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.