count by date

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

Guest

Hello and thanks in advance to those who can help. I would like to count
records by month and display it on a form (like a running sum). Can this be
done?
 
Lin said:
Hello and thanks in advance to those who can help. I would like to count
records by month and display it on a form (like a running sum).
Click to expand...


You don't say much about what your form is doing so this is
going to be kind of vague.

Create a Totals type of query to calculate the counts:

SELECT Month(datefield) As MonthNum, Count(*) As MonthCount
FROM sometable

Then you can display the counts in a subform based on the
query.
 
Hi Marshall and thanks. I should explain further. I have a form based on a
table for children admitted. I need to count each child on a monthly bases.
What I did was add a field to the table with default value as 1. I was
hoping this would help to calculate each child as they are entered.
 
Sorry, but that doesn't help much. Can you explain more
about what/how you are counting?

You say you want to count by month, which implies some kind
of date in the records, or does it. An explanation of the
pertinate fields in the table would help a lot.

That extra field will not help. If you're hoping a table
will calculate this total without using a query, stop
looking in that direction. Tables can not do calculations,
but queries can.
 
Hi Marshall, thanks, sorry for generalizing.

We admit kids daily. We issue what is called a clearing number. How this
works is i.e.(8-125) The 8 would stand for August the number or count would
be the 125 child admitted for august. I guess basically I need to count all
records for the month and have it returned on a form. If the user was
opening the form to admit a new name (child), that number would already be
there. I have the form all set up for this. Its just that Im not sure how
to reset to (0) for each new month. I hope this is little more helpful,
again thank you.

Marshall Barton said:
Sorry, but that doesn't help much. Can you explain more
about what/how you are counting?

You say you want to count by month, which implies some kind
of date in the records, or does it. An explanation of the
pertinate fields in the table would help a lot.

That extra field will not help. If you're hoping a table
will calculate this total without using a query, stop
looking in that direction. Tables can not do calculations,
but queries can.
--
Marsh
MVP [MS Access]

Hi Marshall and thanks. I should explain further. I have a form based on a
table for children admitted. I need to count each child on a monthly bases.
What I did was add a field to the table with default value as 1. I was
hoping this would help to calculate each child as they are entered.
Click to expand...
Click to expand...
 
Lin said:
We admit kids daily. We issue what is called a clearing number. How this
works is i.e.(8-125) The 8 would stand for August the number or count would
be the 125 child admitted for august. I guess basically I need to count all
records for the month and have it returned on a form. If the user was
opening the form to admit a new name (child), that number would already be
there. I have the form all set up for this. Its just that Im not sure how
to reset to (0) for each new month. I hope this is little more helpful,
again thank you.
Click to expand...

First, make sure that you do not store that "clearing
number" in your table, it would be rather messy to do simple
things like sort if it's all jammed into one field (and it
would violate a rule of database normalization).

Instead you should have the Admitnum (e.g. 125) in its own
field and an AdmitDate in another field. These two values
can easily be formatted whenever you need to display the
clearing number to a user:
=Format(AdmitDate, "m") & "-" & Format(AdmitNum, "0")

Getting the next AdmitNum sounds like the usual do it
yourself autonumber kind of thing. This way will get the
next number
intAdmit = Nz(DMax("AdmitNum", "table", _
"Format(ClearingDate, ""yyyymm"") = """ _
& Format(Date(), "yyyymm") & """"), 0) + 1
 
Hi Marshall, thanks again for reply, I just have one more question. You
provided 2 examples, Now I deffinately need this count visible and ongoing
on a form. Which of the ex. do I use and where do I key it?????

Please and thanks sincerely.
 
Both!

The first one could be a text box expression to display the
combined clearing number.

The code sequence would usually go in the Form's
BeforeInsert event to calculate the number for a new record.
The name on the left of the = sign would then be assigned to
the (probably invisible) AdmitNum text box.

Be sure to change all the names I made up with the ones you
actually have in your program.
 
Back
Top