Writing a query to return only rows that are null

[hola717]

I am trying figure out how to get access to do the following:

Column 1 = Name
Column 2 = Project1
Column 3 = Project2
Column 4 = Project3
Column 5 = Project4

The values for Project1 - Project4 can be null or have a num value. I
would like to see a list that contains the Name and whatever that Name
has that is null.

Name Project1 Project2 Project3 Project4
Jane 4 5
Joe 4 4

What I would like to see as the result of a query is:
Jane Project2, Project4
Joe Project1, Project3

I dont really care how it is displayed.

I will be trying to accomplish this with 200 columns and 200 rows.

 

[Guest]

Seems like a crosstab query might do it. Post your table structure.

 

[John Spencer]

The only reasonable approach I can see is to use a vba function. To examine
each field in the table and return the names concatenated.

Even to attempt this , I would have to know some more about your structure.
Assuming that all the columns other than Column 1 are what you want
returned. So making the assumption the following AIR CODE - Untested might
work.

Paste the code into a module and call it in your query with the column1 -
name as the argument

Your SQL statement might look something like the follwoing.

SELECT Column1Name, ListActiveFields([Column1Name]) as ProjList
FROM [YourTableName]

Public Function ListActiveFields (strNameIn) as string
Dim strSQL as String
Dim rstAny as DAO.Recordset
Dim dbAny as DAO.Database
Dim fldAny as DAO.Field
Dim LngCount as Long
Dim strReturn

strSQL = "SELECT * FROM [YourTableName] WHERE [Column1Name] = """ &
strNameIn & """"
set dbany = dbengine(0)(0)
set rstAny = dbany.OpenRecordset (strSQL)

if rstany.recordcount > 0 then
For lngCount = 1 to .Fields.Count -1
If IsNull(.Fields(lngCount)) = False then
strReturn = strReturn & .Fields(lngCount).Name & ", '"
End if
Next lngCount
end if
If Len(strReturn) > 0 Then
strReturn = Left(strReturn,Len(StrReturn)-2)
end if

ListActiveFields = strReturn
End Function

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