Help in counting different data.....

[shieriel]

Please help me. I have a database with monitoring of hours work and i want a report to count total hours of work with graph, example:
-worked greater than 16hrs = ?
- worked between 8 to 16 hrs = ?
- worked between 4 to 8 hours = ?
- worked between 2 to 4 hours = ?
- worked between 1 to 2 hours = ?
- worked less than 1 hour = ?

I can easily done this in excel but i dont know how to do in access. Experts please help...

Many thanks in advance...

 

[shieriel]

please help anyone?...

 

[Brianwarnock]

create fields in query

gtrthan16:Sum(iif(hoursworked>16,1,0))

frm8to16:sum(iif(hoursworked>=8 and hoursworked<=16,1,0))

etc

Brian

 

[shieriel]

Thank you very much. I will try this today, hope this work..

 

[shieriel]

create fields in query

gtrthan16:Sum(iif(hoursworked>16,1,0))

frm8to16:sum(iif(hoursworked>=8 and hoursworked<=16,1,0))

etc

Brian

Hello sir. Remember the formula you gave me? It is working very good. I applied it on my monthly report on my project. The only thing is i cannot apply this when doing on showing all the monthly summary.

If you look into the attached png file the summary is for January only. How can i make it to show the field value and also showing all the month such as January, February, and so on. I always got an error about "aggregate function" when i tried to put another formula for the field in a query.

 

[Brianwarnock]

I'm sorry I don't follow what you are trying to do, can you post the query that you have tried, ie copy and paste the SQL. It might help.

Brian

 

[shieriel]

I'm sorry I don't follow what you are trying to do, can you post the query that you have tried, ie copy and paste the SQL. It might help.

Brian

Sir what i am trying to say is how can i apply using that formula to show the records per month. Please see attached sample in excel. How can i apply that on my query.

 

[shieriel]

Sir what i am trying to say is how can i apply using that formula to show the records per month. Please see attached sample in excel. How can i apply that on my query.

Please see attached png file for the sample of the formula on my query. How can apply that to show all the data for let's say January to July. Because currnetly i am doing it one by one for every month. Is there simple way for this? Many Thanks in advance

 

[shieriel]

Please see attached png file for the sample of the formula on my query. How can apply that to show all the data for let's say January to July. Because currnetly i am doing it one by one for every month. Is there simple way for this? Many Thanks in advance

Sir i attached here a picture of my current form showing all the data per month. Is there a much simpler way maybe a crosstab query to show just like on the excel form i sent.
Thanks again.

 

[Brianwarnock]

It looks like you should be able to add a group by monthname to your query, but the data will not be in calendar month order as you need the month number for that. If you have the month number you should be able to sort on it without having to show it.

Brian

 

[shieriel]

It looks like you should be able to add a group by monthname to your query, but the data will not be in calendar month order as you need the month number for that. If you have the month number you should be able to sort on it without having to show it.

Brian

Sir, can you give me some sample project for this? I am a little bit confused on how to do it. Sorry just beginning to learn more about access...

 

[Brianwarnock]

Let's see if we can get a few points clear.
1 Using Sum as we have

gtrthan16:Sum(iif(hoursworked>16,1,0))

automatically makes the query a Totals query.
Thus if you click on the Sigma sign in the design grid to create a Totals query then you must select expression in all of the columns with the Sum function in the Field row.

2 Do you have a Date, or a numerical value for the month in the table? Without one of these you cannot select a range of months.

If you have then place this field in the query grid and select Group By in the totals row, and then code your criteria as required.

If you only have a text month then you will have to manufacture the selection. The simplest approach is to construct a 2 field table of txtMonth and month_number
ie January 1
February 2
etc , and join this to your original table on the text Month fields, select the month_number, you can unclick the Show, criteria and sorting can be applied to this numeric field.

If all else fails attach you DB. note that I do not have 2007.

Brian

 

[shieriel]

Let's see if we can get a few points clear.
1 Using Sum as we have automatically makes the query a Totals query.
Thus if you click on the Sigma sign in the design grid to create a Totals query then you must select expression in all of the columns with the Sum function in the Field row.

2 Do you have a Date, or a numerical value for the month in the table? Without one of these you cannot select a range of months.

If you have then place this field in the query grid and select Group By in the totals row, and then code your criteria as required.

If you only have a text month then you will have to manufacture the selection. The simplest approach is to construct a 2 field table of txtMonth and month_number
ie January 1
February 2
etc , and join this to your original table on the text Month fields, select the month_number, you can unclick the Show, criteria and sorting can be applied to this numeric field.

If all else fails attach you DB. note that I do not have 2007.

Brian

Hi Sir Brian. My query works now, thanks for your support!
Here is what i do, as you said i clicked the Sigma sign and selected "Expression" in replacement of "Group By" in the Total Field (which i missed previously). Then, when i tried to add "MonthName" into the field i have no more error running the query. Now instead of creating single query for every month, i now have a single one showing all the months in a year with corresponding summary.
Thank again!...

 

[Brianwarnock]

Glad we got there and happy to have helped.

Brian