Best way of doing this?

[QuietRiot]

in my main productionsupport table I have a field called category with 7 categorys you can choose and a field called TimeinHrs with the time spent on each category.

what i want is a summary report for the year that shows all 12 months up top then on the left the category and then time spent each month.

whats the best way of getting this on a report?

Im currently doing this in excel and im using 84 formulas in the spreadsheet to pull all those values.

there must be a better way then say.. 84 dlookups or the like. Do I write a SQL statement to create the entire table and then display the table on a report. and if i do this how could i get the total for each month or how would i even do the table.

any ideas.


Thanks,

 

[AC5FF]

Quick Question: In your table, you are tracking hours, but is there a date value with that?

I have a simiar table, tracking work hours, dates, productID's etc. I run a query from this table with a field:

Date: CDate("1 " & Format(Work![TIN Date],"mmm yy"))

This groups my results by month. The next field would be productID and the third would be hours, using something like:

Hours: sum(iif(work!Complete=true,work!hrs,0))

This would give me a sum by month then by productID for hrs spent on all that product during the month.

Fairly new to Access here, and not sure i'm completely wording this right...
Hope it's understandable..

 

[QuietRiot]

Quick Question: In your table, you are tracking hours, but is there a date value with that?

I have a simiar table, tracking work hours, dates, productID's etc. I run a query from this table with a field:
This groups my results by month. The next field would be productID and the third would be hours, using something like:
This would give me a sum by month then by productID for hrs spent on all that product during the month.

Fairly new to Access here, and not sure i'm completely wording this right...
Hope it's understandable..

i should of mentioned that. yes there is date in (shortdate). So I would be using DateReported, Category, TimeInHrs fields

still a little confused by using functions to pull 84 times and then totals

 

[AC5FF]

Access will do it automatically. It's not really using a function (IMO) as much as it is 'organizing' your data and summerizing it for you.
The first field in the query will group all entries by month
The second entry will group all by productid
The thrid will take all hrs within that month against that product and sum them.

Imagine 100 lines for 5 products over a 2 month period. It'll take the first month (50) first product and sum all the hrs and show it; 2nd product and sum hrs; etc...
when my query runs something very similar to this, it's pulling out data from around 60K records; and it will do a simple layout like this in less than a second...

I used to use Excel for my 'DB' needs; I had around 12K records at the time, each line had a formula attached (dlookup/sum/compair/etc.). When I went to run a compile for something like this I could almost go get a soda before it would finish.

 

[AC5FF]

Sample

Tossed this together... Just a quick idea; wanted to be sure I was on the same page that you were

If you look at the work table; I entered data randomly; dates mix and matched, catagories mix/match, ect. I also included a catagory table to give a 'longname' for each (makes entering data a lot easier IMO).

The query sorts by date, than by catagory, then sums the hrs.

Hope thsi can help!

 

[QuietRiot]

Tossed this together... Just a quick idea; wanted to be sure I was on the same page that you were

If you look at the work table; I entered data randomly; dates mix and matched, catagories mix/match, ect. I also included a catagory table to give a 'longname' for each (makes entering data a lot easier IMO).

The query sorts by date, than by catagory, then sums the hrs.

Hope thsi can help!

I get it. This is great.

Thank you!!

 

[AC5FF]

Wow! My first real attempt to help someone worked/??? I'm in SHOCK! LOL

Seariously, glad to hear it worked for ya. Good luck

AC