Similar to CountA in MS Excel

[chuaby]

Hi

currently i have a table in MS Access which has many fields A B C D E
F G for example.

I would like to get a summary of the number of "filled" columns in
each of these cols.

In Excel, i am using countA(A1:A10) etc to find how many row in Col A
does not contain empty fields. But i would like to get a count for all
cols.

May i know if i do not wish to export to Excel to do it,
is there anyway to do it in MS Access instead of SELECT count(*) ,
group by.

Thank you
Best Regards
Boon Yiang

 

[Guest]

You can create a query with SQL like:

SELECT Sum(IsNull([A]) +1) as CountA, Sum(IsNull() +1) as CountB,
Sum(IsNull([C]) +1) as CountC, ....
FROM tblNoNameGiven;

Performing counts like this some times suggest an un-normalized table
structure.

 

[John Spencer]

If you want a count of the number of times that a value is entered in a
column, then all you need is a totals query that looks like the following.

SELECT Count(A) as CountA
, Count(B) as CountB
, Count(C) as CountC
, Count(D) as CountD
, Count(*) as CountRows
FROM [YourTable]

In the query grid
-- Add all the fields you want to count to the fields grid
-- Select View: Totals from the menu
-- Change GROUP BY to COUNT under each field
--Run the query.

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

You can create a query with SQL like:

SELECT Sum(IsNull([A]) +1) as CountA, Sum(IsNull() +1) as CountB,
Sum(IsNull([C]) +1) as CountC, ....
FROM tblNoNameGiven;

Performing counts like this some times suggest an un-normalized table
structure.

--
Duane Hookom
Microsoft Access MVP

Hi

currently i have a table in MS Access which has many fields A B C D E
F G for example.

I would like to get a summary of the number of "filled" columns in
each of these cols.

In Excel, i am using countA(A1:A10) etc to find how many row in Col A
does not contain empty fields. But i would like to get a count for all
cols.

May i know if i do not wish to export to Excel to do it,
is there anyway to do it in MS Access instead of SELECT count(*) ,
group by.

Thank you
Best Regards
Boon Yiang

 

[Guest]

I'm not sure why I made the solution so complicated :-(

Also, Boon, I don't know if the posting contains your actual email address
but this is generally a ticket to getting a ton of spam in your in box. You
should mudge your email like chuaby AT hotmail DOT kom.
--
Duane Hookom
Microsoft Access MVP

If you want a count of the number of times that a value is entered in a
column, then all you need is a totals query that looks like the following.

SELECT Count(A) as CountA
, Count(B) as CountB
, Count(C) as CountC
, Count(D) as CountD
, Count(*) as CountRows
FROM [YourTable]

In the query grid
-- Add all the fields you want to count to the fields grid
-- Select View: Totals from the menu
-- Change GROUP BY to COUNT under each field
--Run the query.

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

You can create a query with SQL like:

SELECT Sum(IsNull([A]) +1) as CountA, Sum(IsNull() +1) as CountB,
Sum(IsNull([C]) +1) as CountC, ....
FROM tblNoNameGiven;

Performing counts like this some times suggest an un-normalized table
structure.

--
Duane Hookom
Microsoft Access MVP

Hi

currently i have a table in MS Access which has many fields A B C D E
F G for example.

I would like to get a summary of the number of "filled" columns in
each of these cols.

In Excel, i am using countA(A1:A10) etc to find how many row in Col A
does not contain empty fields. But i would like to get a count for all
cols.

May i know if i do not wish to export to Excel to do it,
is there anyway to do it in MS Access instead of SELECT count(*) ,
group by.

Thank you
Best Regards
Boon Yiang