I have a form which shows the monthly records for a user

Is it possible show, on a form, the total records. for Example:

Mary has 100 records for March
John has 100 records for March
Tim has 100 records for March Total records for March are 300

Sally has 200 records for April
Mary has 50 records for April
John has 200 records for April
Tim has 75 records for April Total records for April are 525

and so on.

It's hard to tell what you need from your message.

Are you saying you have a form that shows the monthly records for a single individual and you also want to do a record count for each of a group of individuals?

You'd do that via SQL statements. I'm sure someone here could tell you how to loop through a single statement so you'd find information on each person, but you might want to ask that in the query area.

I have a form which shows me the number of records for each user. However, I want to create a form which shows this information plus a totoal for that month.

Assuming you know how to write SQL to get totals, after you display all your other stuff, you run your SQL in some fashion (there are many ways).

One way might be something like:

Set myRecordSet = currentDB.OpenRecordset(“Select total(theNumbers) as anns_total from myTable where person = 'ann'")

You put this information in an unbound field on your form, such as

me.myUnboundField = myRecordSet("anns_total")

If you are running a full set of Ann's records on your form, you can also get a total from that by looping in some way over the record set.

When the recordset is available to the form, you'd go something like:

dim calc as double

calc=0
If myRecords.RecourdCount < 0 then

myRecords.moveFirst

do until myRecords.EOF

calc = calc + myRecords("numberField")

Loop

me.DisplayTotal = calc

End if

I may not understand this and I think that I should try to explain my request a little better.

Please refer to my example in the beginning.

What I am trying to obtain is that part that says "Total Records for March are xxx"
"Total records for April are xxx"

I am trying to get the xxx part of the equation. It is a total of all of the records for that month regardless of the user.

So you have a form that shows all the numbers for a single user, and now you want the total of those numbers, you need to loop over that information, using a method similar to what I said in my first response.

You could also run a second query (select total(someField) as personTotal from mytable where id = personID), but you'd run the risk of the total changing because of a change of data during the split second while you are doing this ... the total wouldn't match the list.

Anyway, you can get the total on form1 and put this information in a nonvisible field. On your popup window, you'd reference this invisible field: forms!myMainForm.myInvisibleField

I usually set the background color of invisible fields to orange (any color would do) to document their existance.

If you need to get the total records for all users for a particular month, you sql would run something like this

select count(*) as records from table where date between #1/1/2006# and #1/31/2006#

I believe you can get a grand total in the same query.

OR, you can get the recordcount of what is displayed

me. myRecordCount = queryResults.recordcount

Will return a recordset that looks like

100 Mary
100 John
100 Tim

for March 2006.



will get you the total records for the same period.