Return zeros in a table instead of blanks in a make-table query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to return zeros in the make-table query instead of blanks?

Any point in the right direction would be greatly appreciated.

Thanks
Zack
 
Hi Ken, thanks for answering. Below is my SQL. I may not be able to change
every one of the many fields below. Even if you could just show me how to
fill in any one of the fields that would be a good start for me. Any small
help very appreciated.



SELECT Union3.PART AS INQRYPART, Union3.CountOfBID_NO AS [>8INQ],
Union3.FirstOfPROC AS PCode1, Union3.PART_ORD AS PARTSOLD,
Union3.[12MthInqirs], Union3.PCode AS PCode2, Union3.QtySold, Union3.Revenue,
Union3.QtyBought, Union3.TotalCost, Union3.QOH, Union3.QOO, Union3.QtyAlloc,
Union3.TableCode, Union3.[2002QtySold], Union3.[2003QtySold],
Union3.[2004QtySold], Union3.[2002Rev], Union3.[2003Rev], Union3.[2004Rev],
Union3.[2002QtyBuy], Union3.[2003QtyBuy], Union3.[2004QtyBuy],
Union3.[2002Cost], Union3.[2003Cost], Union3.[2004Cost],
(([2003Cost]-[2002Cost])+([2004Cost]-[2003Cost])+([2004Cost])) AS 2005Cost,
(([2003Cost]-[2002Cost])+([2004Cost]-[2003Cost])+([2005Cost]-[2004Cost])+([2005Cost]))
AS 2006Cost, (([2003Rev]-[2002Rev])+([2004Rev]-[2003Rev])+([2004Rev])) AS
2005Rev,
(([2003Rev]-[2002Rev])+([2004Rev]-[2003Rev])+([2005Rev]-[2004Rev])+([2005Rev]))
AS 2006Rev,
(([2003Rev]-[2002Rev])+([2004Rev]-[2003Rev])+([2005Rev]-[2004Rev])+([2006Rev]-[2005Rev])+([2006Rev]))
AS 2007Rev,
(([2003Rev]-[2002Rev])+([2004Rev]-[2003Rev])+([2004Rev]))+(([2003Rev]-[2002Rev])+([2004Rev]-[2003Rev])+([2005Rev]-[2004Rev])+([2005Rev]))+(([2003Rev]-[2002Rev])+([2004Rev]-[2003Rev])+([2005Rev]-[2004Rev])+([2006Rev]-[2005Rev])+([2006Rev]))
AS [Fcst 3 YrsRev],
(([2003Cost]-[2002Cost])+([2004Cost]-[2003Cost])+([2005Cost]-[2004Cost])+([2006Cost]-[2005Cost])+([2006Cost]))
AS 2007Cost,
(([2003Cost]-[2002Cost])+([2004Cost]-[2003Cost])+([2004Cost]))+(([2003Cost]-[2002Cost])+([2004Cost]-[2003Cost])+([2005Cost]-[2004Cost])+([2005Cost]))+(([2003Cost]-[2002Cost])+([2004Cost]-[2003Cost])+([2005Cost]-[2004Cost])+([2006Cost]-[2005Cost])+([2006Cost]))
AS [Fcst 3 YrsCost],
(([2003QtySold]-[2002QtySold])+([2004QtySold]-[2003QtySold])+([2005QtySold]-[2004QtySold])+([2006QtySold]-[2005QtySold])+([2006QtySold]))
AS 2007QtyS,
(([2003QtySold]-[2002QtySold])+([2004QtySold]-[2003QtySold])+([2004QtySold]))+(([2003QtySold]-[2002QtySold])+([2004QtySold]-[2003QtySold])+([2005QtySold]-[2004QtySold])+([2005QtySold]))+(([2003QtySold]-[2002QtySold])+([2004QtySold]-[2003QtySold])+([2005QtySold]-[2004QtySold])+([2006QtySold]-[2005QtySold])+([2006QtySold]))
AS [Fcst 3 YrsQty],
(([2003QtySold]-[2002QtySold])+([2004QtySold]-[2003QtySold])+([2004QtySold]))
AS 2005QtySold,
(([2003QtySold]-[2002QtySold])+([2004QtySold]-[2003QtySold])+([2005QtySold]-[2004QtySold])+([2005QtySold]))
AS 2006QtySold, Union3.[2005QtyBuy2Date], Union3.[2005Rev2Date],
Union3.[2005QtySold2Date], Union3.[2005Cost2Date] INTO 8b
FROM Union3
ORDER BY Union3.QtySold DESC
WITH OWNERACCESS OPTION;
 
Probably you can use the Nz function in calculated fields to convert Null
values to zero values:

Nz(Union3.QtySold, 0)

and so on.

--

Ken Snell
<MS ACCESS MVP>

