Solved

Access query in MySQL - IIF and SUM function

Posted on 2007-03-23
5
5,398 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
change database name 2 40
when to use sequences in mysql 4 39
How do uses indexes to maximize MySQL Searches 14 68
Generate Unique ID in VB.NET 21 104
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Foreword This article was written many years ago, in the days when PHP supported the MySQL extension (http://php.net/manual/en/function.mysql-connect.php).  Today (http://php.net/manual/en/migration70.removed-exts-sapis.php) you would not use MySQL…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

726 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