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.
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)