Solved

Access query in MySQL - IIF and SUM function

Posted on 2007-03-23
5
5,377 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
/* 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
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

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). …
All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now