John Spencer's suggested SQL looked pretty good to me, assuming that you
wanted to count days in more than one column. But I noticed a typo in
his SQL that wasn't a big deal, but would likely keep you from being
able to use it directly.
Anyway, suppose your Table looks like this:
[Meals] Table Datasheet View:
YourDateField Morning Lunch Afternoon Evening
------------- ------- ----- --------- -------
2/5/2005 No No Yes Yes
11/5/2005 Yes Yes Yes Yes
12/1/2005 Yes No No No
12/3/2005 No Yes Yes No
12/6/2005 Yes Yes No Yes
John Spencer's SQL for this Table would look like this:
[Q_Meals] SQL:
SELECT Format([YourDateField],"yyyy-mm")
AS YrMonth,
Sum(Abs([Morning])) AS MorningCount,
Sum(Abs([Lunch])) AS LunchCount,
Sum(Abs([Morning] And [Lunch]))
AS MorningLunchCount
FROM Meals
GROUP BY Format([YourDateField],"yyyy-mm")
ORDER BY Format([YourDateField],"yyyy-mm");
Note: I think the easiest way for you to use SQL appearing in a message
like this would be to ...
- Define a Table that the Query will match. In this case, it would
have a Date/Time field called [YourDateField] and Yes/No fields called
[Morning] and [Lunch].
- Open a new Query in Query Design View
- Select no Tables (click "Close" on the Show Table window)
- Switch to SQL view, and erase the "SELECT;" line appearing there
- Copy the SQL from this message to the blank SQL window
- Switch back to Query Design View to edit the Query
The result of running the [Q_Meals] Query on the [Meals] Table shown
above would be ...
[Q_Meals] Query Datasheet View
YrMonth MorningCount LunchCount MorningLunchCount
------- ------------ ---------- -----------------
2005-02 0 0 0
2005-11 1 1 1
2005-12 2 2 1
Notice that the record for 11/5/2005 is counted in both the
[MorningCount] and the [MorningLunchCount] columns. If this is not what
you want (for example, if you want it counted as [MorningLunchCount] but
not as [MorningCount]), you'll have to change the design of the Query.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
Thank you, I do need how on how to do this in the query grid.
Thank You,
Melissa
:
You will need a Totals query with the following formulas
Abs(Sum(Morning)) as MorningCount
Abs(Sum(Morning And Lunch)) as MorningLunchCount
The SQL would look something like
SELECT Format([YourDateField,"YYYY-MM") as YrMonth
Abs(Sum(Morning)) as MorningCount,
Abs(Sum(Lunch)) as LunchCount,
Abs(Sum(Morning And Lunch)) as MorningLunchCount,
....
FROM [YourTableName]
GROUP BY Format([YourDateField,"YYYY-MM")
If you need further explanation on how to do this in the query grid, post
back.
"Melissa needing help!!" <
[email protected]>
wrote in message
I have 4 colums of time slots "morning, lunch, afternoon, and evening. I
want to find out how many times a month each time slot was used, as well
as,
the different combinations, such as "morning and Lunch, or lunch and
afternoon or afternoon and evening. These colums are yes/no answers.
How do I do the query for this.
Thank you so much for your help
Melissa
