unexpected query result

  • Thread starter Thread starter Lowan Chan
  • Start date Start date
L

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!
 
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]
 
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


Allen Browne said:
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.

Lowan Chan said:
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!
Click to expand...
Click to expand...
 
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.

Lowan Chan said:
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


Allen Browne said:
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 said:
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.
Click to expand...
Click to expand...
Click to expand...
 
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
'====================================================


Lowan said:
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


Allen Browne said:
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.

Lowan Chan said:
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!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top