Counting data?

[jmuirman]

I searched for the answer but not sure if this is a count IIF or Count or
DCount

My field is [Service] and contains a drop-down list with Meeting1, Meeting2
and Meeting3. How do I count each and get totals for each - like 5 Meeting1
and 8 Meeting2, etc?

Thanks,

John

 

[Duane Hookom]

Create a query with this SQL:

SELECT Service, Count(*) As NumOf
FROM tblWithNoName
GROUP BY Service;

 

[John Vinson]

I searched for the answer but not sure if this is a count IIF or Count or
DCount

My field is [Service] and contains a drop-down list with Meeting1, Meeting2
and Meeting3. How do I count each and get totals for each - like 5 Meeting1
and 8 Meeting2, etc?

Thanks,

John

Create a Query based on your table. Make it a Totals query by clicking
the Greek Sigma icon (looks like a sideways M).

Select the Service field twice. On the Totals row under one instance
leave the default Group By; change it to Count under the other.

John W. Vinson[MVP]

 

[jmuirman]

Sorry - I don't understand what to do with this - do I need to create an
additional query to the one I have? Not sure where I find SELECT or FROM?? dO
i USE THE WIZARD?

Thanks,

John

Create a query with this SQL:

SELECT Service, Count(*) As NumOf
FROM tblWithNoName
GROUP BY Service;

--
Duane Hookom
MS Access MVP

I searched for the answer but not sure if this is a count IIF or Count or
DCount

My field is [Service] and contains a drop-down list with Meeting1,
Meeting2
and Meeting3. How do I count each and get totals for each - like 5
Meeting1
and 8 Meeting2, etc?

Thanks,

John

 

[Duane Hookom]

John provided the steps to do this. If the result isn't what you want then
you need to be more specific with what you have and what you want.

--
Duane Hookom
MS Access MVP

Sorry - I don't understand what to do with this - do I need to create an
additional query to the one I have? Not sure where I find SELECT or FROM??
dO
i USE THE WIZARD?

Thanks,

John

Create a query with this SQL:

SELECT Service, Count(*) As NumOf
FROM tblWithNoName
GROUP BY Service;

--
Duane Hookom
MS Access MVP

I searched for the answer but not sure if this is a count IIF or Count
or
DCount

My field is [Service] and contains a drop-down list with Meeting1,
Meeting2
and Meeting3. How do I count each and get totals for each - like 5
Meeting1
and 8 Meeting2, etc?

Thanks,

John

 

[jmuirman]

The query returns a value of 1 for each record - but I need to know the count
for each item. For example, let's say the field is [FruitPreference] and I
enter 5 records that choose apple and 10 records that chooses orange - I
know how to count total records, in this case 15, but -- how do I find 5
apples and 10 oranges?

Thanks again,

John

John provided the steps to do this. If the result isn't what you want then
you need to be more specific with what you have and what you want.

--
Duane Hookom
MS Access MVP

Sorry - I don't understand what to do with this - do I need to create an
additional query to the one I have? Not sure where I find SELECT or FROM??
dO
i USE THE WIZARD?

Thanks,

John

Create a query with this SQL:

SELECT Service, Count(*) As NumOf
FROM tblWithNoName
GROUP BY Service;

--
Duane Hookom
MS Access MVP


I searched for the answer but not sure if this is a count IIF or Count
or
DCount

My field is [Service] and contains a drop-down list with Meeting1,
Meeting2
and Meeting3. How do I count each and get totals for each - like 5
Meeting1
and 8 Meeting2, etc?

Thanks,

John

 

[Duane Hookom]

Make sure your query doesn't have the option selected to output all fields.

Please reply with the SQL view of your query that "returns a value of 1 for
each record".

--
Duane Hookom
MS Access MVP

The query returns a value of 1 for each record - but I need to know the
count
for each item. For example, let's say the field is [FruitPreference] and I
enter 5 records that choose apple and 10 records that chooses orange - I
know how to count total records, in this case 15, but -- how do I find 5
apples and 10 oranges?

