Null / Non Existent values and Query woes.

[Chrism2]

Hi there.

I've attached my db in the hope someone can help my head scratching.

I have got a Sales summary table with several other related table. Most notably, a table with the items in the sale and one with the costs. Because each sale might contain many different items and many different costs, I thought seperate tables were the way to go.

All I'm trying to do is make another query that gets the total sales (That's adding each line item * quantity) - (Each cost line item*it's quantity) and then finally the margin made on the deal.

However, if there are no costs or sales involved in the sale the query ignores it. (Sounds odd, but some transactions might be cost free, and some may not involve any revenue - so I have to bear it in mind).

I tried the Nz function, but It's either not what I need or I'm doing it wrong.

How do I get this to work?

Many, many thanks!

 

[RV]

The answer is LEFT JOINS.

Here's what your query basically should look like in SQL.
You'll need to use the Nz function on each SUM plus some sort of formatting to reflect your calculated amounts in the appropiate format.
I'll leave that up to you

SELECT tblSalesinfo.SaleRefID, SUM(tblSalesandDebits.[Sale / Debit  Value]* tblSalesandDebits.[Sale / Debit Quantity]) As Sales, SUM(tblCosts.[Cost £] * tblCosts.[Cost QTY]) AS Costs, Sales-Costs AS Margin
FROM (tblSalesinfo LEFT JOIN tblCosts ON tblSalesinfo.SaleRefID = tblCosts.SaleRefID) LEFT JOIN tblSalesandDebits ON tblSalesinfo.SaleRefID = tblSalesandDebits.SaleRefID
GROUP BY tblSalesinfo.SaleRefID;

RV

 

[Chrism2]

Thanks so much for getting back with the answer so quick. Indeed... that seems to have sorted my problem quite nicely!