Calculation where record does not exist

[Mattchew2]

Hi,

I'm VERY new to building databases and am playing around with a few things before I unleash any "creations" upon the world.

My first test is to look at a Stock management DB.

I have 3 tables:
Stock (Type, Make, Model).
Stock In (Date In, Model, Quantity)
Stock Out (Date Out, Customer Ref, Model, Quantity)

Now - I fully appreciate I may have not separated the tables correctly - so feel free to feedback there...

However, my specific question is about running a query to show Total stock available. This should be Stock In - Stock Out. However, there are cases where stock out does not exist. With this, the query asks for values (rightly so) before it will run. I tried to build the expression with an Iif statement which looked something like:
Iif(QuantOut is Null, QuantIn, QuantIn - QuantOut). Unfortunately, that didn't do the trick.

Any ideas how you handle this type of error?

Thanks in advance.

[Oralloy]

Mattchew2,

Did you use an outer join between the two tables?

Try that. If you need help with the SQL, post it up here, and we can help you with that.

Cheers,
Oralloy

[Mattchew2]

Hi Oralloy,

Thanks for your response. That helped! Once I had done that and correct set the relationships on both tables - my If expression worked.

One problem down, will no doubt have a lot more along the line.

[Oralloy]

Mattchew2,

You probably want a left or right outer join in this case, not a full outer join. I don't know which to suggest, as the type depends on the order that you put them in the SQL.

Cheers!
Oralloy