Thanks again,

John

John provided the steps to do this. If the result isn't what you want
then
you need to be more specific with what you have and what you want.

--
Duane Hookom
MS Access MVP

Sorry - I don't understand what to do with this - do I need to create
an
additional query to the one I have? Not sure where I find SELECT or
FROM??
dO
i USE THE WIZARD?

Thanks,

John

:

Create a query with this SQL:

SELECT Service, Count(*) As NumOf
FROM tblWithNoName
GROUP BY Service;

--
Duane Hookom
MS Access MVP


I searched for the answer but not sure if this is a count IIF or
Count
or
DCount

My field is [Service] and contains a drop-down list with Meeting1,
Meeting2
and Meeting3. How do I count each and get totals for each - like 5
Meeting1
and 8 Meeting2, etc?

Thanks,

John

 

[jmuirman]

Thanks Duane - now I know what you mean by sql view - what I'm interested
is.[Meeting/Event Product1]:

SELECT WCCAMembers.[Member ID], WCCAMembers.FirstName, WCCAMembers.LastName,
WCCAMembers.Title, WCCAMembers.[Certificate of Attendance],
WCCAMembers.OrganizationName, WCCAMembers.Address1, WCCAMembers.Address2,
WCCAMembers.City, WCCAMembers.State, WCCAMembers.PostalCode,
WCCAMembers.WorkPhone, WCCAMembers.MobilePhone, WCCAMembers.FaxNumber,
WCCAMembers.EmailAddress, WCCAMembers.MembershipStatus,
WCCAMembers.DuesAmount, WCCAMembers.ChapterLocation, WCCAMembers.[Annual Dues
Paid?], WCCAMembers.[Meeting or Event Code], WCCAMembers.[Meeting or Event
Code2], WCCAMembers.[Non-Member Fee Paid?], WCCAMembers.[Amount Received],
WCCAMembers.[Professional Designation], WCCAMembers.Takeofflist,
WCCAMembers.[Willing to Serve on Committee?], WCCAMembers.[How became aware
of WCCA?], WCCAMembers.[Professional Designation], WCCAMembers.[Meeting
Attendee Alternate Name], WCCAMembers.[Food Preference], WCCAMembers.[CEUs?],
WCCAMembers.[FantaSEA Member Fee Paid $40], WCCAMembers.[FantaSEA Non-member
Fee Paid $60], WCCAMembers.Service, WCCAMembers.[Service Paid?],
WCCAMembers.[Member Meeting Fee Paid?], WCCAMembers.[Meeting/Event Product1],
WCCAMembers.[Meeting/Event Product2], WCCAMembers.[Meeting/Event Product3],
WCCAMembers.[Meeting/Event Product4], WCCAMembers.[Paid?1],
WCCAMembers.[Paid?2], WCCAMembers.[Paid?3], WCCAMembers.[Paid?4],
WCCAMembers.[Special Request]
FROM WCCAMembers
WHERE (((WCCAMembers.[Meeting or Event Code])=[Which Meeting Code?]));

Make sure your query doesn't have the option selected to output all fields.

Please reply with the SQL view of your query that "returns a value of 1 for
each record".

--
Duane Hookom
MS Access MVP

The query returns a value of 1 for each record - but I need to know the
count
for each item. For example, let's say the field is [FruitPreference] and I
enter 5 records that choose apple and 10 records that chooses orange - I
know how to count total records, in this case 15, but -- how do I find 5
apples and 10 oranges?

Thanks again,

John

John provided the steps to do this. If the result isn't what you want
then
you need to be more specific with what you have and what you want.

--
Duane Hookom
MS Access MVP


Sorry - I don't understand what to do with this - do I need to create
an
additional query to the one I have? Not sure where I find SELECT or
FROM??
dO
i USE THE WIZARD?

Thanks,

John

:

Create a query with this SQL:

SELECT Service, Count(*) As NumOf
FROM tblWithNoName
GROUP BY Service;