The parawon said:
Hi Ken, thanks for answering. Below is my SQL. I may not be able to
change
every one of the many fields below. Even if you could just show me how to
fill in any one of the fields that would be a good start for me. Any
small
help very appreciated.



SELECT Union3.PART AS INQRYPART, Union3.CountOfBID_NO AS [>8INQ],
Union3.FirstOfPROC AS PCode1, Union3.PART_ORD AS PARTSOLD,
Union3.[12MthInqirs], Union3.PCode AS PCode2, Union3.QtySold,
Union3.Revenue,
Union3.QtyBought, Union3.TotalCost, Union3.QOH, Union3.QOO,
Union3.QtyAlloc,
Union3.TableCode, Union3.[2002QtySold], Union3.[2003QtySold],
Union3.[2004QtySold], Union3.[2002Rev], Union3.[2003Rev],
Union3.[2004Rev],
Union3.[2002QtyBuy], Union3.[2003QtyBuy], Union3.[2004QtyBuy],
Union3.[2002Cost], Union3.[2003Cost], Union3.[2004Cost],
(([2003Cost]-[2002Cost])+([2004Cost]-[2003Cost])+([2004Cost])) AS
2005Cost,
(([2003Cost]-[2002Cost])+([2004Cost]-[2003Cost])+([2005Cost]-[2004Cost])+([2005Cost]))
AS 2006Cost, (([2003Rev]-[2002Rev])+([2004Rev]-[2003Rev])+([2004Rev])) AS
2005Rev,
(([2003Rev]-[2002Rev])+([2004Rev]-[2003Rev])+([2005Rev]-[2004Rev])+([2005Rev]))
AS 2006Rev,
(([2003Rev]-[2002Rev])+([2004Rev]-[2003Rev])+([2005Rev]-[2004Rev])+([2006Rev]-[2005Rev])+([2006Rev]))
AS 2007Rev,
(([2003Rev]-[2002Rev])+([2004Rev]-[2003Rev])+([2004Rev]))+(([2003Rev]-[2002Rev])+([2004Rev]-[2003Rev])+([2005Rev]-[2004Rev])+([2005Rev]))+(([2003Rev]-[2002Rev])+([2004Rev]-[2003Rev])+([2005Rev]-[2004Rev])+([2006Rev]-[2005Rev])+([2006Rev]))
AS [Fcst 3 YrsRev],
(([2003Cost]-[2002Cost])+([2004Cost]-[2003Cost])+([2005Cost]-[2004Cost])+([2006Cost]-[2005Cost])+([2006Cost]))
AS 2007Cost,
(([2003Cost]-[2002Cost])+([2004Cost]-[2003Cost])+([2004Cost]))+(([2003Cost]-[2002Cost])+([2004Cost]-[2003Cost])+([2005Cost]-[2004Cost])+([2005Cost]))+(([2003Cost]-[2002Cost])+([2004Cost]-[2003Cost])+([2005Cost]-[2004Cost])+([2006Cost]-[2005Cost])+([2006Cost]))
AS [Fcst 3 YrsCost],
(([2003QtySold]-[2002QtySold])+([2004QtySold]-[2003QtySold])+([2005QtySold]-[2004QtySold])+([2006QtySold]-[2005QtySold])+([2006QtySold]))
AS 2007QtyS,
(([2003QtySold]-[2002QtySold])+([2004QtySold]-[2003QtySold])+([2004QtySold]))+(([2003QtySold]-[2002QtySold])+([2004QtySold]-[2003QtySold])+([2005QtySold]-[2004QtySold])+([2005QtySold]))+(([2003QtySold]-[2002QtySold])+([2004QtySold]-[2003QtySold])+([2005QtySold]-[2004QtySold])+([2006QtySold]-[2005QtySold])+([2006QtySold]))
AS [Fcst 3 YrsQty],
(([2003QtySold]-[2002QtySold])+([2004QtySold]-[2003QtySold])+([2004QtySold]))
AS 2005QtySold,
(([2003QtySold]-[2002QtySold])+([2004QtySold]-[2003QtySold])+([2005QtySold]-[2004QtySold])+([2005QtySold]))
AS 2006QtySold, Union3.[2005QtyBuy2Date], Union3.[2005Rev2Date],
Union3.[2005QtySold2Date], Union3.[2005Cost2Date] INTO 8b
FROM Union3
ORDER BY Union3.QtySold DESC
WITH OWNERACCESS OPTION;



Ken Snell said:
Post the SQL statement of the query that you're using.
Click to expand...
Click to expand...
 
Thanks Ken. That was helpful. Also, is there a function that returns zeros
instead of negative values?



Ken Snell said:
Probably you can use the Nz function in calculated fields to convert Null
values to zero values:

Nz(Union3.QtySold, 0)

and so on.

--

