How do I count text fields?

[TonyWilliams via AccessMonster.com]

I have a table which contains a text field where the value can be "PureABL"
or "HYBRID" I want to be able to count how many records where the value of
the text field is PureABL and how many where the value of the text field is
Hybrid. I've created a query and used the Count function and put the two
descriptions in the Criteria but I get a Data type mismatch error.

Can some explain how I create a query where I can count text fields?
Thanks
Tony

 

[Allen Browne]

1. In query design, depress the Total button on the toolbar. Access adds a
Total row to the query design grid.

2. In the Total row under this text field, accept Group By.

3. Add the text field again (i.e. in a 2nd column.)
In the Total row this time, choose Count.

 

[Jeff Boyce]

Tony

Have you looked into using a "Totals" query? You could use "GroupBy" on
that field, and "Count" on the table's primary key field ... your table DOES
have a primary key field, right?!

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Marshall Barton]

I have a table which contains a text field where the value can be "PureABL"
or "HYBRID" I want to be able to count how many records where the value of
the text field is PureABL and how many where the value of the text field is
Hybrid. I've created a query and used the Count function and put the two
descriptions in the Criteria but I get a Data type mismatch error.

Can some explain how I create a query where I can count text fields?

Your gray cells still comunicate? ;-)

Try this kind of query:

SELECT thetextfield, Count(*) As CountOfSomething
FROM thetable
GROUP BY thetextfield

If that's too trivial for whatever you are trying to do,
please provide more details about what you want is the
query's result dataset.

 

[TonyWilliams via AccessMonster.com]

Thanks guys. I've tried all your suggestions and whilst I can get the query
to count all the records I want it to count how many records have the value
of my field equal to Pure ABL and how many of the records have the value
Hybrid.
How do I design the query to do those two counts?
Thanks for your help and a Happy New Year!
Tony

 

[Bob Barrows]

Thanks guys. I've tried all your suggestions and whilst I can get the
query to count all the records I want it to count how many records
have the value of my field equal to Pure ABL and how many of the
records have the value Hybrid.
How do I design the query to do those two counts?
Thanks for your help and a Happy New Year!
Tony

Switch your query to SQL View so you can show us what you tried. Here is
what the sql should look like:

select yourtextfield, count(*)
from yourtable
where yourtextfield in ("PureABL","HYBRID")
group by yourtextfield

 

[TonyWilliams via AccessMonster.com]

Hi Bob here's my sql along the lines that you suggested
SELECT tblhvdeals.txtablhybrid
FROM tblhvdeals
WHERE (((tblhvdeals.txtablhybrid) In ("PureABL","HYBRID")))
GROUP BY tblhvdeals.txtablhybrid;

The result doesn't show any records when the total of PureABL should be 28
and Hybrid should be 0

Having searched the help I'm thinking of trying DCount, do you think that
would give me what I want?

Thanks
Tony

Thanks guys. I've tried all your suggestions and whilst I can get the
query to count all the records I want it to count how many records

[quoted text clipped - 14 lines]
Switch your query to SQL View so you can show us what you tried. Here is
what the sql should look like:

select yourtextfield, count(*)
from yourtable
where yourtextfield in ("PureABL","HYBRID")
group by yourtextfield

 

[Bob Barrows]

No. You're missing the count(*) part of it. You can type it into the SQL
View. Like this:

SELECT tblhvdeals.txtablhybrid, Count(*) as totals
FROM tblhvdeals
WHERE (((tblhvdeals.txtablhybrid) In ("PureABL","HYBRID")))
GROUP BY tblhvdeals.txtablhybrid;


If it still fails to give you what you want, then we need to see some sample
data

Hi Bob here's my sql along the lines that you suggested
SELECT tblhvdeals.txtablhybrid
FROM tblhvdeals
WHERE (((tblhvdeals.txtablhybrid) In ("PureABL","HYBRID")))
GROUP BY tblhvdeals.txtablhybrid;

The result doesn't show any records when the total of PureABL should
be 28 and Hybrid should be 0

Having searched the help I'm thinking of trying DCount, do you think
that would give me what I want?

Thanks
Tony

Thanks guys. I've tried all your suggestions and whilst I can get
the query to count all the records I want it to count how many
records

[quoted text clipped - 14 lines]

Thanks
Tony

Switch your query to SQL View so you can show us what you tried.
Here is what the sql should look like:

select yourtextfield, count(*)
from yourtable
where yourtextfield in ("PureABL","HYBRID")
group by yourtextfield

 

[TonyWilliams via AccessMonster.com]

Hi Bob that gives me a total of 28 for the Pure ABL records but I don't get a
0 for the Hybrid deals?
Any thoughts?
Thanks
Tony

No. You're missing the count(*) part of it. You can type it into the SQL
View. Like this:

SELECT tblhvdeals.txtablhybrid, Count(*) as totals
FROM tblhvdeals
WHERE (((tblhvdeals.txtablhybrid) In ("PureABL","HYBRID")))
GROUP BY tblhvdeals.txtablhybrid;

If it still fails to give you what you want, then we need to see some sample
data

Hi Bob here's my sql along the lines that you suggested
SELECT tblhvdeals.txtablhybrid

[quoted text clipped - 24 lines]

 

[Bob Barrows]

All I can say is you don't have any "Hybrid" deals. Given this set of
records in a table called tblhvdeals:

ID| txtablhybrid
1 | PureABL
2 | PureABL
3 | PureABL
4 | Hybrid
5 | Hybrid

Running the suggested query should give this result:

PureABL | 3
Hybrid | 2

If it doesn't, all I can say is that your data doesn't really look like
that.

Hi Bob that gives me a total of 28 for the Pure ABL records but I
don't get a 0 for the Hybrid deals?
Any thoughts?
Thanks
Tony

