Using Nz in inventory running balance

[Lyle via AccessMonster.com]

Hi!

I hope you can help me with this, I'm sure it will seems easy to you guys.

I want to run a query where it will show me the running balance of my
inventory. But I can't seem to get it right. I have two types of
transactions, Purchase and Sales. I made a query for each of them to total
the purchases and sales for each inventory item. Problem is, when I group
them together in a single query, the only inventory items appearing with the
running balance are the ones who have both been purchased and sold. I even
tried to use the Nz function, but i think I got it wrong.

In the Purchase query, I placed the expression for Purchase as Purchase: Nz(
[qty],0), where qty is the quantity I purchased.

In the Sales Query, I put Sales: Nz((-1*[qty]),0).

When i put them together in a another query Balance, I placed for the Balance:
[Purchases]+[Sales]. I even tried using Nz([Purchases],0)+Nz([Sales],0]) but
still to no avail.

I summed/totaled all the expressions aboved and grouped them to their
respective inventory name.

Thanks! Hope you can help me.

Lyle

 

[ctfrigg]

Hi Lyle,

I think this had to do with your join type. It sounds like you are using an
Inner Join for the relationship between the two, which would give you the
results you are seeing - returning results where there is a match in both
places. Try using an outer join and see if this helps.

 

[rigormortiz via AccessMonster.com]

Thanks!

But can you briefly explain to me what's the difference between Inner adn
Outer join types? It's the first time I've heard about these. I kind of new
to Access.

Thanks Again!

Lyle

Hi Lyle,

I think this had to do with your join type. It sounds like you are using an
Inner Join for the relationship between the two, which would give you the
results you are seeing - returning results where there is a match in both
places. Try using an outer join and see if this helps.

Hi!

[quoted text clipped - 23 lines]

Lyle