Counting yes no fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two related tables -- and a field ARTNAME that is the common field in
both. ARTNAME is the primary key in TableA and is the foreign key in Table B.

There is a field in the primary table called ARTDESC

In the Child table there are two yes/no fields -- PAINTING and SCULPTURE.

What I would like to do is count the number of Paintings and Sculptures for
each ARTDESC.

I would like the fields in the resulting query to be:
ARTDESC PAINTINGS SCULPTURE
Madonna and Child 4 12

The above shows that there are 4 paintings and 12 scuptures (16 items) that
come under the description Madonna and Child. How can I get this. I can get
one (either Paintings or Sculptures) but am not able to get both!

Is this supposed to be a Crosstab query. Please help if you can.

Thanks
 
The the record can only be Painting or Sculpture then you really should use
a single field that would store the art type. However, to count yes/no
fields, you can Sum() their absolute value.

=Sum( Abs([Paintings]) )
 
Thank you Duane. Your solution worked beautifully and this is the one that I
used. I hope that you don't consider it overkill another way shown to me by a
friend. It is a bit more complicated than yours but comes up with the same
results.

I brought down ARTDESC from the Main Table and PAINTING and SCULPTURE from
the daughter table

ARTDESC was grouped in the totals row while the other two fields had an IIF
statement in the field row and Expression in the Total row.. E.g.
Sum(IIf([Painting],1,0))

Thank you again for your help. It is greatly appreciated and will prove very
helpful in several future queries in this project.

Duane Hookom said:
The the record can only be Painting or Sculpture then you really should use
a single field that would store the art type. However, to count yes/no
fields, you can Sum() their absolute value.

=Sum( Abs([Paintings]) )


--
Duane Hookom
MS Access MVP


Elaine said:
I have two related tables -- and a field ARTNAME that is the common field
in
both. ARTNAME is the primary key in TableA and is the foreign key in
Table B.

There is a field in the primary table called ARTDESC

In the Child table there are two yes/no fields -- PAINTING and SCULPTURE.

What I would like to do is count the number of Paintings and Sculptures
for
each ARTDESC.

I would like the fields in the resulting query to be:
ARTDESC PAINTINGS SCULPTURE
Madonna and Child 4 12

The above shows that there are 4 paintings and 12 scuptures (16 items)
that
come under the description Madonna and Child. How can I get this. I can
get
one (either Paintings or Sculptures) but am not able to get both!

Is this supposed to be a Crosstab query. Please help if you can.

Thanks
Click to expand...
Click to expand...
 
That works well also.

I still would prefer to not use data values as field names. I'm not sure how
you would add another art type without having to add a field and various
controls as well as modifying queries.

--
Duane Hookom
MS Access MVP


Elaine said:
Thank you Duane. Your solution worked beautifully and this is the one that
I
used. I hope that you don't consider it overkill another way shown to me
by a
friend. It is a bit more complicated than yours but comes up with the same
results.

I brought down ARTDESC from the Main Table and PAINTING and SCULPTURE from
the daughter table

ARTDESC was grouped in the totals row while the other two fields had an
IIF
statement in the field row and Expression in the Total row.. E.g.
Sum(IIf([Painting],1,0))

Thank you again for your help. It is greatly appreciated and will prove
very
helpful in several future queries in this project.

Duane Hookom said:
The the record can only be Painting or Sculpture then you really should
use
a single field that would store the art type. However, to count yes/no
fields, you can Sum() their absolute value.

=Sum( Abs([Paintings]) )


--
Duane Hookom
MS Access MVP


Elaine said:
I have two related tables -- and a field ARTNAME that is the common
field
in
both. ARTNAME is the primary key in TableA and is the foreign key in
Table B.

There is a field in the primary table called ARTDESC

In the Child table there are two yes/no fields -- PAINTING and
SCULPTURE.

What I would like to do is count the number of Paintings and Sculptures
for
each ARTDESC.

I would like the fields in the resulting query to be:
ARTDESC PAINTINGS SCULPTURE
Madonna and Child 4 12

The above shows that there are 4 paintings and 12 scuptures (16 items)
that
come under the description Madonna and Child. How can I get this. I can
get
one (either Paintings or Sculptures) but am not able to get both!

Is this supposed to be a Crosstab query. Please help if you can.

Thanks
Click to expand...
Click to expand...
Click to expand...
 
Back
Top