unexpected query result

[Lowan Chan]

I created a database file for sales and inventory management. I have problem
to get correct inventory balance. e.g.

I have goods A, B, C, D, E in the item info table
I imported A, B, C, D in certain quantity, created a query for stock-in
and sold A and C in certain quantity, created a query for stock-out

then I combine both query to get an inventory by adding one column
(stock-in.[quantity])-(stock-out.[quantity]), the result for balance only
come out for A and C, but not B and D. Even I did not sell any item of B and
D, then there should be stock there, but I cannot get it from this query.

What should I do or anything missing in my query?

Many thanks!

 

[Allen Browne]

Use an outer-join between the 2 tables in the upper pane of query design.

For details, see:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

You also need to use square brackets around the table names if they contain
a dash, e.g.:
[stock-in]

 

[Lowan Chan]

Thanks for your advice.

I go to the website, and accroding that, I set the following into field:

balance: Nz(([stockindetails].[inqty],0)-([stockoutdetails].[outqty],0))

but seems the expression I wrote is wrong. How should i correct it?

Thanks,

Lowan

Use an outer-join between the 2 tables in the upper pane of query design.

For details, see:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

You also need to use square brackets around the table names if they contain
a dash, e.g.:
[stock-in]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I created a database file for sales and inventory management. I have
problem
to get correct inventory balance. e.g.

I have goods A, B, C, D, E in the item info table
I imported A, B, C, D in certain quantity, created a query for stock-in
and sold A and C in certain quantity, created a query for stock-out

then I combine both query to get an inventory by adding one column
(stock-in.[quantity])-(stock-out.[quantity]), the result for balance only
come out for A and C, but not B and D. Even I did not sell any item of B
and
D, then there should be stock there, but I cannot get it from this query.

What should I do or anything missing in my query?

Many thanks!

 

[Allen Browne]

Switch the query to SQL View (View menu, when you are in query design.)

Copy the statement, and post it here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your advice.

I go to the website, and accroding that, I set the following into field:

balance: Nz(([stockindetails].[inqty],0)-([stockoutdetails].[outqty],0))

but seems the expression I wrote is wrong. How should i correct it?

Thanks,

Lowan

Use an outer-join between the 2 tables in the upper pane of query design.

For details, see:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

You also need to use square brackets around the table names if they
contain
a dash, e.g.:
[stock-in]

I created a database file for sales and inventory management. I have
problem
to get correct inventory balance. e.g.

I have goods A, B, C, D, E in the item info table
I imported A, B, C, D in certain quantity, created a query for
stock-in
and sold A and C in certain quantity, created a query for stock-out

then I combine both query to get an inventory by adding one column
(stock-in.[quantity])-(stock-out.[quantity]), the result for balance
only
come out for A and C, but not B and D. Even I did not sell any item of
B
and
D, then there should be stock there, but I cannot get it from this
query.

 

[John Spencer]

Try the following

balance: Nz([stockindetails].[inqty],0)-Nz([stockoutdetails].[outqty],0)

or the following which is syntactally correct, but is logically incorrect.

balance: Nz([stockindetails].[inqty]-[stockoutdetails].[outqty],0)

In other words, don't use the second example.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Thanks for your advice.

I go to the website, and accroding that, I set the following into field:

balance: Nz(([stockindetails].[inqty],0)-([stockoutdetails].[outqty],0))

but seems the expression I wrote is wrong. How should i correct it?

Thanks,

Lowan

Use an outer-join between the 2 tables in the upper pane of query design.

For details, see:
The Query Lost My Records! (Nulls)
at:
http://allenbrowne.com/casu-02.html

You also need to use square brackets around the table names if they contain
a dash, e.g.:
[stock-in]

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I created a database file for sales and inventory management. I have
problem
to get correct inventory balance. e.g.

I have goods A, B, C, D, E in the item info table
I imported A, B, C, D in certain quantity, created a query for stock-in
and sold A and C in certain quantity, created a query for stock-out

then I combine both query to get an inventory by adding one column
(stock-in.[quantity])-(stock-out.[quantity]), the result for balance only
come out for A and C, but not B and D. Even I did not sell any item of B
and
D, then there should be stock there, but I cannot get it from this query.

What should I do or anything missing in my query?

Many thanks!