need help with count query

[mcnews]

i need a query that will have a temp field for each record that
contains a total of yes/no fields in the record if they are checked.
there are many of these boolean fields in each record and they are
named A1 - A99, but not all 1-99 are used as names.

can this be done?

tia,
mcnewsxp

 

[Jeff Boyce]

If you are saying that you have a table that contains 99 fields, odds are
pretty good that you have ... a spreadsheet! A well-normalized table in a
relational database might have 30 fields.

If your table structure is "wide", the only way I'm aware of, inside Access,
to check on how many of those are checked is to use the name of each field
in an expression. This is a "brittle" design, though ... if you add or
subtract any of these A* fields, you'll have to rebuild your expressions and
queries and forms and reports and code and macros and ... -- a maintenance
nightmare!

Perhaps one of the other readers can offer a more-elegant solution than
simply naming and adding each and every field.

Were this mine, the very first thing I'd do is normalize the data structure.
With a relational table design, you'd only need to count records, not
fields.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[mcnews]

If you are saying that you have a table that contains 99 fields, odds are
pretty good that you have ... a spreadsheet! A well-normalized table in a
relational database might have 30 fields.

If your table structure is "wide", the only way I'm aware of, inside Access,
to check on how many of those are checked is to use the name of each field
in an expression. This is a "brittle" design, though ... if you add or
subtract any of these A* fields, you'll have to rebuild your expressions and
queries and forms and reports and code and macros and ... -- a maintenance
nightmare!

Perhaps one of the other readers can offer a more-elegant solution than
simply naming and adding each and every field.

Were this mine, the very first thing I'd do is normalize the data structure.
With a relational table design, you'd only need to count records, not
fields.

Regards

Jeff Boyce
Microsoft Office/Access MVP

sometimes we got what we got.
thanks.
next.

 

[Jeff Boyce]

Please re-read my response. You CAN do what you want with what you have,
but it will require that you call out/name each and every column in your
expression.

Access' features and functions work best on well-normalized data.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

You might be able to write a VBA function that would do what you want.

First question: Is there a primary key for each record? One field (or a
combination) that uniquely identifies the record and no other record.

IF so you could try something like the following really ugly UNTESTED
AIR CODE. Save this function in a VBA module and call it from your query

Field: CountYes: GetYesCount([PrimaryKeyField])

Assumption:
Your Primary Key field is a number field (Perhaps an autonumber)

Public Function GetYesCount(RecordID) as Long
Dim strSQL as String
Dim rstAny as DAO.Recordset
Dim dbAny as DAO.Database
Dim ICount as Integer
Dim IResult as Integer

StrSQL = "SELECT * FROM YourTable WHERE PrimaryKeyField = " & RecordID
Set DbAny = CurrentDb()
Set RstAny = Dbany.OpenRecordset (StrSQL)

On error resume Next
For ICount = 1 to 99
IResult = IResult + RstAny.Fields("A" & ICount)
Next ICount
On Error GoTo 0

GetYesCount = Abs(IResult)


End Function

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

 

[mcnews]

You might be able to write a VBA function that would do what you want.

First question: Is there a primary key for each record? One field (or a
combination) that uniquely identifies the record and no other record.

IF so you could try something like the following really ugly UNTESTED
AIR CODE. Save this function in a VBA module and call it from your query

Field: CountYes: GetYesCount([PrimaryKeyField])

Assumption:
Your Primary Key field is a number field (Perhaps an autonumber)

Public Function GetYesCount(RecordID) as Long
Dim strSQL as String
Dim rstAny as DAO.Recordset
Dim dbAny as DAO.Database
Dim ICount as Integer
Dim IResult as Integer

StrSQL = "SELECT * FROM YourTable WHERE PrimaryKeyField = " & RecordID
Set DbAny = CurrentDb()
Set RstAny = Dbany.OpenRecordset (StrSQL)

On error resume Next
For ICount = 1 to 99
IResult = IResult + RstAny.Fields("A" & ICount)
Next ICount
On Error GoTo 0

GetYesCount = Abs(IResult)

End Function

that looks good.
i'll try it.
thanks.

 

[mcnews]

You might be able to write a VBA function that would do what you want.

First question: Is there a primary key for each record? One field (or a
combination) that uniquely identifies the record and no other record.

IF so you could try something like the following really ugly UNTESTED
AIR CODE. Save this function in a VBA module and call it from your query

Field: CountYes: GetYesCount([PrimaryKeyField])

Assumption:
Your Primary Key field is a number field (Perhaps an autonumber)

Public Function GetYesCount(RecordID) as Long
Dim strSQL as String
Dim rstAny as DAO.Recordset
Dim dbAny as DAO.Database
Dim ICount as Integer
Dim IResult as Integer

StrSQL = "SELECT * FROM YourTable WHERE PrimaryKeyField = " & RecordID
Set DbAny = CurrentDb()
Set RstAny = Dbany.OpenRecordset (StrSQL)

On error resume Next
For ICount = 1 to 99
IResult = IResult + RstAny.Fields("A" & ICount)
Next ICount
On Error GoTo 0

GetYesCount = Abs(IResult)

End Function

works perfectly with very little editing!
thanks again.