--
Duane Hookom
MS Access MVP


I searched for the answer but not sure if this is a count IIF or
Count
or
DCount

My field is [Service] and contains a drop-down list with Meeting1,
Meeting2
and Meeting3. How do I count each and get totals for each - like 5
Meeting1
and 8 Meeting2, etc?

Thanks,

John

 

[Duane Hookom]

I would not spend any time on this until it was normalized. Your products
and paids fields should be in a related table where you can query them with
very little effort.

--
Duane Hookom
MS Access MVP

Thanks Duane - now I know what you mean by sql view - what I'm interested
is.[Meeting/Event Product1]:

SELECT WCCAMembers.[Member ID], WCCAMembers.FirstName,
WCCAMembers.LastName,
WCCAMembers.Title, WCCAMembers.[Certificate of Attendance],
WCCAMembers.OrganizationName, WCCAMembers.Address1, WCCAMembers.Address2,
WCCAMembers.City, WCCAMembers.State, WCCAMembers.PostalCode,
WCCAMembers.WorkPhone, WCCAMembers.MobilePhone, WCCAMembers.FaxNumber,
WCCAMembers.EmailAddress, WCCAMembers.MembershipStatus,
WCCAMembers.DuesAmount, WCCAMembers.ChapterLocation, WCCAMembers.[Annual
Dues
Paid?], WCCAMembers.[Meeting or Event Code], WCCAMembers.[Meeting or Event
Code2], WCCAMembers.[Non-Member Fee Paid?], WCCAMembers.[Amount Received],
WCCAMembers.[Professional Designation], WCCAMembers.Takeofflist,
WCCAMembers.[Willing to Serve on Committee?], WCCAMembers.[How became
aware
of WCCA?], WCCAMembers.[Professional Designation], WCCAMembers.[Meeting
Attendee Alternate Name], WCCAMembers.[Food Preference],
WCCAMembers.[CEUs?],
WCCAMembers.[FantaSEA Member Fee Paid $40], WCCAMembers.[FantaSEA
Non-member
Fee Paid $60], WCCAMembers.Service, WCCAMembers.[Service Paid?],
WCCAMembers.[Member Meeting Fee Paid?], WCCAMembers.[Meeting/Event
Product1],
WCCAMembers.[Meeting/Event Product2], WCCAMembers.[Meeting/Event
Product3],
WCCAMembers.[Meeting/Event Product4], WCCAMembers.[Paid?1],
WCCAMembers.[Paid?2], WCCAMembers.[Paid?3], WCCAMembers.[Paid?4],
WCCAMembers.[Special Request]
FROM WCCAMembers
WHERE (((WCCAMembers.[Meeting or Event Code])=[Which Meeting Code?]));

Make sure your query doesn't have the option selected to output all
fields.

Please reply with the SQL view of your query that "returns a value of 1
for
each record".

--
Duane Hookom
MS Access MVP

The query returns a value of 1 for each record - but I need to know the
count
for each item. For example, let's say the field is [FruitPreference]
and I
enter 5 records that choose apple and 10 records that chooses orange -
I
know how to count total records, in this case 15, but -- how do I find
5
apples and 10 oranges?

Thanks again,

John

:

John provided the steps to do this. If the result isn't what you want
then
you need to be more specific with what you have and what you want.

--
Duane Hookom
MS Access MVP


Sorry - I don't understand what to do with this - do I need to
create
an
additional query to the one I have? Not sure where I find SELECT or
FROM??
dO
i USE THE WIZARD?

Thanks,

John

:

Create a query with this SQL:

SELECT Service, Count(*) As NumOf
FROM tblWithNoName
GROUP BY Service;

--
Duane Hookom
MS Access MVP


I searched for the answer but not sure if this is a count IIF or
Count
or
DCount

My field is [Service] and contains a drop-down list with
Meeting1,
Meeting2
and Meeting3. How do I count each and get totals for each - like
5
Meeting1
and 8 Meeting2, etc?

