Number of Fields in a query that are not null (1 Viewer)

[msp]

I have created a query that gives me results of records from across two tables. Currently it returns about 450 rows and 16 columns. What I would like to-do is find out what the number of the fields within the query that are null (or filled in). I guess there is a simple way to do this.

Thanks for any help

 

[Mile-O]

Why would you have so many Null fields?

 

[msp]

Why would you have so many Null fields?

we are collecting data on system/server attributes currently I guess about 80% is completed, However we have not collected data on all the attributes hence the fields are blank.

 

[Mile-O]

I guess there is a simple way to do this.

It's just that you would do summary calculations on records in a query and not on fields.

 

[msp]

It's just that you would do summary calculations on records in a query and not on fields.

So what are you saying? That it cannot be done ?

 

[Mile-O]

So what are you saying? That it cannot be done ?

I'm saying that I don't understand your thinking.

 

[cable]

I think I might, you want to know how much data you've yet to fill in.

a simple way would to have a query that just selects the fields that are null then count the records in that query. the total systems - the total from the query would then be the number of systems with missing info.

For number of systems missing the 'memory size' for example, you would a query that just returned the nulls in 'memory size' ie you would need a query for each attribute you are gathering.

At least thats how i'd start...you might find easier methods as you go long.

If you want to count nulls accross rather than down then your probly best with code, as I can't think of a simple query solution.

 

[Mile-O]

I think the problem is msp's database is not normalised and that's why the problem is occurring.

i.e. the table design isn't meeting First Normal Form and the fields are components of a repeating group whereby they might be something like:

Attribute1, Attribute2, Attribute3, Attribute4, Attribute5

msp, am I right?

 

[msp]

Sorry I will try and try explain more fully.

We are collecting data from different sources and populating the database. I have created a query that displays the records that we are particularly interested in and shows that attributes we have collected so far. (This is the one described above)

For reporting purposes there is a requirement to have a value of the percentage of attributes collected so far. My thinking was that if I could have a count of the fields within the query that are blank (or null) and thus we have not collected that attribute data I could produce a value for the amount of data collected (and to be collected)

 

[msp]

I think I might, you want to know how much data you've yet to fill in.

Yes that is all I am trying to do, the database is normalised, I just have blanks where no data has been entered yet.

 

[Mile-O]

What's the structure of these two tables?

 

[Mile-O]

the database is normalised

If that's the case and it is normalised then all you need to do is this.

 

[msp]

I can post the structure of the two tables, whoever the query I run shows the correct information I just wish to know which fields within that data have not been completed yet?

If you believe that supplying the table structure would help with this problem I am happy to oblige, I am just unsure if this is the problem. As even if I run the a query on one table I am unsure of gaining the information I wish.

 

[msp]

Mile/Cable

We seem to be getting our replies slightly out of synch.

Thanks for the help I will check out you suggestions this afternoon let you know how I get on...

 

[Pat Hartman]

You can write a totals query that counts what's filled in and by inference figure out what's empty.

Select Count(*) As RowCount, Count(fldA) As fldACount, Count(fldB) As fldBCount, Count(fldC) As fldCCount, .....

When you use Count(fieldname) you are counting the number of non-null values. When you use Count(*) you are counting the number of rows.