Convert from MySQL statement to MSAccess SQL Statement.

Discussion in 'Database Support' started by swanside, Apr 3, 2008.

  1. swanside

    swanside Guppy

    Hello everybody. Can somebody please help me to convert the following statement that acesss a MySQL database into a SQL statement to access a MSAccess Database.

    I an having trouble with the IFNULL field?

    Thanks
    Paul.

    Code:
    SELECT
    Job_No,
    IFNULL((SELECT sum(Paying_Rate*Working_Hrs) FROM Labour WHERE Job_No = Job.Job_No),0) AS Labsubtotal,
    IFNULL((SELECT sum(Quantity*Unit_Price+(Quantity*Unit_Price*+Additional_Cost/100)) FROM Material WHERE Job_No = Job.Job_No),0) AS Matsubtotal,
    (SELECT Labsubtotal*0.175) AS Labvat,
    (SELECT Matsubtotal*0.175) AS Matvat,
    (SELECT Matsubtotal + Labsubtotal) AS subtotal,
    (SELECT Labsubtotal*0.175 + Matsubtotal*0.175 ) AS vat,
    (SELECT Labsubtotal*1.175 + Matsubtotal*1.175) AS TOTAL,
    Job.Order_Date,
    Job.File_No,
    Job.Contract,
    Job.Order_Site_Address,
    Job.Job_Description,
    Job.Invoice_Tax_Date, 
    Job.CustomerRef,
    Job.Customer_Name,
    Customer.Billing_Address
    FROM Job
    LEFT JOIN Customer
    ON(Job.Customer_Name=Customer.Customer_Name)

Share This Page

JodoHost - 26,000 hosting end-users in 100 countries
Plesk Web Hosting
VPS Hosting
H-Sphere Web Hosting
Other Services