How to perform this task?

[Tsung90]

Hi, I need to create a query to produce a report showing 'how busy the pool is over a typical week'.

In my tblPool:

Column 1: Pool_Ticket, e.g. a0001

Column 2: Ticket_Type, e.g. MP or NP, MP = member of the gym and NP = non-member of the gym.

Column 3: Member_ID

Column 4: Time_Pool = the time the member or non-member used the pool.

Column 5: Date_Pool = the date the member or non-member used the pool.

Do I need to add any more fields to be able to create the query or are the fields I have already, enough?

Many thanks.

 

[plog]

Depends on what you want the output to be and what Time_Pool contains. Can you post a sample of the output you want?

Also, does Time_Pool note a time of the day that a user entered the pool (i.e. 4:23 pm) or does it represent the amount of time a person spent at the pool (i.e. 72 minutes)?

 

[Tsung90]

E.g. the number of members or non-members who have used the pool in a typical week and whether it's busier in the morning, the afternoon or the evening.

Also, Time_Pool = the time the member or non-member used the pool (i.e. 4:23 pm).

 

[plog]

Now you've got 2 queries: Q1 is number of pool users in a week, Q2 is busyness of pool during times of day. Here's my questions for each:

Q1. Do you want unique pool users per week, or total pool users? If every day a member comes at 8 am, leaves at 10 am, returns at 4 pm and leaves at 5 pm how many times are they counted that week? 1 time because its a unique person no matter how many visits, 7 times because its a unique person on a unique day, or 14 times because its 2 visits per day?

2. Define morning, afternoon and evening. What hours constitute each? Also, since you don't capture leaving time, suppose evening starts at 5:00 pm, should a person who comes at 4:59 pm be counted in afternoon or evenining or both?

 

[Tsung90]

I would like to be able to see the total number of pool users for a typical week and to distinguish between members and non-members. Ignore the morning, afternoon and evening query.

Thanks.

 

[Tsung90]

I would also like to be able to see whether weekdays are busier or weekends. So from Monday to Friday or Saturday and Sunday.

 

[jdraw]

You may get some ideas from samples and syntax for Aggregate functions and Group By
at http://www.techonthenet.com/sql/group_by.php

and http://www.techonthenet.com/sql/count.php

I'd suggest you start with your table and some test data, set up some queries and adjust as necessary to gt the details/outputs you want.

You can always come back with more questions as you refine your activity.

Good luck with the project.

 

[Tsung90]

I'm a novice user so it'll be hard for me to carry out the queries I want. I can produce basic queries but not the ones I require.

 

[jdraw]

Why not start with something basic, and see how it goes? Add to it as needed.

Post back with questions as necessary.
Get your tables set up first. Then work on a query or 2.

Ya' gotta start somewhere! People are here to offer help/advice.

 

[Tsung90]

I've set up the tables and tried setting up the query and no luck. I've done everything apart from the query that I require now, that's why I posted a question in this forum, to ask for help. The table is listed at the top...

 

[jdraw]

Please post a jpg of your tables and relationships.
Please post the SQL for the query that is giving you trouble.

 

[MSAccessRookie]

Hi, I need to create a query to produce a report showing 'how busy the pool is over a typical week'.

In my tblPool:

Column 1: Pool_Ticket, e.g. a0001

Column 2: Ticket_Type, e.g. MP or NP, MP = member of the gym and NP = non-member of the gym.

Column 3: Member_ID

Column 4: Time_Pool = the time the member or non-member used the pool.

Column 5: Date_Pool = the date the member or non-member used the pool.

Do I need to add any more fields to be able to create the query or are the fields I have already, enough?

Many thanks.

It appears that JDraw's suggestion regarding using Aggregate Functions with a Group By Statement is exactly what you need. Check out the links in detail, and get back with any questions about Aggregate Functions

On another note, Fields 4 and 5 should be exactly the same (unless you are entering the values as Text). If you are, then consider using a Single Date/Time Type Field and having the Query separate the Date and Time using the Format() Function. It is easier to aggregate and sort Date/Time values in Date/Time Format.