Displaying a list of nulls only

C

Chris

Hi Group,

I am using Northwind to test 17 fields for occurances of
nulls.
I have designed a query (SQL provided below) that returns
538 nulls from the Orders Table, but for the purposes of
the report I don't require all the record information,
instead can Access return just the field(s) that contains
the null(s) only but in a 'list-style format', like so...

EmployeeID FieldContainingNull
1 RequiredDate
1 OrderDate
8 RequiredDate
9 RequiredDate
9 OrderDate
9 ShipName

The output is required for further production of a pivot
report to identify nulls by employee only.
Very grateful for any help or suggestions.

Chris

SELECT Employees.EmployeeID, Orders.RequiredDate,
Orders.ShipVia, Employees.FirstName, Employees.LastName,
Orders.OrderDate, Orders.ShippedDate, Orders.Freight,
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipRegion, Orders.ShipPostalCode,
Orders.ShipCountry, Employees.Title, Employees.BirthDate,
Employees.HireDate
FROM Employees INNER JOIN Orders ON Employees.EmployeeID =
Orders.EmployeeID
WHERE (((Orders.RequiredDate) Is Null)) OR
(((Orders.ShipVia) Is Null)) OR (((Orders.OrderDate) Is
Null)) OR (((Orders.ShippedDate) Is Null)) OR
(((Orders.Freight) Is Null)) OR (((Orders.ShipName) Is
Null)) OR (((Orders.ShipAddress) Is Null)) OR
(((Orders.ShipCity) Is Null)) OR (((Orders.ShipRegion) Is
Null)) OR (((Orders.ShipPostalCode) Is Null)) OR
(((Orders.ShipCountry) Is Null)) OR
(((Employees.FirstName) Is Null)) OR
(((Employees.LastName) Is Null)) OR (((Employees.Title) Is
Null)) OR (((Employees.BirthDate) Is Null)) OR
(((Employees.HireDate) Is Null))
ORDER BY Employees.EmployeeID;
 
J

John Spencer (MVP)

About the only way I can see to do this type of thing is to use a UNION query as
the base. And then you run into limits on the number of Select statements you
can use in a Union query.


SELECT EmployeeID, "RequiredDate" as NullField
FROM Employees Inner Join Orders
ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.RequiredDate is Null
UNION All
SELECT EmployeeID, "ShipVia"
FROM Employees Inner Join Orders
ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.Shipvia is Null
UNION ALL
SELECT ...

That would give you the needed data in a query. Another method would be to
build a table to hold the data and use a series of append queries to populate
the table. You could probably automate a good part of that using VBA.
 
C

Chris

John,

This is interesting. I had thought about the union query
approach but I was not aware of the limit on the number of
select statements that you can use in a union query. Do
you know what it is? I have about 100 fields that I need
to query.

I am very interested in your suggestion in automating the
solution using VBA and append queries. I'm not well
versed in VBA so I was wondering could I trouble you for
an example.

Thanks

Chris
 
J

John Spencer (MVP)

I'm pretty sure you'll hit the limit with 100 separate select statements.

As far as writing the code, I'm afraid it would take me a while. Longer than I
normally devote to answering a question.

Simplest method would be to build two tables.
Table1 would hold the field names and possibly the table names (if you were
doing multiple tables)

Table2 would hold
IdentifierField
TableName
FieldName

Then In VBA I would open a recordset based on table1 and step through it and
populate table2.

That said here is SOME UNTESTED AIRCODE off the top of my head. No Warrantee,
Guarantee, or promise that this will work

Public Sub FindNullFields()
Dim dbAny as DAO.Database, rstSource as DAO.RecordSet
Dim StrSQL as String

Set DbAny = CurrentDB()
Set rstSource = DbAny.OpenRecordSet ("SELECT FieldName, TableName FROM Table2")

With rstSource
While Not .EOF
strSQL = "INSERT Into Table2 " & _
" (IdentifierField, TableName, FieldName) " & _
" SELECT S.EmployeeID, S.[" & !TableName & _
"], S.[" & !FieldName & "] " & _
" FROM [" & !Tablename & "] as S" & _
" WHERE S.[" & !Fieldname & "] is null"
DbAny.Execute StrSQL

.MoveNext
WEND
End With

End Sub

Play with it and see if you can get it to work.
 
C

Chris

John,

