How to get counts & totals from a drop-down list

[dohernan]

I'm using Access 2003. I have a table/form with a column, Form Type. It is
a drop-down menu with 9 choices, like Insurance, Housing, Mortgage, etc.

In a weekly report I want to be able to see how many Housing Forms we got,
How many Mortgage forms, etc. all on one page.

I can't seem to figure out how to get it to tell me there were for example,
9 Mortgage Forms, 2 Housing forms, and an Insurance form completed between
the dates of 9/14/09 and 9/18/09, for a total of 12 completed forms.

Thanks for your time.

 

[Klatuu]

Use a DCount function in a text box to pull a count of the records you want:

=Dcount("*", "Table Name", "[Form Type] = """ & "Insurance""")

You need one for each of the 9 types and if your report is filtered, you
will need to add the same filtering to the DCount. It may even be better to
create a query that filters the same as the report to make it easier.

 

[dohernan]

Thank you.

I have a query that takes only 3 columns from the main Table, Form Type,
Received, and Completed. Received & Completed are simply dates. I update
the Query dates weekly, the Criteria is set on the Completed column.
I have some forms that aren't completed yet so the Completed date column is
blank and the query seems to ignore them, which suits me.

I do the report based off of this query.

There are weeks when we don't get certain form types, is there some way to
only print out the Names and sums of the forms that were actually done,
instead of zeros next to the form names? Thanks.

Use a DCount function in a text box to pull a count of the records you want:

=Dcount("*", "Table Name", "[Form Type] = """ & "Insurance""")

You need one for each of the 9 types and if your report is filtered, you
will need to add the same filtering to the DCount. It may even be better to
create a query that filters the same as the report to make it easier.
--
Dave Hargis, Microsoft Access MVP

I'm using Access 2003. I have a table/form with a column, Form Type. It is
a drop-down menu with 9 choices, like Insurance, Housing, Mortgage, etc.

In a weekly report I want to be able to see how many Housing Forms we got,
How many Mortgage forms, etc. all on one page.

I can't seem to figure out how to get it to tell me there were for example,
9 Mortgage Forms, 2 Housing forms, and an Insurance form completed between
the dates of 9/14/09 and 9/18/09, for a total of 12 completed forms.

Thanks for your time.

 

[KARL DEWEY]

Try this --
SELECT [Form Type], Count([Form Type]) AS QTY
FROM YourTable
WHERE [Completed] Between CVDate([Enter week start date]) AND CVDate([Enter
week start date]) +6
GROUP BY [Form Type];

--
Build a little, test a little.

Thank you.

I have a query that takes only 3 columns from the main Table, Form Type,
Received, and Completed. Received & Completed are simply dates. I update
the Query dates weekly, the Criteria is set on the Completed column.
I have some forms that aren't completed yet so the Completed date column is
blank and the query seems to ignore them, which suits me.

I do the report based off of this query.

There are weeks when we don't get certain form types, is there some way to
only print out the Names and sums of the forms that were actually done,
instead of zeros next to the form names? Thanks.

Use a DCount function in a text box to pull a count of the records you want:

=Dcount("*", "Table Name", "[Form Type] = """ & "Insurance""")

You need one for each of the 9 types and if your report is filtered, you
will need to add the same filtering to the DCount. It may even be better to
create a query that filters the same as the report to make it easier.
--
Dave Hargis, Microsoft Access MVP

I'm using Access 2003. I have a table/form with a column, Form Type. It is
a drop-down menu with 9 choices, like Insurance, Housing, Mortgage, etc.

In a weekly report I want to be able to see how many Housing Forms we got,
How many Mortgage forms, etc. all on one page.

I can't seem to figure out how to get it to tell me there were for example,
9 Mortgage Forms, 2 Housing forms, and an Insurance form completed between
the dates of 9/14/09 and 9/18/09, for a total of 12 completed forms.

Thanks for your time.

 

[dohernan]

Thank you.

I tried this as a Query.

