Solved

Access query in MySQL - IIF and SUM function

Posted on 2007-03-23
5
5,389 Views
Last Modified: 2007-11-27
Hi,
I'm planning to have MySQL instead of Access 2k3 as back-end.
I'll keep the Access-DB as front-end.

I have mainly 3 types of queries in Access. Would like to see how they look like as a MySQL view.
(MySQL 5.0.27 / MyODBC 3.x and 5.0.11)

1.) with Access IIf function and some calculation:
========================================
SELECT
tblL5LAT.tblL5CAUTOID,
IIf(([LengteAfgekort]),[LengteAfgekort],[REALLengte]) AS BerLengte,
([BerLengte]*[REALStuks]) AS StrekkendeMetersPerLengteInPak
FROM tblL5LAT;

2.) with Access SUM-function (probably the same as Qry 1 ?!?)
====================================================
SELECT
tblL4LAT.LATPakID,
Sum(qryLATSubBer_A.StrekkendeMetersPerLengteInPak) AS SumOfStrekkendeMetersPerLengteInPak
FROM tblL4LAT LEFT JOIN (tblL5LAT LEFT JOIN qryLATSubBer_A ON tblL5LAT.tblL5CAUTOID = qryLATSubBer_A.tblL5CAUTOID) ON tblL4LAT.tbl4LATAUTO = tblL5LAT.tblL4LATID
GROUP BY tblL4LAT.LATPakID;

3.) Filter on a certain Access form-field. For report purposes.
==================================================
SELECT qryJaarOverzichtPRINT.*
FROM qryJaarOverzichtPRINT
WHERE (((qryJaarOverzichtPRINT.VerkoopcontractID)=[Forms]![frmVerkoopcontractOverzicht]![VerkoopcontractID]));
0
Comment
Question by:joop123456
  • 4
5 Comments
 

Author Comment

by:joop123456
ID: 18780822
I can split the question in 3 parts...
Just translate the first query and I will post 2 questions more.... (and link to them)
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 18780996
2 and 3 are the same as in access

the IIF translates to the use of the case statement

e.g.

case when  [LengteAfgekort] > 0 then [LengteAfgekort] else REALLengte end as ....
0
 

Author Comment

by:joop123456
ID: 18781181
Hi!

How would no.3 work in practice then.. I mean It's a form field from Access to filter on.
Since I would like to have it to work in a view.. the view can't read this certain Access form field...
How to deal with this matter...
0
 

Author Comment

by:joop123456
ID: 18782149
/* ALGORITHM=UNDEFINED */ select `db_230`.`tbll5lat`.`tblL5CAUTOID` AS `tblL5CAUTOID`,((case when (`db_230`.`tbll5lat`.`LengteAfgekort` > 0) then `db_230`.`tbll5lat`.`LengteAfgekort` else `db_230`.`tbll5lat`.`REALLengte` end) * `db_230`.`tbll5lat`.`REALStuks`) AS `StrekkendeMetersPerLengteInPak` from `db_230`.`tbll5lat`

It works, but I doubt it is ok.. why does it write the db_name all the time?
This CASE-line in Navicat shows as well a bunch of DOS-tabs/enters..

((case
(.`tbll5lat`
.`LengteAfgekort`
>0).`tbll5lat`
.`LengteAfgekort`
.`tbll5lat`
.`REALLengte`
)*.`tbll5lat`
.`REALStuks`
)
0
 

Author Comment

by:joop123456
ID: 18782189
Maybe I understand it better with an example of realworld code (instead of syntax-examples) of a MySQL view.
(I've found lot of stored procedures with CASE and IF etc. but that seems to be slightly different...)
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
xampp tool 12 48
Need Wordpress help to link from outside to image item number. 6 36
paypal ipn to mysql 3 35
SELECT query on two levels (detail and summary) 13 47
Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Creating and Managing Databases with phpMyAdmin in cPanel.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

786 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question