query

[Guest]

How do I SUM a date field to find how many times a room as been used in that
month. Eample:
Event Date Reception Hall
November 21, 2005 NO
November 25, 2005 YES
November 27, 2005 YES

I want to find out the total times the Reception Hall has been used for the
month of November.

Thank you for your help!

 

[Douglas J. Steele]

SELECT Format([Event Date], "yyyy-mm"),
Sum(Abs([Reception Hall]) AS TimesUsed
FROM MyTable
GROUP BY Format([Event Date], "yyyy-mm")

will summarize every month in your table.

If you only want the current month, use:

SELECT Format(Date(), "yyyy-mm"),
Sum(Abs([Reception Hall]) AS TimesUsed
FROM MyTable
WHERE [Event Date] BETWEEN DateSerial(Year(Date), Month(Date), 1)
AND DateSerial(Year(Date), Month(Date) + 1, 0)


If you only want the previous month, use:

SELECT Format(DateAdd("m", -1, Date()), "yyyy-mm"),
Sum(Abs([Reception Hall]) AS TimesUsed
FROM MyTable
WHERE [Event Date] BETWEEN DateSerial(Year(Date), Month(Date) - 1, 1)
AND DateSerial(Year(Date), Month(Date), 0)

 

[Ed Robichaud]

You really mean COUNT. Create a totals query, and set the criteria of the
[reception hall] column to "Yes" or -1, and totals row to "count".
-Ed

 

[Guest]

I've tried that but it counts each date and puts a 1 in every colum, even if
the Reception Hall has been rented 3 times in a month. I have four other
rooms that I want to know the same information for. My Reception hall Colum
is a YES/NO answer. Wow, I never thought something so simple would be so
confussing!! Thank you for your help.

melissa

You really mean COUNT. Create a totals query, and set the criteria of the
[reception hall] column to "Yes" or -1, and totals row to "count".
-Ed

How do I SUM a date field to find how many times a room as been used in
that
month. Eample:
Event Date Reception Hall
November 21, 2005 NO
November 25, 2005 YES
November 27, 2005 YES

I want to find out the total times the Reception Hall has been used for
the
month of November.

Thank you for your help!

 

[Ed Robichaud]

Oops! I should have included that you need to create an expression like
DatePart("mmm",[myDate]) to group on. That way, you count the total per
month, not the total per date.
-Ed

I've tried that but it counts each date and puts a 1 in every colum, even
if
the Reception Hall has been rented 3 times in a month. I have four other
rooms that I want to know the same information for. My Reception hall
Colum
is a YES/NO answer. Wow, I never thought something so simple would be so
confussing!! Thank you for your help.

melissa

You really mean COUNT. Create a totals query, and set the criteria of
the
[reception hall] column to "Yes" or -1, and totals row to "count".
-Ed

"Melissa needing help!!" <[email protected]>
wrote in message

How do I SUM a date field to find how many times a room as been used in
that
month. Eample:
Event Date Reception Hall
November 21, 2005 NO
November 25, 2005 YES
November 27, 2005 YES

I want to find out the total times the Reception Hall has been used for
the
month of November.

Thank you for your help!