++++++++++++

SELECT [Form Type], Count([Form Type]) AS QTY
FROM Verif1Form
WHERE [Completed] Between CVDate([9/10/2009]) And CVDate([9/10/2009])+6
GROUP BY [Form Type];

++++++++++++

It asks for a Parameter Value for 9/10/2009, I tried doing 1 and 0 and it
opens up 2 columns, Form Type and QTY that are empty.

 

[John Spencer]

If you are going to use date literals and not a parameter then

SELECT [Form Type], Count([Form Type]) AS QTY
FROM Verif1Form
WHERE [Completed] Between CVDate(#9/10/2009#) And CVDate(#9/10/2009# + 6)
GROUP BY [Form Type];

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Thank you.

I tried this as a Query.

++++++++++++

SELECT [Form Type], Count([Form Type]) AS QTY
FROM Verif1Form
WHERE [Completed] Between CVDate([9/10/2009]) And CVDate([9/10/2009])+6
GROUP BY [Form Type];

++++++++++++

It asks for a Parameter Value for 9/10/2009, I tried doing 1 and 0 and it
opens up 2 columns, Form Type and QTY that are empty.

Try this --
SELECT [Form Type], Count([Form Type]) AS QTY
FROM YourTable
WHERE [Completed] Between CVDate([Enter week start date]) AND CVDate([Enter
week start date]) +6
GROUP BY [Form Type];

 

[dohernan]

Thank you, that Query works perfectly!

Now I have 2 ways to see the information, this Query and the earlier report
that used D count.

I redid the report using the new Query and added
=Sum([QTY])

To the Report footer and got my total completed for the week number as well.

Thanks again!!

If you are going to use date literals and not a parameter then

SELECT [Form Type], Count([Form Type]) AS QTY
FROM Verif1Form
WHERE [Completed] Between CVDate(#9/10/2009#) And CVDate(#9/10/2009# + 6)
GROUP BY [Form Type];
John Spencer

Use a DCount function in a text box to pull a count of the records you want:
=Dcount("*", "Table Name", "[Form Type] = """ & "Insurance""")

 

[dohernan]

Hello again.

I decided to get ambitious and tried adding a pie chart to the report based
on the Query, each slice a Form Type, size based on QTY. It keeps showing
me just 1 form type. I think I'm missing something.

Thanks again.

I redid the report using the new Query and added
=Sum([QTY])

To the Report footer and got my total completed for the week number as well.

Thanks again!!

If you are going to use date literals and not a parameter then

SELECT [Form Type], Count([Form Type]) AS QTY
FROM Verif1Form
WHERE [Completed] Between CVDate(#9/10/2009#) And CVDate(#9/10/2009# + 6)
GROUP BY [Form Type];
John Spencer

Use a DCount function in a text box to pull a count of the records you want:
=Dcount("*", "Table Name", "[Form Type] = """ & "Insurance""")

 

[Duane Hookom]

You pie chart control might have something in the Link Master/Child
properties that is filtering the results. Try remove the values to see if
your results match your needs.

--
Duane Hookom
Microsoft Access MVP

Hello again.

I decided to get ambitious and tried adding a pie chart to the report based
on the Query, each slice a Form Type, size based on QTY. It keeps showing
me just 1 form type. I think I'm missing something.

Thanks again.

I redid the report using the new Query and added
=Sum([QTY])

To the Report footer and got my total completed for the week number as well.

Thanks again!!

If you are going to use date literals and not a parameter then

SELECT [Form Type], Count([Form Type]) AS QTY
FROM Verif1Form
WHERE [Completed] Between CVDate(#9/10/2009#) And CVDate(#9/10/2009# + 6)
GROUP BY [Form Type];
John Spencer

:
Use a DCount function in a text box to pull a count of the records you want:
=Dcount("*", "Table Name", "[Form Type] = """ & "Insurance""")

 

[dohernan]

I got a working chart with percentages.

Thanks again.