Thanks,

John

 

[jmuirman]

All of my data bases have been based on generally one table because I'm not
sure where to start to design a good db and two of my databases are extremely
important and haven't wanted to tinker too much - can you look at this db and
start me in the right direction?

Thanks,

John

I would not spend any time on this until it was normalized. Your products
and paids fields should be in a related table where you can query them with
very little effort.

--
Duane Hookom
MS Access MVP

Thanks Duane - now I know what you mean by sql view - what I'm interested
is.[Meeting/Event Product1]:

SELECT WCCAMembers.[Member ID], WCCAMembers.FirstName,
WCCAMembers.LastName,
WCCAMembers.Title, WCCAMembers.[Certificate of Attendance],
WCCAMembers.OrganizationName, WCCAMembers.Address1, WCCAMembers.Address2,
WCCAMembers.City, WCCAMembers.State, WCCAMembers.PostalCode,
WCCAMembers.WorkPhone, WCCAMembers.MobilePhone, WCCAMembers.FaxNumber,
WCCAMembers.EmailAddress, WCCAMembers.MembershipStatus,
WCCAMembers.DuesAmount, WCCAMembers.ChapterLocation, WCCAMembers.[Annual
Dues
Paid?], WCCAMembers.[Meeting or Event Code], WCCAMembers.[Meeting or Event
Code2], WCCAMembers.[Non-Member Fee Paid?], WCCAMembers.[Amount Received],
WCCAMembers.[Professional Designation], WCCAMembers.Takeofflist,
WCCAMembers.[Willing to Serve on Committee?], WCCAMembers.[How became
aware
of WCCA?], WCCAMembers.[Professional Designation], WCCAMembers.[Meeting
Attendee Alternate Name], WCCAMembers.[Food Preference],
WCCAMembers.[CEUs?],
WCCAMembers.[FantaSEA Member Fee Paid $40], WCCAMembers.[FantaSEA
Non-member
Fee Paid $60], WCCAMembers.Service, WCCAMembers.[Service Paid?],
WCCAMembers.[Member Meeting Fee Paid?], WCCAMembers.[Meeting/Event
Product1],
WCCAMembers.[Meeting/Event Product2], WCCAMembers.[Meeting/Event
Product3],
WCCAMembers.[Meeting/Event Product4], WCCAMembers.[Paid?1],
WCCAMembers.[Paid?2], WCCAMembers.[Paid?3], WCCAMembers.[Paid?4],
WCCAMembers.[Special Request]
FROM WCCAMembers
WHERE (((WCCAMembers.[Meeting or Event Code])=[Which Meeting Code?]));

Make sure your query doesn't have the option selected to output all
fields.

Please reply with the SQL view of your query that "returns a value of 1
for
each record".

--
Duane Hookom
MS Access MVP


The query returns a value of 1 for each record - but I need to know the
count
for each item. For example, let's say the field is [FruitPreference]
and I
enter 5 records that choose apple and 10 records that chooses orange -
I
know how to count total records, in this case 15, but -- how do I find
5
apples and 10 oranges?

Thanks again,

John

:

John provided the steps to do this. If the result isn't what you want
then
you need to be more specific with what you have and what you want.

--
Duane Hookom
MS Access MVP


Sorry - I don't understand what to do with this - do I need to
create
an
additional query to the one I have? Not sure where I find SELECT or
FROM??
dO
i USE THE WIZARD?

Thanks,

John

:

Create a query with this SQL:

SELECT Service, Count(*) As NumOf
FROM tblWithNoName
GROUP BY Service;

--
Duane Hookom
MS Access MVP


I searched for the answer but not sure if this is a count IIF or
Count
or
DCount

My field is [Service] and contains a drop-down list with
Meeting1,
Meeting2
and Meeting3. How do I count each and get totals for each - like
5
Meeting1
and 8 Meeting2, etc?

Thanks,

John

 

[Duane Hookom]

