Group Sum

  • Thread starter Thread starter Steven
  • Start date Start date
S

Steven

We have a tblEnquiry based on 'Contact Type' and 'Date'
of contact. We have a report based on a query linking the
two, and grouped by priority - Month, Contact Type, Date.
We have tried to count the number of dates for each
contact type for each month ie. Group Sum, in the group
footer, but Access does not allow us to do this. We have
tried =Sum([tblEnquiry]![Date]), - the square brackets
cannot be saved and a huge total is produced. Is this
something to do with trying to count dates, and thus we
need another query???
Any advice appreciated. tks.
 
Um, if you want a Count of the number of dates, why are you using Sum?

Dates are stored as 8 byte floating point numbers, where the integer part
represents the date as the number of days relative to 30 Dec, 1899, and the
decimal part represents the time as a fraction of a day. Today (19 Jul,
2004) is 38187 (and 6:00 AM today is 38187.25, Noon today is 38187.5 and so
on) That probably explains why you're getting "huge totals"

BTW, Date is a reserved word, and isn't recommended as the name of a table
field or a variable. I'd recommend renaming your field.
 
Doug, thanks for your explanation. If not SUM, What
should I be using to sum the number of entries (by date)
per month?
thanks
-----Original Message-----
Um, if you want a Count of the number of dates, why are you using Sum?

Dates are stored as 8 byte floating point numbers, where the integer part
represents the date as the number of days relative to 30 Dec, 1899, and the
decimal part represents the time as a fraction of a day. Today (19 Jul,
2004) is 38187 (and 6:00 AM today is 38187.25, Noon today is 38187.5 and so
on) That probably explains why you're getting "huge totals"

BTW, Date is a reserved word, and isn't recommended as the name of a table
field or a variable. I'd recommend renaming your field.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


We have a tblEnquiry based on 'Contact Type' and 'Date'
of contact. We have a report based on a query linking the
two, and grouped by priority - Month, Contact Type, Date.
We have tried to count the number of dates for each
contact type for each month ie. Group Sum, in the group
footer, but Access does not allow us to do this. We have
tried =Sum([tblEnquiry]![Date]), - the square brackets
cannot be saved and a huge total is produced. Is this
something to do with trying to count dates, and thus we
need another query???
Any advice appreciated. tks.
Click to expand...


.
Click to expand...
 
Don't you want to Count the number of entries (by date)?

--
Duane Hookom
MS Access MVP


Steven said:
Doug, thanks for your explanation. If not SUM, What
should I be using to sum the number of entries (by date)
per month?
thanks
-----Original Message-----
Um, if you want a Count of the number of dates, why are you using Sum?

Dates are stored as 8 byte floating point numbers, where the integer part
represents the date as the number of days relative to 30 Dec, 1899, and the
decimal part represents the time as a fraction of a day. Today (19 Jul,
2004) is 38187 (and 6:00 AM today is 38187.25, Noon today is 38187.5 and so
on) That probably explains why you're getting "huge totals"

BTW, Date is a reserved word, and isn't recommended as the name of a table
field or a variable. I'd recommend renaming your field.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


We have a tblEnquiry based on 'Contact Type' and 'Date'
of contact. We have a report based on a query linking the
two, and grouped by priority - Month, Contact Type, Date.
We have tried to count the number of dates for each
contact type for each month ie. Group Sum, in the group
footer, but Access does not allow us to do this. We have
tried =Sum([tblEnquiry]![Date]), - the square brackets
cannot be saved and a huge total is produced. Is this
something to do with trying to count dates, and thus we
need another query???
Any advice appreciated. tks.
Click to expand...


.
Click to expand...
Click to expand...
 
Yes, apologies for incorrect terminology
-----Original Message-----
Don't you want to Count the number of entries (by date)?

--
Duane Hookom
MS Access MVP


Doug, thanks for your explanation. If not SUM, What
should I be using to sum the number of entries (by date)
per month?
thanks
-----Original Message-----
Um, if you want a Count of the number of dates, why
Click to expand...
are
you using Sum?
Dates are stored as 8 byte floating point numbers,
Click to expand...
where
the integer part
represents the date as the number of days relative to
Click to expand...
30
Dec, 1899, and the
decimal part represents the time as a fraction of a
Click to expand...
day.
Today (19 Jul,
2004) is 38187 (and 6:00 AM today is 38187.25, Noon today is 38187.5 and so
on) That probably explains why you're getting "huge totals"

BTW, Date is a reserved word, and isn't recommended as the name of a table
field or a variable. I'd recommend renaming your field.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


"Steven" <[email protected]> wrote
Click to expand...
in
message
We have a tblEnquiry based on 'Contact Type' and 'Date'
of contact. We have a report based on a query
Click to expand...
linking
the
two, and grouped by priority - Month, Contact Type, Date.
We have tried to count the number of dates for each
contact type for each month ie. Group Sum, in the group
footer, but Access does not allow us to do this. We have
tried =Sum([tblEnquiry]![Date]), - the square brackets
cannot be saved and a huge total is produced. Is this
something to do with trying to count dates, and thus we
need another query???
Any advice appreciated. tks.


.
Click to expand...
Click to expand...


.
Click to expand...
 
Did you try
=Count(*)

--
Duane Hookom
MS Access MVP


Steven said:
Yes, apologies for incorrect terminology
-----Original Message-----
Don't you want to Count the number of entries (by date)?

--
Duane Hookom
MS Access MVP


Doug, thanks for your explanation. If not SUM, What
should I be using to sum the number of entries (by date)
per month?
thanks
-----Original Message-----
Um, if you want a Count of the number of dates, why are
you using Sum?

Dates are stored as 8 byte floating point numbers, where
the integer part
represents the date as the number of days relative to 30
Dec, 1899, and the
decimal part represents the time as a fraction of a day.
Today (19 Jul,
2004) is 38187 (and 6:00 AM today is 38187.25, Noon
today is 38187.5 and so
on) That probably explains why you're getting "huge
totals"

BTW, Date is a reserved word, and isn't recommended as
the name of a table
field or a variable. I'd recommend renaming your field.

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)


message
We have a tblEnquiry based on 'Contact Type' and 'Date'
of contact. We have a report based on a query linking
the
two, and grouped by priority - Month, Contact Type,
Date.
We have tried to count the number of dates for each
contact type for each month ie. Group Sum, in the group
footer, but Access does not allow us to do this. We
have
tried =Sum([tblEnquiry]![Date]), - the square brackets
cannot be saved and a huge total is produced. Is this
something to do with trying to count dates, and thus we
need another query???
Any advice appreciated. tks.


.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top