Return 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__Skill6
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.

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...

many thanks.

 

[OddProject]

*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.

 

[OddProject]

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!

 

[spaddhu]

Hi,

I'm basically a SQL programmer currently working on Access. I don't see the feasibility of your requirement. What you want to do is conditionally select a column. This is possible on paper as you said. But in Access I doubt it's possibility.

 

[OddProject]

Hmm...

Could anyone suggest an alternative?

I have gathered a large data set, but not all of the data is required (only the feilds with at least one value and exclude the rest).

Why cant Access let me write a query like

Select *
From Table1
Where all feilds >= 1

Returning all feilds with values so I can run a report from the query.

I cannot compile a report as there are too many feilds.

I need an solution/alternative as a matter of urgency.

Thanks

 

[spaddhu]

Why cant Access let me write a query like

Select *
From Table1
Where all feilds >= 1

Returning all feilds with values so I can run a report from the query.

That is a real cool functionality you expect out of Access; as a matter of fact out of any Database Management System.
I'm afraid, I don't have the answer to this. Let's eagerly wait for some other input from others.

 

[OddProject]

Cant believe how simple this could be... Yet I cant find any help anywhere...

there must be 1000's of DB's out there with similar needs and there must be a solution.

 

[OddProject]

Can someone please help!

I need this urgently.

Any help is highly appreciated

 

[mihabaki]

Can someone please help!

I need this urgently.

Any help is highly appreciated

Base your report on a new table. The table should be empty and have the same structure as your source table/query.

Before your report open you should run VBA code that includes:

- create a recordset (SET rs = currendb.openrecordset("SELECT * FROM ... ;")) that selects all the rows from your source table
- then scroll through all the records (something like "Do Until rs.EOF ... Loop" statement)
- each record that meets your criteria (you have to check it in code) make an "INSERT INTO newTable..." statement.
- ...at the end open report (docmd.openreport ...)

M

 

[OddProject]

Base your report on a new table. The table should be empty and have the same structure as your source table/query.

Before your report open you should run VBA code that includes:

- create a recordset (SET rs = currendb.openrecordset("SELECT * FROM ... ;")) that selects all the rows from your source table
- then scroll through all the records (something like "Do Until rs.EOF ... Loop" statement)
- each record that meets your criteria (you have to check it in code) make an "INSERT INTO newTable..." statement.
- ...at the end open report (docmd.openreport ...)

M

Thanks for your reply.

Could I ask for an example? I'm not too hot with VBA in access. Am i correct in saying i need to create a module?

 

[OddProject]

Well... thanks.

Waste of time posting anything on here.

 

[mihabaki]

Well... thanks.

Waste of time posting anything on here.

I will post you a detailed example tomorrow morning (CET)

 

[Kryst51]

Well... thanks.

Waste of time posting anything on here.

Have a little patience, this is a volunteer site.

Especially when you've double posted.

 

[mihabaki]

