added nulls to query results

[Guest]

I have an inventory database, in one query i want to show total parts in stock.
the tables I have are TblAcq ( main inventory ) and WOCheckout ( Parts
going out to a jobsite ) When I tell it to use the "In Stock" from TblAcq
minus the "Quantity" from WOCheckout i ge the correct values, but it only
shows items from TblAcq that have a part also on WOCheckout... For instance
if ther are Ten differnt items checked out, But TblAcq has 800 parts in it,
the query only shows the Ten differnt parts correct sum, and show none of the
rest of the parts at all. How do I get it to show the rest of the parts in
quantity?? ( which would be In Stock - 0 since none are chceked out )

right now I have a simple [In Stock] - [Quantity] in a sum format in the
query

[In Stock] is the colum from TableAcq and [Quantity] is from it's colum in
WOCheckout

thanks for any help!

 

[Guest]

Post your query SQL.

 

[Guest]

Here it is,

SELECT [Work Order Check Out].[Part Number] AS [Work Order Check Out_Part
Number], [Work Order Check Out].Quantity, TblAcq.[Part Number] AS
[TblAcq_Part Number], TblAcq.[In Stock], Sum([In Stock]-[Quantity]) AS [Total
in Stock]
FROM [Work Order Check Out] LEFT JOIN TblAcq ON [Work Order Check Out].[Part
Description] = TblAcq.[Part Description]
GROUP BY [Work Order Check Out].[Part Number], [Work Order Check
Out].Quantity, TblAcq.[Part Number], TblAcq.[In Stock];

Post your query SQL.
--
KARL DEWEY
Build a little - Test a little

I have an inventory database, in one query i want to show total parts in stock.
the tables I have are TblAcq ( main inventory ) and WOCheckout ( Parts
going out to a jobsite ) When I tell it to use the "In Stock" from TblAcq
minus the "Quantity" from WOCheckout i ge the correct values, but it only
shows items from TblAcq that have a part also on WOCheckout... For instance
if ther are Ten differnt items checked out, But TblAcq has 800 parts in it,
the query only shows the Ten differnt parts correct sum, and show none of the
rest of the parts at all. How do I get it to show the rest of the parts in
quantity?? ( which would be In Stock - 0 since none are chceked out )

right now I have a simple [In Stock] - [Quantity] in a sum format in the
query

[In Stock] is the colum from TableAcq and [Quantity] is from it's colum in
WOCheckout

thanks for any help!

 

[Guest]

Let me make sure I understand. Does your [Work Order Check Out] table
contain multple work orders? Does this table get cleared at some point in
the day? If what you really want is a list of all the parts, and their
current in stock quantity, then I think you need to do a couple of things:

1. Change the order of the JOIN. The way you have it, you will only see
the parts that are in your [Work Order Check Out] table.
2. You are not really joining on a part description are you? You really
should be using some sort of a Part_Number or an ID field for this. For the
time being, I'll assume that Part Description works.
3. Once you change the order of the join, you also need to take into
account that Access cannot perform arithmetic against a Null value, which is
what you will get in the [Quantity] field from the left join for all of
thoses parts that are not in the [Work Order Check Out] table. To alleviate
this problem, you will need to convert those Null values to zero.
4. I think I would probably aggregate the info in your [Work Order Check
Out] table before I did the join, so it would look like:

SELECT tblAcq.[Part Number],
tblAcq.[In Stock],
[WOCO].Qty,
[In Stock] - NZ([Qty], 0) AS [Total in Stock]
FROM tblAcq
LEFT JOIN (SELECT[Part Description], SUM([Quantity]) as Qty
FROM [Work Order Check Out]
GROUP BY [Part Description]) as WOCO
ON tblAcq.[Part Description] = WOCO.[Part Description]

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Here it is,

SELECT [Work Order Check Out].[Part Number] AS [Work Order Check Out_Part
Number], [Work Order Check Out].Quantity, TblAcq.[Part Number] AS
[TblAcq_Part Number], TblAcq.[In Stock], Sum([In Stock]-[Quantity]) AS [Total
in Stock]
FROM [Work Order Check Out] LEFT JOIN TblAcq ON [Work Order Check Out].[Part
Description] = TblAcq.[Part Description]
GROUP BY [Work Order Check Out].[Part Number], [Work Order Check
Out].Quantity, TblAcq.[Part Number], TblAcq.[In Stock];

Post your query SQL.
--
KARL DEWEY
Build a little - Test a little

I have an inventory database, in one query i want to show total parts in stock.
the tables I have are TblAcq ( main inventory ) and WOCheckout ( Parts
going out to a jobsite ) When I tell it to use the "In Stock" from TblAcq
minus the "Quantity" from WOCheckout i ge the correct values, but it only
shows items from TblAcq that have a part also on WOCheckout... For instance
if ther are Ten differnt items checked out, But TblAcq has 800 parts in it,
the query only shows the Ten differnt parts correct sum, and show none of the
rest of the parts at all. How do I get it to show the rest of the parts in
quantity?? ( which would be In Stock - 0 since none are chceked out )

