Access - Grouping, Sorting, Averaging

Access 2000

I've got several tables I'm running a query on. One of the tables records the number of hours worked for each week, for each person.

EXAMPLE:
Name Project Category Week Beginning Hours
Doe, John P1 X 01-Jan-2001 24
Doe, John P1 X 08-Jan-2001 20
Doe, John P1 C 08-Jan-2001 20
Dun, Jane P2 X 01-Jan-2001 20
Dun, Jane P2 C 01-Jan-2001 10
Doe, John P1 X 05-Feb-2001 20
Doe, John P1 X 05-Feb-2001 20

...and so forth.

What I want to do is run the query so that the results show as below (using the above example):

Name Project Category Total Month Hours
Doe, John P1 X Jan 2001 44
Doe, John P1 C Jan 2001 20
Dun, Jane P2 X Jan 2001 20
Dun, Jane P2 C Jan 2001 10
Doe, John P1 X Feb 2001 40

Thus, I would like to:
Group each person by month, by project, and by category.

This way I get a combined monthly statement of the hours that were worked by each person and on what project.

Am I going about this the right way, or should I be doing something in my tables before I get to this point?

Thanks for any help!
Ana

 

Access

I would perfom whatever calculations are need on a form and make a report from that if you need a hard copy.

 

Expand please

Can you expand on this please?

 

Anakalia:

I would suggest doing this in 3 steps:
1. if using multiple tables, keep your first query as-is
2. create a second query, using the first query as your source, to summarize the data. of course, you'll have to transform the date to derive the specific element desired (ie: month/year/day).
3. create your report from the second query where you'll do the appropriate sorting and grouping.

 

computer

Thanks for the note. After playing around with queries for the past week, that is the conclusion that I came up with too. Unfortunately I've got a lot of information that needs to be ordered in a specific way and I'll be narrowing everything down with several queries.

I'm sure there has to be a better way, but this quick fix will work until I can learn more!

 

Access/Queries

I've been working with Access and complex SQL queries for awhile......if there's anything I can help you with, I'd be happy to.

Good Luck

 

Another question about queries

I've got another interesting complication in my query. I have a date field in the table that stores "week beginning" dates as 01-Jan-01. When I created my query I needed to have the dates grouped together by month. (Although I have to track my info weekly, I only report on it monthly.)

So, I did a summary while making the query and had it summarize by month. This worked perfectly and now my records in the date field are listed as January 2001, February 2001, etc. The problem is that I need them to be ordered chronologically. I can't sort them alphabetically, so do you know of a way I can sort these chronologically?

If I do a yearly report that is broken down by months, they are based on the order of the records they are attached to, not by Jan, Feb, Mar, Apr.

Is this something I can do in the report, or is this something I should do with the formatting of my dates? I have no choice (as far as I know) as to how the dates are formatted in the query.

THANKS!!!!!

 

How about adding another column to your query that extracts the Month from your date. The result will be the number of the month. You can choose to not show this column in the result, but do sort on that column. This will order the result of the query by the month.

- or -

You can add the column to your query and sort on that column in your report.

 

Stumped

I'm not really that great at the whole query thing - how do I extract the month into a new column in the same query?

 

Extracting Month

Access has a built-in function called Month. In the Select statement of your query, you would add the following expression for the new column:

Month(date column name/I]) as MonthNo

Notes:
- replace "date column name" with the name of your column that contains the date
- "MonthNo" will be the name of your new column containing the number of the month that was extracted. You can use this name or any name you're comfortable with.

If you're still not sure, send me your query. My email address is located in my profile.

 

correction

somehow the message added codes that you didn't need to see.
the following 2 lines are corrected.

Month(date column name) as MonthNo

Notes:
- replace "date column name" with the name of your column that contains the date

 

Thanks!

It worked perfectly! Thanks for all your help!


Anakalia

 

You are very welcome!