re; Null Values

STEVEGARDNER

New member
Local time
Today, 08:02
Joined
Dec 4, 2008
Messages
4
I have built a query from 2 tables tblPlannedOrders and tblPurchase Orders, the aim of the query is to show how much has been ordered against the planned amount from the budget.

The query works only if something has been ordered against the Planned Order number, if there has been no order the Planned Order Number is not showing.

Is there any way I can get the query to show all planned orders, and if no order has come through yet, for that value to show as a 0... my aim is to show a total overview of what has been ordered versus plan.

Here is the query SQL (which i don't understand!) in case this helps.

SELECT tblPlannedOrders.[Order No], tblPlannedOrders.[Project Name], tblPlannedOrders.Value, Sum(tblPurchaseOrders.Total) AS SumOfTotal
FROM tblPlannedOrders INNER JOIN tblPurchaseOrders ON tblPlannedOrders.[Order No] = tblPurchaseOrders.[Purchase Order No]
GROUP BY tblPlannedOrders.[Order No], tblPlannedOrders.[Project Name], tblPlannedOrders.Value
ORDER BY tblPlannedOrders.[Order No];

Thanks in advance

Steve
 
LOL, looks like you and I have the same problem (see above post - Converting null values)... and good timing! :p
 
The reason it isn't showing you all records is because you need to change the join properties of the two tables you are comparing. It either needs to be option 2 or option 3 depending how your tables are set up. By changing this you are telling the qeury to show results including records that do not exist in both tables.

To do this click the relationship line between the tables from the qeury design view.
 

Users who are viewing this thread

Back
Top Bottom