joop123456
asked on
Access query in MySQL - IIF and SUM function
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]),[Le ngteAfgeko rt],[REALL engte]) AS BerLengte,
([BerLengte]*[REALStuks]) AS StrekkendeMetersPerLengteI nPak
FROM tblL5LAT;
2.) with Access SUM-function (probably the same as Qry 1 ?!?)
========================== ========== ========== ======
SELECT
tblL4LAT.LATPakID,
Sum(qryLATSubBer_A.Strekke ndeMetersP erLengteIn Pak) AS SumOfStrekkendeMetersPerLe ngteInPak
FROM tblL4LAT LEFT JOIN (tblL5LAT LEFT JOIN qryLATSubBer_A ON tblL5LAT.tblL5CAUTOID = qryLATSubBer_A.tblL5CAUTOI D) 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.V erkoopcont ractID)=[F orms]![frm Verkoopcon tractOverz icht]![Ver koopcontra ctID]));
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]),[Le
([BerLengte]*[REALStuks]) AS StrekkendeMetersPerLengteI
FROM tblL5LAT;
2.) with Access SUM-function (probably the same as Qry 1 ?!?)
==========================
SELECT
tblL4LAT.LATPakID,
Sum(qryLATSubBer_A.Strekke
FROM tblL4LAT LEFT JOIN (tblL5LAT LEFT JOIN qryLATSubBer_A ON tblL5LAT.tblL5CAUTOID = qryLATSubBer_A.tblL5CAUTOI
GROUP BY tblL4LAT.LATPakID;
3.) Filter on a certain Access form-field. For report purposes.
==========================
SELECT qryJaarOverzichtPRINT.*
FROM qryJaarOverzichtPRINT
WHERE (((qryJaarOverzichtPRINT.V
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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...
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...
ASKER
/* ALGORITHM=UNDEFINED */ select `db_230`.`tbll5lat`.`tblL5 CAUTOID` AS `tblL5CAUTOID`,((case when (`db_230`.`tbll5lat`.`Leng teAfgekort ` > 0) then `db_230`.`tbll5lat`.`Lengt eAfgekort` else `db_230`.`tbll5lat`.`REALL engte` end) * `db_230`.`tbll5lat`.`REALS tuks`) AS `StrekkendeMetersPerLengte InPak` 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`
)
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`
)
ASKER
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...)
(I've found lot of stored procedures with CASE and IF etc. but that seems to be slightly different...)
ASKER
Just translate the first query and I will post 2 questions more.... (and link to them)