Calculations Query

[Christine Jackson]

Hope someone can help.

I have 3 tables. Table 1 contains Opening Balance (item ref is unique key). Table 2 contains qty in and Table 3 contains qty out (relationship between all 3 tables is via item ref which is only unique in Table 1).

I am trying to make a query to show the current balance and have tried the following:
Current stock: Nz([Opening Balance])+Nz([qty In])-Nz([Qty Out]) but this is not working.

I think its the link between the 3 tables. If I only put items from table 1 in my query it shows up, but the moment I add table 2 and/or 3 nothing happens.

Anybody any idea where I'm going wrong?

Thanks

Christine

 

[Brianwarnock]

I'm going to guess that you are doing inner joins and thus if an item is not in all 3 tables it will not be selected. You need an outer join from table1 to table2 and another to table3 so that all records from table1 are selected. right click the join line in the query grid and select the correct option from join properties.

Brian

 

[Christine Jackson]

Dear Brian

Many thanks for your help, only not sure what you mean by inner and outer joins. The relationship from Table 1 to tables 2 and 3 are 'one to many'.

christine

 

[Christine Jackson]

Thanks Brian

Worked out what you meant and query now works. Many thanks for your help.

Regards

Christine

 

[neileg]

If there are multiple records in tables 2 and 3 you will need to create totals queries to tie up with table 1.

 

[DCrake]

Simple Software Solutions

You code:

Current stock: Nz([Opening Balance])+Nz([qty In])-Nz([Qty Out]) but this is not working.

Should read:

Current stock: Nz([Opening Balance],0)+Nz([qty In],0)-Nz([Qty Out],0) but this is not working.

David

 

[neileg]

Should read:

Current stock: Nz([Opening Balance],0)+Nz([qty In],0)-Nz([Qty Out],0) but this is not working.

David

Not strictly true. The second parameter in Nz() will default to zero if not specified. It's more of a style/readability issue.

 

[Brianwarnock]

Not strictly true. The second parameter in Nz() will default to zero if not specified. It's more of a style/readability issue.

Whilst true in VBA I thought that in a query expression it defaulted to a zero length string, as i always state it I cannot speak from experience.

Brian