100 select statement is more than enough for my union
query (I'm only using 29). Thanks also for the aircode -
it provides a working alternative to the union method.

Regards

Tony
-----Original Message-----
I'm pretty sure you'll hit the limit with 100 separate select statements.

As far as writing the code, I'm afraid it would take me a while. Longer than I
normally devote to answering a question.

Simplest method would be to build two tables.
Table1 would hold the field names and possibly the table names (if you were
doing multiple tables)

Table2 would hold
IdentifierField
TableName
FieldName

Then In VBA I would open a recordset based on table1 and step through it and
populate table2.

That said here is SOME UNTESTED AIRCODE off the top of my head. No Warrantee,
Guarantee, or promise that this will work

Public Sub FindNullFields()
Dim dbAny as DAO.Database, rstSource as DAO.RecordSet
Dim StrSQL as String

Set DbAny = CurrentDB()
Set rstSource = DbAny.OpenRecordSet ("SELECT FieldName, TableName FROM Table2")

With rstSource
While Not .EOF
strSQL = "INSERT Into Table2 " & _
" (IdentifierField, TableName, FieldName) " & _
" SELECT S.EmployeeID, S.[" & !TableName & _
"], S.[" & !FieldName & "] " & _
" FROM [" & !Tablename & "] as S" & _
" WHERE S.[" & !Fieldname & "] is null"
DbAny.Execute StrSQL

.MoveNext
WEND
End With

End Sub

Play with it and see if you can get it to work.
John,

This is interesting. I had thought about the union query
approach but I was not aware of the limit on the number of
select statements that you can use in a union query. Do
you know what it is? I have about 100 fields that I need
to query.

I am very interested in your suggestion in automating the
solution using VBA and append queries. I'm not well
versed in VBA so I was wondering could I trouble you for
an example.

Thanks

Chris
is
to use a UNION query as of
Select statements you Another
method would be to append
queries to populate (((Orders.ShipRegion)
Is (((Employees.Title)
Is
Click to expand...
.
Click to expand...
 
J

John Spencer (MVP)

Uhmm! I didn't mean the limit was 100. I meant that long before you reached
100, you would get an error.

Try it and see if you can get it to work with 29. Maybe yes, maybe no.
John,

100 select statement is more than enough for my union
query (I'm only using 29). Thanks also for the aircode -
it provides a working alternative to the union method.

Regards

Tony
-----Original Message-----
I'm pretty sure you'll hit the limit with 100 separate select statements.

As far as writing the code, I'm afraid it would take me a while. Longer than I
normally devote to answering a question.

Simplest method would be to build two tables.
Table1 would hold the field names and possibly the table names (if you were
doing multiple tables)

Table2 would hold
IdentifierField
TableName
FieldName

Then In VBA I would open a recordset based on table1 and step through it and
populate table2.

That said here is SOME UNTESTED AIRCODE off the top of my head. No Warrantee,
Guarantee, or promise that this will work

Public Sub FindNullFields()
Dim dbAny as DAO.Database, rstSource as DAO.RecordSet
Dim StrSQL as String

Set DbAny = CurrentDB()
Set rstSource = DbAny.OpenRecordSet ("SELECT FieldName, TableName FROM Table2")

With rstSource
While Not .EOF
strSQL = "INSERT Into Table2 " & _
" (IdentifierField, TableName, FieldName) " & _
" SELECT S.EmployeeID, S.[" & !TableName & _
"], S.[" & !FieldName & "] " & _
" FROM [" & !Tablename & "] as S" & _
" WHERE S.[" & !Fieldname & "] is null"
DbAny.Execute StrSQL

.MoveNext
WEND
End With

End Sub

Play with it and see if you can get it to work.
John,

This is interesting. I had thought about the union query
approach but I was not aware of the limit on the number of
select statements that you can use in a union query. Do
you know what it is? I have about 100 fields that I need
to query.

I am very interested in your suggestion in automating the
solution using VBA and append queries. I'm not well
versed in VBA so I was wondering could I trouble you for
an example.

Thanks

Chris

-----Original Message-----
About the only way I can see to do this type of thing is
to use a UNION query as
the base. And then you run into limits on the number of
Select statements you
can use in a Union query.


SELECT EmployeeID, "RequiredDate" as NullField
FROM Employees Inner Join Orders
ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.RequiredDate is Null
UNION All
SELECT EmployeeID, "ShipVia"
FROM Employees Inner Join Orders
ON Employees.EmployeeID = Orders.EmployeeID
WHERE Orders.Shipvia is Null
UNION ALL
SELECT ...

That would give you the needed data in a query. Another
method would be to
build a table to hold the data and use a series of append
queries to populate
the table. You could probably automate a good part of
that using VBA.


Chris wrote:

Hi Group,

I am using Northwind to test 17 fields for occurances of
nulls.
I have designed a query (SQL provided below) that
returns
538 nulls from the Orders Table, but for the purposes of
the report I don't require all the record information,
instead can Access return just the field(s) that
contains
the null(s) only but in a 'list-style format', like
so...

EmployeeID FieldContainingNull
1 RequiredDate
1 OrderDate
8 RequiredDate
9 RequiredDate
9 OrderDate
9 ShipName

The output is required for further production of a pivot
report to identify nulls by employee only.
Very grateful for any help or suggestions.

Chris

SELECT Employees.EmployeeID, Orders.RequiredDate,
Orders.ShipVia, Employees.FirstName, Employees.LastName,
Orders.OrderDate, Orders.ShippedDate, Orders.Freight,
Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,
Orders.ShipRegion, Orders.ShipPostalCode,
Orders.ShipCountry, Employees.Title,
Employees.BirthDate,
Employees.HireDate
FROM Employees INNER JOIN Orders ON
Employees.EmployeeID =
Orders.EmployeeID
WHERE (((Orders.RequiredDate) Is Null)) OR
(((Orders.ShipVia) Is Null)) OR (((Orders.OrderDate) Is
Null)) OR (((Orders.ShippedDate) Is Null)) OR
(((Orders.Freight) Is Null)) OR (((Orders.ShipName) Is
Null)) OR (((Orders.ShipAddress) Is Null)) OR
(((Orders.ShipCity) Is Null)) OR (((Orders.ShipRegion)
Is
Null)) OR (((Orders.ShipPostalCode) Is Null)) OR
(((Orders.ShipCountry) Is Null)) OR
(((Employees.FirstName) Is Null)) OR
(((Employees.LastName) Is Null)) OR (((Employees.Title)
Is
Null)) OR (((Employees.BirthDate) Is Null)) OR
(((Employees.HireDate) Is Null))
ORDER BY Employees.EmployeeID;
.
Click to expand...
.
Click to expand...
Click to expand...
 
Top