Ken Snell
<MS ACCESS MVP>

The parawon said:
Hi Ken, thanks for answering. Below is my SQL. I may not be able to
change
every one of the many fields below. Even if you could just show me how to
fill in any one of the fields that would be a good start for me. Any
small
help very appreciated.



SELECT Union3.PART AS INQRYPART, Union3.CountOfBID_NO AS [>8INQ],
Union3.FirstOfPROC AS PCode1, Union3.PART_ORD AS PARTSOLD,
Union3.[12MthInqirs], Union3.PCode AS PCode2, Union3.QtySold,
Union3.Revenue,
Union3.QtyBought, Union3.TotalCost, Union3.QOH, Union3.QOO,
Union3.QtyAlloc,
Union3.TableCode, Union3.[2002QtySold], Union3.[2003QtySold],
Union3.[2004QtySold], Union3.[2002Rev], Union3.[2003Rev],
Union3.[2004Rev],
Union3.[2002QtyBuy], Union3.[2003QtyBuy], Union3.[2004QtyBuy],
Union3.[2002Cost], Union3.[2003Cost], Union3.[2004Cost],
(([2003Cost]-[2002Cost])+([2004Cost]-[2003Cost])+([2004Cost])) AS
2005Cost,
(([2003Cost]-[2002Cost])+([2004Cost]-[2003Cost])+([2005Cost]-[2004Cost])+([2005Cost]))
AS 2006Cost, (([2003Rev]-[2002Rev])+([2004Rev]-[2003Rev])+([2004Rev])) AS
2005Rev,
(([2003Rev]-[2002Rev])+([2004Rev]-[2003Rev])+([2005Rev]-[2004Rev])+([2005Rev]))
AS 2006Rev,
(([2003Rev]-[2002Rev])+([2004Rev]-[2003Rev])+([2005Rev]-[2004Rev])+([2006Rev]-[2005Rev])+([2006Rev]))
AS 2007Rev,
(([2003Rev]-[2002Rev])+([2004Rev]-[2003Rev])+([2004Rev]))+(([2003Rev]-[2002Rev])+([2004Rev]-[2003Rev])+([2005Rev]-[2004Rev])+([2005Rev]))+(([2003Rev]-[2002Rev])+([2004Rev]-[2003Rev])+([2005Rev]-[2004Rev])+([2006Rev]-[2005Rev])+([2006Rev]))
AS [Fcst 3 YrsRev],
(([2003Cost]-[2002Cost])+([2004Cost]-[2003Cost])+([2005Cost]-[2004Cost])+([2006Cost]-[2005Cost])+([2006Cost]))
AS 2007Cost,
(([2003Cost]-[2002Cost])+([2004Cost]-[2003Cost])+([2004Cost]))+(([2003Cost]-[2002Cost])+([2004Cost]-[2003Cost])+([2005Cost]-[2004Cost])+([2005Cost]))+(([2003Cost]-[2002Cost])+([2004Cost]-[2003Cost])+([2005Cost]-[2004Cost])+([2006Cost]-[2005Cost])+([2006Cost]))
AS [Fcst 3 YrsCost],
(([2003QtySold]-[2002QtySold])+([2004QtySold]-[2003QtySold])+([2005QtySold]-[2004QtySold])+([2006QtySold]-[2005QtySold])+([2006QtySold]))
AS 2007QtyS,
(([2003QtySold]-[2002QtySold])+([2004QtySold]-[2003QtySold])+([2004QtySold]))+(([2003QtySold]-[2002QtySold])+([2004QtySold]-[2003QtySold])+([2005QtySold]-[2004QtySold])+([2005QtySold]))+(([2003QtySold]-[2002QtySold])+([2004QtySold]-[2003QtySold])+([2005QtySold]-[2004QtySold])+([2006QtySold]-[2005QtySold])+([2006QtySold]))
AS [Fcst 3 YrsQty],
(([2003QtySold]-[2002QtySold])+([2004QtySold]-[2003QtySold])+([2004QtySold]))
AS 2005QtySold,
(([2003QtySold]-[2002QtySold])+([2004QtySold]-[2003QtySold])+([2005QtySold]-[2004QtySold])+([2005QtySold]))
AS 2006QtySold, Union3.[2005QtyBuy2Date], Union3.[2005Rev2Date],
Union3.[2005QtySold2Date], Union3.[2005Cost2Date] INTO 8b
FROM Union3
ORDER BY Union3.QtySold DESC
WITH OWNERACCESS OPTION;



Ken Snell said:
Post the SQL statement of the query that you're using.
--

Ken Snell
<MS ACCESS MVP>


Is there a way to return zeros in the make-table query instead of
blanks?

Any point in the right direction would be greatly appreciated.

Thanks
Zack
Click to expand...
Click to expand...
Click to expand...
 
Back
Top