QA

[BillyRogers]

I need a query that can help me speed up the QA process of a table I have. I
need to check that each column has been updated. What i need to check for is
that each column is not all zeros or nulls. Some columns are numeric and
some are text. i want a query that shows me columns that are either all
zeros or nulls. I've doing this visually now but would like to autmate it.

Is there a way to do this?
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

 

[Duane Hookom]

I would create a union query that builds the list. It might look something
like:

SELECT UniqueID, "Field1" as BlankField
FROM tblNoNameGiven
WHERE Len(Field1 & "") = 0
UNION ALL
SELECT UniqueID, "Field2"
FROM tblNoNameGiven
WHERE Nz(Field2,0) = 0
UNION ALL
.... etc for all fields ...

 

[BillyRogers]

Thanks for the response Duane.

Can you explain why you use len function for "Field1" and NZ for "Field2"?
I'm just trying to understand what it does.

Thanks
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

 

[John Spencer]

"each column is not all zeros or nulls"
Do you mean that you want to identify any column where the field in every
record is zero or Null ; or do you mean you want to show the records where
the value is zero or null in any column in the record?

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

 

[BillyRogers]

What i'm trying to verify is that the update queries were run and each query
updates one column.

I'm trying to identify Columns that don't have any data (null or zero)
because this would indicate that the update query didn't work.

I'm not trying to identify and specific rows.
--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

 

[John Spencer]

So any column where all the data in the column is zero or null. Any single
column would be simple.

SELECT Count(Column) as ColCount
FROM Table
HAVING Count(Column) = 0

For multiple fields, you could use the following. Which returns a count for
every column

SELECT Count(TableA.SomeNumber) AS CountOfSomeNumber
, Count(TableA.Allnull) AS CountOfAllnull
, Count(TableA.AnotherNull) AS CountOfAnotherNull
FROM TableA

'Optionally adding criteria as follows to only return a record if at least
one of the fields is all null
HAVING (((Count(TableA.SomeNumber))=0)) OR (((Count(TableA.Allnull))=0)) OR
(((Count(TableA.AnotherNull))=0));

Counting zeroes or nulls is a bit tougher, but not much
SELECT COUNT(IIF(SomeNumber=0,Null, SomeNumber)) as CountZeroNull
FROM TableA

Hope that helps.

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

 

[Duane Hookom]

I use Field1 and Field2 because I didn't know what your actual field names
are. Note: you also didn't provide your table name.

If you want a specific query that should meet your needs, you should provide
your table and field names as well as the data types of the fields so we know
which are text and which are numbers.

 

[BillyRogers]

Duane,

I didn't have any questions about the table and field names you used. They
are perfectly understandable.

I just noticed that for Field1 you used the LEN function and for field2 to
you used the NZ function. I was wanting to know why you used different
criteria for the different fields.
Is the LEN criteria for numeric fields and the NZ for text fields?

SELECT UniqueID, "Field1" as BlankField
FROM tblNoNameGiven
WHERE Len(Field1 & "") = 0
UNION ALL
SELECT UniqueID, "Field2"
FROM tblNoNameGiven
WHERE Nz(Field2,0) = 0
UNION ALL

--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

 

[Duane Hookom]

Now I understand. I actually wanted to check text fields for Len() and
compare numeric fields to 0. The Nz([NumericField,0) will convert null values
in numerics to the number 0. The Len([TextField] & "") will convert nulls to
zero length strings and check their length.

 

[BillyRogers]

Here's what I came up with.

A count of all the fields and then - just look for columnames witha count
of zero.

Is there any flaw in this method?


SELECT Count([TableName].[FieldName1]) AS
[CountOfFieldName1],Count([TableName].[FieldName2]) AS [CountOfFieldName2]
from TableName


--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

 

[John Spencer]

No. That should work fine. and looks very much like what I proposed.

One problem is this will only detect nulls. You were talking abount
detecting all zeroes in addition to all nulls To do that you would need to
use the variation

SELECT Count(IIF(TableName.FieldName = 0, Null, TableName.FieldName) as
CountFieldName
FROM TableName

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

Here's what I came up with.

A count of all the fields and then - just look for columnames witha count
of zero.

Is there any flaw in this method?


SELECT Count([TableName].[FieldName1]) AS
[CountOfFieldName1],Count([TableName].[FieldName2]) AS [CountOfFieldName2]
from TableName


--
Billy Rogers

Dallas,TX

Currently Using SQL Server 2000, Office 2000 and Office 2003

So any column where all the data in the column is zero or null. Any
single
column would be simple.

SELECT Count(Column) as ColCount
FROM Table
HAVING Count(Column) = 0

For multiple fields, you could use the following. Which returns a count
for
every column

SELECT Count(TableA.SomeNumber) AS CountOfSomeNumber
, Count(TableA.Allnull) AS CountOfAllnull
, Count(TableA.AnotherNull) AS CountOfAnotherNull
FROM TableA

'Optionally adding criteria as follows to only return a record if at
least
one of the fields is all null
HAVING (((Count(TableA.SomeNumber))=0)) OR (((Count(TableA.Allnull))=0))
OR
(((Count(TableA.AnotherNull))=0));

Counting zeroes or nulls is a bit tougher, but not much
SELECT COUNT(IIF(SomeNumber=0,Null, SomeNumber)) as CountZeroNull
FROM TableA

Hope that helps.

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