Help Modifying Query Please

[pldd4]

Thanks for that, great help

Ive had a go myself, seems to work fine. Is there anything you can find in this sql query which could produce bad things!? or not work etc!?

-----------------

SELECT [product id] AS Product_Code, (SELECT Nz([OS]) FROM [Product Table] WHERE [Product id]=[order table].[Product id]) AS OS, (SELECT Nz([Produce Wk 1]) FROM [Production] WHERE [Product id]=[order table].[Product id]) AS Prod_Wk1, SUM(Nz([wk 1 order])) AS Orders_For_WK1, (SELECT Nz([OS]) FROM [Product Table] WHERE [Product id]=[order table].[Product id]) - Nz (Orders_For_Wk1) AS Left_Wk1, (SELECT Nz([Produce Wk 2]) FROM [Production] WHERE [Product id]=[order table].[Product id]) AS Prod_Wk2, SUM(Nz([wk 2 order])) AS Orders_For_WK2, Nz (Left_Wk1) - Nz (Orders_For_Wk2) + (SELECT Nz([Produce Wk 1]) FROM [Production] WHERE [Product id]=[order table].[Product id]) AS Left_Wk2, (SELECT Nz([Produce Wk 3]) FROM [Production] WHERE [Product id]=[order table].[Product id]) AS Prod_Wk3, SUM(Nz([wk 3 order])) AS Orders_For_WK3, Nz(Left_Wk2) - Nz(Orders_For_Wk3) + (SELECT Nz([Produce Wk 2]) FROM [Production] WHERE [Product id]=[order table].[Product id]) AS Left_Wk3, (SELECT Nz([Produce Wk 4]) FROM [Production] WHERE [Product id]=[order table].[Product id]) AS Prod_Wk4, SUM(Nz([wk 4 order])) AS Orders_For_WK4, Nz(Left_Wk3) - Nz(Orders_For_Wk4) + (SELECT Nz([Produce Wk 3]) FROM [Production] WHERE [Product id]=[order table].[Product id]) AS Left_Wk4, Nz(Left_Wk4) + (SELECT Nz([Produce Wk 4]) FROM [Production] WHERE [Product id]=[order table].[Product id]) AS Total_Left_Inc_Wk4_Production
FROM [order table]
GROUP BY [product id];

--------------

Thanks again for your help ive learnt A LOT!

 

[neileg]

SELECT Nz([Produce Wk 1]) FROM [Production]

Unless you have changed the db from the one I looked at, Produce Wk 1 is not a field in the Production table. Neither are the other fields of this type.

Why are you asking this question? Have you tried it and it doesn't work?

 

[pldd4]

Those fields do now exist.

It does work i'm just not very experienced with queries/sql, so thought id just ask to see if you could see any downfalls or bugs etc.

Regards,