Selecting all excluding Nulls

[OddProject]

Hi, I have a signle table with a large number of feilds (project ref, name dates etc... and a large number of skills). the skill feilds hold a number (number of days) assigned to each skill. If no values are entered no skill for that particular project is needed.

Many of these feilds remain null, but many have a value (but not for all rows).

I want a query to retrieve all results excluding a feild with no values at all (all null).

Example:

Project code:_Skill1__skill2__skill3__skill4__Skill5__Skil l6
AA00001______ 1__________ 4
AA00002
AA00003_______3__________2_____1
AA00004______________________________1

Now if I use a query to select all rows and use the criteria Is not null (on each individual feild) it returns nothing at all, due to the fact that each skill has a null. all i want excluding from the example above are feilds 'skill2 and skill6'. Would this have to be a manual operation to find each feild with no vaules and leave them out of my query? I would much prefere a query to exclude each feild with 0 values for every project.

Keeping in mind I have over 30 skills in my table.

I also tried adding a 0 instead of a null and trying >0 as the criteria, but returns no results much like the Is not null.

Note, I need this for reporting, I can't include all the feilds (null) as 1. Access wont let me and 2. Readability.

I just need a method to present all the data with values for each project excluding feilds with no vaules at all.

P.s I also have another relational DB (a test) keeping the skills in a seperate table (using the project code for Primary/Foreign key), I cannot find a method for either way...

Can someone please help me... I am getting nowhere and have run out of ideas? Is what im asking actually possible? It seems to me that it is very simple (on paper) to simply exlude a feild with no values, I just need to know how to put it into practice and exlcude multiple feilds.

Please help,

Thank you!

 

[Poppa Smurf]

In your query add another column and add the following in the field
checksum: nz([f1]+[f2]+[f3]+[f4]+[f5]+[f6]......[f30]), then in the criteria add >0.
[f1] ... [f30] is the name of your skill fields. If any field is NULL then a 0 is assign to that field to give a total depending on the value of the other fields. Now is ALL fields are null then each field will have a value of 0 assign to it to give a checksum value of 0 using the >0 will exclude the 0 total.

 

[OddProject]

In your query add another column and add the following in the field
checksum: nz([f1]+[f2]+[f3]+[f4]+[f5]+[f6]......[f30]), then in the criteria add >0.
[f1] ... [f30] is the name of your skill fields. If any field is NULL then a 0 is assign to that field to give a total depending on the value of the other fields. Now is ALL fields are null then each field will have a value of 0 assign to it to give a checksum value of 0 using the >0 will exclude the 0 total.

Thank you for your reply.

Will this return all the fields with values? I already tried adding the criteria on each individual feild with >0, Is not Null etc... but it wouldnt return a signle record.

Does this extra column just add a '0' to every row without a value? much like a default setting for the feild as '0'?

because I already manually added 0 to every cell without a value then tried >0 on each skill criteria but again, retrieved no records

Thanks so much for your reply.

 

[OddProject]

Thank you for your reply.

Will this return all the fields with values? I already tried adding the criteria on each individual feild with >0, Is not Null etc... but it wouldnt return a signle record.

Does this extra column just add a '0' to every row without a value? much like a default setting for the feild as '0'?

because I already manually added 0 to every cell without a value then tried >0 on each skill criteria but again, retrieved no records

Thanks so much for your reply.

Well i began to try your method for the first 4 feilds, but all this column seems to do is sum the values from these feilds and doesnt return any actual values

 

[OddProject]

I tried with a portion of my database using this query and adding the extra column

Checksum: nz([IS Ref]+[Business stream]+[Project opportunity name]+[IWP PM]+[IWP New media analysis]+[IWP EPS Analysis/Design]):

SELECT Table1.[IS ref], Table1.[Business stream], Table1.[Project opportunity name], Table2.[IWP PM], Table2.[IWP New media analysis], Table2.[IWP EPS Analysis/Design]