No. You're missing the count(*) part of it. You can type it into the
SQL View. Like this:

SELECT tblhvdeals.txtablhybrid, Count(*) as totals
FROM tblhvdeals
WHERE (((tblhvdeals.txtablhybrid) In ("PureABL","HYBRID")))
GROUP BY tblhvdeals.txtablhybrid;

If it still fails to give you what you want, then we need to see
some sample data

Hi Bob here's my sql along the lines that you suggested
SELECT tblhvdeals.txtablhybrid

[quoted text clipped - 24 lines]

where yourtextfield in ("PureABL","HYBRID")
group by yourtextfield

 

[TonyWilliams via AccessMonster.com]

Hi Bob yes that's exactly how it is. You are right there aren't any Hybrid
deals but I wanted the answer to be "0" I suspect I'm going to have to try a
Dcount combined with an IIf statment. The query is eventually going to be the
record source for a report so I could use either/both of those statemnents as
a calculated control couldn't I?
Thanks again
Tony

All I can say is you don't have any "Hybrid" deals. Given this set of
records in a table called tblhvdeals:

ID| txtablhybrid
1 | PureABL
2 | PureABL
3 | PureABL
4 | Hybrid
5 | Hybrid

Running the suggested query should give this result:

PureABL | 3
Hybrid | 2

If it doesn't, all I can say is that your data doesn't really look like
that.

Hi Bob that gives me a total of 28 for the Pure ABL records but I
don't get a 0 for the Hybrid deals?

[quoted text clipped - 18 lines]

 

[John W. Vinson]

Hi Bob that gives me a total of 28 for the Pure ABL records but I don't get a
0 for the Hybrid deals?

Access won't show what's not there. I'm sure you have 0 records for "Subspace
drive" and "Xzqqyez" too, and you wouldn't expect it to show them!

If you have values that you want to show even if there is no record with that
value, you will need another table, let's call it DealTypes; this should have
one record for PureABL, one record for Hybrid, and one record for each other
legitimate value of the deal type. You can Join this table to your current
table, using a Left Outer Join (select the join line in design view and choose
the option "Show all records in DealTypes and matching records in
tblhvdeals").

 

[Bob Barrows]

Oh, I understand your requirement now. No, DCount is not necessary.

Do you have a table that contains a unique list of all the possible values
for this txtablhybrid field? If so, use it as the left side of an outer
join. Let's say the table is called PossibleValues with a field called
txtablhybrid and it contains one record each for PureABL and Hybrid. If you
don't have one, create one. Once you have that table, this query will give
you what you want:

SELECT PossibleValues.txtablhybrid, Count(tblhvdeals.txtablhybrid) as totals
FROM PossibleValues LEFT JOIN tblhvdeals ON PossibleValues.txtablhybrid =
tblhvdeals.txtablhybrid
WHERE (((PossibleValues .txtablhybrid) In ("PureABL","HYBRID")))
GROUP BY PossibleValues .txtablhybrid;

This needs to be exactly as written. Note which table's txtablhybrid field
is being counted, and which one is being grouped by.

This could also be done with DCount or a correlated subquery, but I tend to
avoid those if possible for performance reasons.

Hi Bob yes that's exactly how it is. You are right there aren't any
Hybrid deals but I wanted the answer to be "0" I suspect I'm going to
have to try a Dcount combined with an IIf statment. The query is
eventually going to be the record source for a report so I could use
either/both of those statemnents as a calculated control couldn't I?
Thanks again
Tony

All I can say is you don't have any "Hybrid" deals. Given this set of
records in a table called tblhvdeals:

ID| txtablhybrid
1 | PureABL
2 | PureABL
3 | PureABL
4 | Hybrid
5 | Hybrid

Running the suggested query should give this result:

PureABL | 3
Hybrid | 2

If it doesn't, all I can say is that your data doesn't really look
like that.

Hi Bob that gives me a total of 28 for the Pure ABL records but I
don't get a 0 for the Hybrid deals?

[quoted text clipped - 18 lines]

where yourtextfield in ("PureABL","HYBRID")
group by yourtextfield

 

[TonyWilliams via AccessMonster.com]

Thanks John, I think I understand that I'll play around with the idea and see
what I can do.
Thanks again
Tony

 

[TonyWilliams via AccessMonster.com]

Thanks Bob, I didn't have a seperate table as the record source for the field
was a combobox with defined values. But i did as you suggested and it works
fine, Thanks a lot and a very Happy New Year to you. I can have the weekend
off now!
Tony

Oh, I understand your requirement now. No, DCount is not necessary.

Do you have a table that contains a unique list of all the possible values
for this txtablhybrid field? If so, use it as the left side of an outer
join. Let's say the table is called PossibleValues with a field called
txtablhybrid and it contains one record each for PureABL and Hybrid. If you
don't have one, create one. Once you have that table, this query will give
you what you want:

SELECT PossibleValues.txtablhybrid, Count(tblhvdeals.txtablhybrid) as totals
FROM PossibleValues LEFT JOIN tblhvdeals ON PossibleValues.txtablhybrid =
tblhvdeals.txtablhybrid
WHERE (((PossibleValues .txtablhybrid) In ("PureABL","HYBRID")))
GROUP BY PossibleValues .txtablhybrid;

This needs to be exactly as written. Note which table's txtablhybrid field
is being counted, and which one is being grouped by.

This could also be done with DCount or a correlated subquery, but I tend to
avoid those if possible for performance reasons.

Hi Bob yes that's exactly how it is. You are right there aren't any
Hybrid deals but I wanted the answer to be "0" I suspect I'm going to

[quoted text clipped - 27 lines]