query

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

Guest

I have 3 colums in my query
1. Event date by month
2. Approx. number of people
3. Bar service
I want to add a forth that is called "Approx. number of people for Bar
Service"
Each month there is an approx number of people who has the bar service. I
want to find out of those who had the bar service how many people were there.
what is the formula for my question.
 
There's no way of knowing without information about how those fields came to
be in your query in the first place. On what table or tables is the query
based? How do these tables relate to each other?
 
I have 3 colums in my query
1. Event date by month
2. Approx. number of people
3. Bar service
I want to add a forth that is called "Approx. number of people for Bar
Service"
Each month there is an approx number of people who has the bar service. I
want to find out of those who had the bar service how many people were there.
what is the formula for my question.
Click to expand...

Put a calculated field in the Query:

HadBarService: IIF([Bar Service], [Approx. Number of people], 0)

and sum this field grouping by month.

John W. Vinson[MVP]
 
Thank you very much. This will defiinatly work!!

Melissa

John Vinson said:
I have 3 colums in my query
1. Event date by month
2. Approx. number of people
3. Bar service
I want to add a forth that is called "Approx. number of people for Bar
Service"
Each month there is an approx number of people who has the bar service. I
want to find out of those who had the bar service how many people were there.
what is the formula for my question.
Click to expand...

Put a calculated field in the Query:

HadBarService: IIF([Bar Service], [Approx. Number of people], 0)

and sum this field grouping by month.

John W. Vinson[MVP]
Click to expand...
 
Hi John,
Just wondering if you could help me with one more thing. My table has colums
with month/year, approx number of people, bar service plus more. Anyways
I'm trying to find the date, how many people, was there bar service, how many
people attented the bar service. when I do my query for that, the month/year
will show me
02-2004
03-2004
04-2004
05-2004
05-2004
05-2004 The 05-2004 is because there was a bar service for two events and
one had no bar service. My bar service is a yes/no field. How can I get the
month/year to show only once, and my approx number of people to be added
together, than have the bar service than have the approx number of people for
the bar service. I hope you understand what I'm looking for!! It's hard to
write it out when it's not in front of you!!!!

Thanks again for your help.
Melissa
John Vinson said:
I have 3 colums in my query
1. Event date by month
2. Approx. number of people
3. Bar service
I want to add a forth that is called "Approx. number of people for Bar
Service"
Each month there is an approx number of people who has the bar service. I
want to find out of those who had the bar service how many people were there.
what is the formula for my question.
Click to expand...

Put a calculated field in the Query:

HadBarService: IIF([Bar Service], [Approx. Number of people], 0)

and sum this field grouping by month.

John W. Vinson[MVP]
Click to expand...
 
Hi John,
Just wondering if you could help me with one more thing. My table has colums
with month/year, approx number of people, bar service plus more. Anyways
I'm trying to find the date, how many people, was there bar service, how many
people attented the bar service. when I do my query for that, the month/year
will show me
02-2004
03-2004
04-2004
05-2004
05-2004
05-2004 The 05-2004 is because there was a bar service for two events and
one had no bar service. My bar service is a yes/no field. How can I get the
month/year to show only once, and my approx number of people to be added
together, than have the bar service than have the approx number of people for
the bar service. I hope you understand what I'm looking for!! It's hard to
write it out when it's not in front of you!!!!
Click to expand...

I don't guess I understand. If there are two meetings, one of which
has bar service, and the other doesn't, what do you want to report for
that month? That there was bar service, or that there wasn't? Both
statements are true!

IF you mean that you want to see "yes" if any meeting during that
month had bar service, you can create a Totals query based on your
table. Add the date field, the bar service field, and the number of
people field. If you want to *also* sum the number of people who
attended meetings with bar service, add the IIF() statement from my
previous message as a calculated field.

On the Totals line, Group By the date; select Min() on the Bar Service
yes/no field (True is -1, False is 0, so this will show True if there
was any meeting in the month with bar service); and select Sum for the
number of people and the number of people with bar service.

John W. Vinson[MVP]
 
Thank you.

John Vinson said:
I don't guess I understand. If there are two meetings, one of which
has bar service, and the other doesn't, what do you want to report for
that month? That there was bar service, or that there wasn't? Both
statements are true!

IF you mean that you want to see "yes" if any meeting during that
month had bar service, you can create a Totals query based on your
table. Add the date field, the bar service field, and the number of
people field. If you want to *also* sum the number of people who
attended meetings with bar service, add the IIF() statement from my
previous message as a calculated field.

On the Totals line, Group By the date; select Min() on the Bar Service
yes/no field (True is -1, False is 0, so this will show True if there
was any meeting in the month with bar service); and select Sum for the
number of people and the number of people with bar service.

John W. Vinson[MVP]
Click to expand...
 
Back
Top