If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Writing a query to return only rows that are null
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. |
#2
|
|||
|
|||
Writing a query to return only rows that are null
Seems like a crosstab query might do it. Post your table structure.
-- KARL DEWEY Build a little - Test a little " wrote: 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. |
#3
|
|||
|
|||
Writing a query to return only rows that are null
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 .. wrote in message ups.com... 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. |
Thread Tools | |
Display Modes | |
|
|