right now I have a simple [In Stock] - [Quantity] in a sum format in the
query

[In Stock] is the colum from TableAcq and [Quantity] is from it's colum in
WOCheckout

thanks for any help!

 

[John Spencer]

Try changing the LEFT JOIN to a RIGHT JOIN.
In the query grid view double click on the join line and select ALL records
in TblAcq and matching in Work Order Check Out.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Here it is,

SELECT [Work Order Check Out].[Part Number] AS [Work Order Check Out_Part
Number], [Work Order Check Out].Quantity, TblAcq.[Part Number] AS
[TblAcq_Part Number], TblAcq.[In Stock], Sum([In Stock]-[Quantity]) AS
[Total
in Stock]
FROM [Work Order Check Out] LEFT JOIN TblAcq ON [Work Order Check
Out].[Part
Description] = TblAcq.[Part Description]
GROUP BY [Work Order Check Out].[Part Number], [Work Order Check
Out].Quantity, TblAcq.[Part Number], TblAcq.[In Stock];

Post your query SQL.
--
KARL DEWEY
Build a little - Test a little

I have an inventory database, in one query i want to show total parts
in stock.
the tables I have are TblAcq ( main inventory ) and WOCheckout (
Parts
going out to a jobsite ) When I tell it to use the "In Stock" from
TblAcq
minus the "Quantity" from WOCheckout i ge the correct values, but it
only
shows items from TblAcq that have a part also on WOCheckout... For
instance
if ther are Ten differnt items checked out, But TblAcq has 800 parts
in it,
the query only shows the Ten differnt parts correct sum, and show none
of the
rest of the parts at all. How do I get it to show the rest of the
parts in
quantity?? ( which would be In Stock - 0 since none are chceked out )

right now I have a simple [In Stock] - [Quantity] in a sum format in
the
query

[In Stock] is the colum from TableAcq and [Quantity] is from it's
colum in
WOCheckout

thanks for any help!

 

[Guest]

That worked! Thank You very much!


- Cat

Let me make sure I understand. Does your [Work Order Check Out] table
contain multple work orders? Does this table get cleared at some point in
the day? If what you really want is a list of all the parts, and their
current in stock quantity, then I think you need to do a couple of things:

1. Change the order of the JOIN. The way you have it, you will only see
the parts that are in your [Work Order Check Out] table.
2. You are not really joining on a part description are you? You really
should be using some sort of a Part_Number or an ID field for this. For the
time being, I'll assume that Part Description works.
3. Once you change the order of the join, you also need to take into
account that Access cannot perform arithmetic against a Null value, which is
what you will get in the [Quantity] field from the left join for all of
thoses parts that are not in the [Work Order Check Out] table. To alleviate
this problem, you will need to convert those Null values to zero.
4. I think I would probably aggregate the info in your [Work Order Check
Out] table before I did the join, so it would look like:

SELECT tblAcq.[Part Number],
tblAcq.[In Stock],
[WOCO].Qty,
[In Stock] - NZ([Qty], 0) AS [Total in Stock]
FROM tblAcq
LEFT JOIN (SELECT[Part Description], SUM([Quantity]) as Qty
FROM [Work Order Check Out]
GROUP BY [Part Description]) as WOCO
ON tblAcq.[Part Description] = WOCO.[Part Description]

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.

Here it is,

SELECT [Work Order Check Out].[Part Number] AS [Work Order Check Out_Part
Number], [Work Order Check Out].Quantity, TblAcq.[Part Number] AS
[TblAcq_Part Number], TblAcq.[In Stock], Sum([In Stock]-[Quantity]) AS [Total
in Stock]
FROM [Work Order Check Out] LEFT JOIN TblAcq ON [Work Order Check Out].[Part
Description] = TblAcq.[Part Description]
GROUP BY [Work Order Check Out].[Part Number], [Work Order Check
Out].Quantity, TblAcq.[Part Number], TblAcq.[In Stock];

Post your query SQL.
--
KARL DEWEY
Build a little - Test a little


:

I have an inventory database, in one query i want to show total parts in stock.
the tables I have are TblAcq ( main inventory ) and WOCheckout ( Parts
going out to a jobsite ) When I tell it to use the "In Stock" from TblAcq
minus the "Quantity" from WOCheckout i ge the correct values, but it only
shows items from TblAcq that have a part also on WOCheckout... For instance
if ther are Ten differnt items checked out, But TblAcq has 800 parts in it,
the query only shows the Ten differnt parts correct sum, and show none of the
rest of the parts at all. How do I get it to show the rest of the parts in
quantity?? ( which would be In Stock - 0 since none are chceked out )

right now I have a simple [In Stock] - [Quantity] in a sum format in the
query

[In Stock] is the colum from TableAcq and [Quantity] is from it's colum in
WOCheckout

thanks for any help!