Query

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

Guest

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
 
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.
 
Thank you, I do need how on how to do this in the query grid.

Thank You,
Melissa

John Spencer said:
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!! said:
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
Click to expand...
Click to expand...
 
Put you table into the query grid
Select View : Total from the menu bar
Drag in your date field
Change the field to
Field: YrMonth: Format([YourDateField], "yyyy-mm")
Total: Group By

--Optional step if you want to specify a date range--
Drag in your date field a second time
Change the "Group By" to "Where" (This will allow you to specify a date
range if you want to)

In a blank field, enter
Field: MorningCount: Abs(Sum([Morning]))
Change Total from Group By to Total
Repeat in additional cells for Lunch, Afternoon, and Evening

For the combinations:
Field: MorningLunchCount: Abs(Sum(Morning And Lunch))
Total: Expression

Hope that helps.

Melissa needing help!! said:
Thank you, I do need how on how to do this in the query grid.

Thank You,
Melissa

John Spencer said:
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
Click to expand...
Click to expand...
Click to expand...
 
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.

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
Click to expand...
Click to expand...
Click to expand...
 
Thank you guys for all your help I will try it and let you know if it works!

Vincent Johns said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top