OK, here it goes (I have to say this was a bit hard to prepare, because I realy don't understand your need for such a query) - also note that my original solution was not good.

Put this code behind the "on click" event of a command button on a form of your choice (it will create a new query and open it):

Private Sub cmdButton_Click()
    Dim sql As String, tdf As DAO.TableDef, fld As DAO.Field, db As DAO.Database
    
    'set database & tabledefs (I use "tblSource" as your table name)
    Set db = CurrentDb
    Set tdf = db.TableDefs("tblSource")
    
    'set first part of your sql statement
    sql = "SELECT ProjectCode"
    
    'scroll through all the fields in your table that start with 'skill'
    For Each fld In tdf.Fields
        If Left(fld.Name, 5) = "skill" Then
            'now check that this field has non-NULL record
            If Nz(DSum(fld.Name, "tblSource"), 0) > 0 Then
                sql = sql & ", " & fld.Name
            End If
        End If
    Next fld
    
    'finish you sql statement
    sql = sql & " FROM tblSource ORDER BY ProjectCode;"
    
    'first delete any previously created query "qryNew"
    Dim qry As DAO.QueryDef, ObjectExists As Boolean
    For Each qry In db.QueryDefs
        If qry.Name = "qryNew" Then
            ObjectExists = True
        End If
    Next qry
    If ObjectExists = True Then
        DoCmd.DeleteObject acQuery, "qryNew"
    End If
        
    'now open the new query with your newly created sql code
    Set QueryDef = db.CreateQueryDef("qryNew", sql)
    DoCmd.OpenQuery "qryNew"
    
    'clean up
    db.Close
    Set db = Nothing
    
End Sub

Rgs,
Miha

 

[OddProject]

OK, here it goes (I have to say this was a bit hard to prepare, because I realy don't understand your need for such a query) - also note that my original solution was not good.

Put this code behind the "on click" event of a command button on a form of your choice (it will create a new query and open it):

Private Sub cmdButton_Click()
    Dim sql As String, tdf As DAO.TableDef, fld As DAO.Field, db As DAO.Database
    
    'set database & tabledefs (I use "tblSource" as your table name)
    Set db = CurrentDb
    Set tdf = db.TableDefs("tblSource")
    
    'set first part of your sql statement
    sql = "SELECT ProjectCode"
    
    'scroll through all the fields in your table that start with 'skill'
    For Each fld In tdf.Fields
        If Left(fld.Name, 5) = "skill" Then
            'now check that this field has non-NULL record
            If Nz(DSum(fld.Name, "tblSource"), 0) > 0 Then
                sql = sql & ", " & fld.Name
            End If
        End If
    Next fld
    
    'finish you sql statement
    sql = sql & " FROM tblSource ORDER BY ProjectCode;"
    
    'first delete any previously created query "qryNew"
    Dim qry As DAO.QueryDef, ObjectExists As Boolean
    For Each qry In db.QueryDefs
        If qry.Name = "qryNew" Then
            ObjectExists = True
        End If
    Next qry
    If ObjectExists = True Then
        DoCmd.DeleteObject acQuery, "qryNew"
    End If
        
    'now open the new query with your newly created sql code
    Set QueryDef = db.CreateQueryDef("qryNew", sql)
    DoCmd.OpenQuery "qryNew"
    
    'clean up
    db.Close
    Set db = Nothing
    
End Sub

Rgs,
Miha

Hi thanks for the code but it doesn't seem to work. I changed all the Tablesource names to Table2 (my table name) and changed other bits for the first part and the closing part of the SQL select query, but when i hit the button nothing happens... no error or anything...

Heres the code:

Private Sub cmdButton_Click()
Dim sql As String, tdf As DAO.TableDef, fld As DAO.Field, db As DAO.Database

'set database & tabledefs (I use "tblSource" as your table name)
Set db = CurrentDb
Set tdf = db.TableDefs("Table2")

'set first part of your sql statement
sql = "SELECT Table2.[IS ref]"

'scroll through all the fields in your table that start with 'skill'
For Each fld In tdf.Fields
If Left(fld.Name, 5) = "IWP" Or "DEV" Or "CSG" Or "UTILITY" Then
'now check that this field has non-NULL record
If Nz(DSum(fld.Name, "Table2"), 0) > 0 Then
sql = sql & ", " & fld.Name
End If
End If
Next fld

'finish you sql statement
sql = sql & " FROM Table2 ORDER BY Table2.[IS Ref];"

'first delete any previously created query "qryNew"
Dim qry As DAO.QueryDef, ObjectExists As Boolean
For Each qry In db.QueryDefs
If qry.Name = "qryNew" Then
ObjectExists = True
End If
Next qry
If ObjectExists = True Then
DoCmd.DeleteObject acQuery, "qryNew"
End If

'now open the new query with your newly created sql code
Set QueryDef = db.CreateQueryDef("qryNew", sql)
DoCmd.OpenQuery "qryNew"

'clean up
db.Close
Set db = Nothing

End Sub

Many thanks

 

[OddProject]

k, i treid again with this code... still nothing happens at all...

Private Sub cmdButton_Click()
Dim sql As String, tdf As DAO.TableDef, fld As DAO.Field, db As DAO.Database
'set database & tabledefs (I use "tblSource" as your table name)
Set db = CurrentDb
Set tdf = db.TableDefs("Table2")
'set first part of your sql statement
sql = "SELECT Table1.[IS ref], Table1.[Date replied], Table1.[Business stream], Table1.[Project opportunity name], Table2.[IWP PM], Table2.[IWP New media analysis], Table2.[IWP EPS Analysis/Design], Table2.[IWP Delphi analysis/Design], Table2.[IWP Net Analysis/Design], Table2.[IWP Java Analysis/Design], Table2.[IWP RFI / RFP Process], Table2.[IWP Prototype Development], Table2.[IWP Feasibility Study Required], Table2.[IWP IM Consultancy Task], Table2.[IWP Other], Table2.[IWP Other1], Table2.[IWP Other2], Table2.[Dev Review Requirements / Confirmation], Table2.[Dev Feasibility Study Required], Table2.[Dev Solution Architect / Systems Analysis], Table2.[Dev RFI Process Required], Table2.[Dev Design], Table2.[Dev Prototype Development], Table2.[Dev First Phase Development], Table2.[Dev Use Case Based Estimation], Table2.[Dev Subsequent Phase Development], Table2.[Dev Systems Test], Table2.[Dev UAT Support and Fixes], Table2.[Dev Implementation], Table2.[Dev Documentation], Table2.[Dev Post Live Support]"

'scroll through all the fields in your table that start with 'skill'
For Each fld In tdf.Fields
If Left(fld.Name, 5) = "IWP" Or "DEV" Or "CSG" Or "UTILITY" Or "Offshore" Or "OutSource" Then
'now check that this field has non-NULL record
If Nz(DSum(fld.Name, "Table2"), 0) > 0 Then
sql = sql & ", " & fld.Name
End If
End If
Next fld
'finish you sql statement
sql = sql & " FROM Table1 INNER JOIN Table2 ON Table1.[IS ref] = Table2.[IS ref];"
'first delete any previously created query "qryNew"
Dim qry As DAO.QueryDef, ObjectExists As Boolean
For Each qry In db.QueryDefs
If qry.Name = "qryNew" Then
ObjectExists = True
End If
Next qry
If ObjectExists = True Then
DoCmd.DeleteObject acQuery, "qryNew"
End If
'now open the new query with your newly created sql code
Set QueryDef = db.CreateQueryDef("qryNew", sql)
DoCmd.OpenQuery "qryNew"
'clean up
db.Close
Set db = Nothing
End Sub

'finish you sql statement
sql = sql & " FROM Table1 INNER JOIN Table2 ON Table1.[IS ref] = Table2.[IS ref];;"

'first delete any previously created query "qryNew"
Dim qry As DAO.QueryDef, ObjectExists As Boolean
For Each qry In db.QueryDefs
If qry.Name = "qryNew" Then
ObjectExists = True
End If
Next qry
If ObjectExists = True Then
DoCmd.DeleteObject acQuery, "qryNew"
End If

'now open the new query with your newly created sql code
Set QueryDef = db.CreateQueryDef("qryNew", sql)
DoCmd.OpenQuery "qryNew"

'clean up
db.Close
Set db = Nothing

End Sub

 

[JohnLee]

The problem you have is that your not thinking in terms of how Access works. From the information you have provided you have a table which has the following fields:

Project Code
Skill1
Skill2
Skill3
Skill4
Skill5
Skill6

Now when you open up your table or query the fields are displayed as follows:

Project Code Skill1 Skill2 Skill3 Skill4 Skill5 Skill6
AA00001 1 4
AA00002
AA00003 3 2 1
AA00004 1

Now in your query grid if you put the following in the criteria for each field “Is Not Null”

Access will display all of the records or none of the records because either all of the record or none of the records meets the criteria for each and every field.

The problem lies within the way you have set up your table and not the query and because your asking Access to do something that’s not within it’s designed ability.

It also appears to me like your trying to treat an Access table like a spreadsheet, which of course it is not.

My approach would be to have a table that lists all the various skills and another table that lists the different number of days for instance:

A table called tblSkills with the following field:

StrSkills [field length to whatever length you require upto 255 characters]

Then in the table view enter each skill type thus

Skill1
Skill2
Skill3
etc. etc.

Then create another table for the number of days in the same mannor.

You will then need to establish a relationship with these two tables and of the relevant projects in your projects table, once you have done that you can then tackle the problem you are looking to resolve.

I hope this has been of some assistance.

John

 

[mihabaki]

k, i treid again with this code... still nothing happens at all...

Private Sub cmdButton_Click()
Dim sql As String, tdf As DAO.TableDef, fld As DAO.Field, db As DAO.Database
'set database & tabledefs (I use "tblSource" as your table name)
Set db = CurrentDb
.
.
.


'clean up
db.Close
Set db = Nothing

End Sub

does you code execute at all??? did you add the module to your command button on the form (open form in design view and check the button's properties) and also is your button's name "cmdButton" ??

miha

 

[PNGBill]

What if you were to create a calculated field for each skill via an expression that entered either 1 or 0 depending on the original field being null or not.
Then total all the new fields and if they are all null then your sum will be less then 1.
Your final filter only looks at records where the total for the calculated fields >0.
This would be a job to setup in query and of course need to be updated when skills are added.
VBA can do a routine that will run through the fields one at a time but not an expert on this.
Trust this helps.