Consider reading up on how to structure tables. Jeff Conrad has great links
from his site at
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html#DatabaseDesign101

--
Duane Hookom
MS Access MVP

All of my data bases have been based on generally one table because I'm
not
sure where to start to design a good db and two of my databases are
extremely
important and haven't wanted to tinker too much - can you look at this db
and
start me in the right direction?

Thanks,

John

I would not spend any time on this until it was normalized. Your products
and paids fields should be in a related table where you can query them
with
very little effort.

--
Duane Hookom
MS Access MVP

Thanks Duane - now I know what you mean by sql view - what I'm
interested
is.[Meeting/Event Product1]:

SELECT WCCAMembers.[Member ID], WCCAMembers.FirstName,
WCCAMembers.LastName,
WCCAMembers.Title, WCCAMembers.[Certificate of Attendance],
WCCAMembers.OrganizationName, WCCAMembers.Address1,
WCCAMembers.Address2,
WCCAMembers.City, WCCAMembers.State, WCCAMembers.PostalCode,
WCCAMembers.WorkPhone, WCCAMembers.MobilePhone, WCCAMembers.FaxNumber,
WCCAMembers.EmailAddress, WCCAMembers.MembershipStatus,
WCCAMembers.DuesAmount, WCCAMembers.ChapterLocation,
WCCAMembers.[Annual
Dues
Paid?], WCCAMembers.[Meeting or Event Code], WCCAMembers.[Meeting or
Event
Code2], WCCAMembers.[Non-Member Fee Paid?], WCCAMembers.[Amount
Received],
WCCAMembers.[Professional Designation], WCCAMembers.Takeofflist,
WCCAMembers.[Willing to Serve on Committee?], WCCAMembers.[How became
aware
of WCCA?], WCCAMembers.[Professional Designation], WCCAMembers.[Meeting
Attendee Alternate Name], WCCAMembers.[Food Preference],
WCCAMembers.[CEUs?],
WCCAMembers.[FantaSEA Member Fee Paid $40], WCCAMembers.[FantaSEA
Non-member
Fee Paid $60], WCCAMembers.Service, WCCAMembers.[Service Paid?],
WCCAMembers.[Member Meeting Fee Paid?], WCCAMembers.[Meeting/Event
Product1],
WCCAMembers.[Meeting/Event Product2], WCCAMembers.[Meeting/Event
Product3],
WCCAMembers.[Meeting/Event Product4], WCCAMembers.[Paid?1],
WCCAMembers.[Paid?2], WCCAMembers.[Paid?3], WCCAMembers.[Paid?4],
WCCAMembers.[Special Request]
FROM WCCAMembers
WHERE (((WCCAMembers.[Meeting or Event Code])=[Which Meeting Code?]));

:

Make sure your query doesn't have the option selected to output all
fields.

Please reply with the SQL view of your query that "returns a value of
1
for
each record".

--
Duane Hookom
MS Access MVP


The query returns a value of 1 for each record - but I need to know
the
count
for each item. For example, let's say the field is [FruitPreference]
and I
enter 5 records that choose apple and 10 records that chooses
range -
I
know how to count total records, in this case 15, but -- how do I
find
5
apples and 10 oranges?

Thanks again,

John

:

John provided the steps to do this. If the result isn't what you
want
then
you need to be more specific with what you have and what you want.

--
Duane Hookom
MS Access MVP


Sorry - I don't understand what to do with this - do I need to
create
an
additional query to the one I have? Not sure where I find SELECT
or
FROM??
dO
i USE THE WIZARD?

Thanks,

John

:

Create a query with this SQL:

SELECT Service, Count(*) As NumOf
FROM tblWithNoName
GROUP BY Service;

--
Duane Hookom
MS Access MVP


I searched for the answer but not sure if this is a count IIF
or
Count
or
DCount

My field is [Service] and contains a drop-down list with
Meeting1,
Meeting2
and Meeting3. How do I count each and get totals for each -
like
5
Meeting1
and 8 Meeting2, etc?

Thanks,

John