Query!! Maybe????

  • Thread starter Melissa needing help!!
  • Start date
M

Melissa needing help!!

I'm really new to this program so I might be asking a question that cannot be
done. I have a table that has fields Event Date, Bar service, Approx Number
of People and many more. With this table I'm looking to answer 4 questions
they are, How many events in each month?
How many People were present?
How many events used the bar service?
How many people at that event used the bar? (this answer would just be
taking from the number of people present). Am I asking for somthing that
cannot be done? If I can do this I want this information to be placed in a
report? Does anyone have any suggestions? Thanks so much.
 
S

stcyrm

Good morning Melissa

Depending on your table layout, I feel that you can extract the
required info using either a Group By query or a Crosstab Query.

Best regards

Maurice St-Cyr
 
J

John Vinson

I'm really new to this program so I might be asking a question that cannot be
done. I have a table that has fields Event Date, Bar service, Approx Number
of People and many more. With this table I'm looking to answer 4 questions
they are, How many events in each month?
How many People were present?
How many events used the bar service?
How many people at that event used the bar? (this answer would just be
taking from the number of people present). Am I asking for somthing that
cannot be done? If I can do this I want this information to be placed in a
report? Does anyone have any suggestions? Thanks so much.
Click to expand...

Access is PERFECTLY capable of doing all this and more.

It sounds like you're looking in the wrong place, though. Tables do
NOT do calculations or totals. Tables store data! You do indeed need
to use Queries based on your table to do this.

Create a Query based on your table.

To get the month, type in a vacant Field cell

EventMonth: Format([Event Date], "yyyy-mm")

This will give you the date in the format 2004-11, 2004-12, 2005-01
and so on so it will both let you group by month and sort
chronologically. Also select the Event Date field itself, and the
Approx Number of People. I don't know what the [Bar Service] field
contains - is it a yes/no field, the number of people using the bar,
or what?

Change the query to a Totals query by clicking the Greek Sigma icon
(looks like a sideways M). A new row, Totals, will appear in the query
grid, with Group BY as the default. Leave that alone on the calculated
EventMonth field; change it to Count on the EventDate field (to count
events in that month), to Sum on the [Approx Number of People] field
to count guests, and I'm not sure what you want on the Bar Service.
(For me, I'll take a glass of the Cabernet, thanks!)

You can then base a Report on this Query.

John W. Vinson[MVP]
 
J

John Vinson

HI John,

Thank you so much for the help, this well help me a lot, the bar service is
a yes/no field. In our report we want to know if the bar service was used
and how many people used it each month. Any thoughts to what I will do here?
Click to expand...

Sneaky trick: since Yes is stored in the computer as -1 and No as 0,
put in a calculated field

- [Bar Service] * [Estimated Number of People]

and sum it. Just multiplying will get the negative of the number of
people at events with bar service; the - sign will make that positive.

And thanks for the cab! <g>

John W. Vinson[MVP]
 
Top