FROM Table1 INNER JOIN Table2 ON Table1.[IS ref] = Table2.[IS ref]

WHERE (((Table1.[IS ref])>"0") AND ((Table1.[Business stream])>"0") AND ((Table1.[Project opportunity name])>"0") AND ((Table2.[IWP PM])>0) AND ((Table2.[IWP New media analysis])>0) AND ((Table2.[IWP EPS Analysis/Design])>0));

But no results retrieved at all

 

[Poppa Smurf]

I have a sample database

table tbl_sum has data type Number for the fields f1 to f6 Query1 will give you results for >0

the table tbl_skills has data type Text for the fields f1 to f6 Query1 will give you results for >0

note for the second query using a text field you must assign a value to the field if it is null as follows.
checksum: nz([f1],0)+nz([f2],0)+nz([f3],0)+nz([f4],0)+nz([f5],0)+nz([f6],0)

 

[OddProject]

All this check sum does is add all the values on the end of the query...

None of the feilds you have in the table have no values, there is at least one value in each feild.

In query2 for example:

if F5 did not have the value '3' for rec_no '3' I would want that excluded (f5) from the results of my query (as it has no values in).

I assume this example will exclude any values of 0 in the checksum column (horizontally) where as i want the entire feild (vertically) excluded from my query.

 

[OddProject]

All this check sum does is add all the values on the end of the query...

None of the feilds you have in the table have no values, there is at least one value in each feild.

In query2 for example:

if F5 did not have the value '3' for rec_no '3' I would want that excluded (f5) from the results of my query (as it has no values in).

I assume this example will exclude any values of 0 in the checksum column (horizontally) where as i want the entire feild (vertically) excluded from my query.

Your method removes 'Records' if >0.
I want Feilds removing if there are no values for any record.

 

[OddProject]

Anyone have any ideas?

 

[Poppa Smurf]

Back to your original question

Note, I need this for reporting, I can't include all the feilds (null) as 1. Access wont let me and 2. Readability.

I just need a method to present all the data with values for each project excluding feilds with no vaules at all.

Question 1 - How many records are in your table?

Question 2 - Why do you need to exclude NULL fields?

Question 3- I assume that you will be using a report to display the results. How will you make the report dynamic to display only the required fields e.g. Skill 2 may be null for all records then next time only skill 16 will be Null?

 

[apr pillai]

If you can use VBA I think we can solve this problem.

1. Create a Make-Table Total Query and Sum-up all the Skills Field Values alone individually and create a Table with a single record.

2. Write a VBA Routine to read the field values one by one to check whether they have values >0 and build an SQL String with Skill Field Names with values in them and exclude the one with zeroes/Nulls.

3. Add other field names from the Original Source table and define the Output Query with the SQL.

If you give me some time I can bring in the example.

 

[apr pillai]

The sample VBA Code is given below:

Public Function CreateQuery()
Dim db As Database, rst As Recordset
Dim qrydef As QueryDef, sql1 As String, sql2 As String
Dim SQL As String
Dim fldCount As Integer, j As Integer

sql1 = "SELECT ProjectCode, "

Set db = CurrentDb
Set rst = db.OpenRecordset("Trans_Out", dbOpenDynaset)
fldCount = rst.Fields.Count
sql2 = ""
For j = 0 To fldCount - 1
  If rst.Fields(j).Value > 0 Then
    sql2 = sql2 & rst.Fields(j).Name & ","
  End If
Next
sql2 = Left(sql2, Len(sql2) - 1)

SQL = sql1 & sql2 & " FROM Trans;"

Set qrydef = db.QueryDefs("Trans_ExclNullQ")
qrydef.SQL = SQL
db.QueryDefs.Refresh

End Function

Need to create the sample Query Trans_ExclNullQ with at least one field from the Source Table Trans before running the above Code.

Since, there is only one record in the Summary Table Trans_Out no need to check the .EOF status of the file.