sumif in Access

[JGrots]

Hello,



I'm trying to sum the production output for all the Work Order numbers that are the same over a 52 week period. For example, I want to create a report for Week 3 and have all the work orders for that week be shown along with the total production for each work order for that week and the total production for that work order from weeks 1, 2 and 3.
Example data:
W/O: 12345A Week1= 10, Week2= 20, Week3= 35
W/O: 6789B Week1= 15, Week2= 0, Week3= 45
W/O: 01010C Week1=150, Week2= 50 Week3= 100

Week #	W/O	Weekly Total	W/O Total
3	12345A	35	65
3	6789B	45	60
3	01010C	100	300

Is there a way to do this? If so, should I do this in a query or in the report?

Any help would be much appreciated. Thanks!

[June7]

If you want to show detail data, do the summation in report. Use report Grouping & Sorting with aggregate calcs in group and report footers.

[alansidman]

See the attached. I created two aggregate queries. If you want the information in one report, then you will have to create a report and use the queries as the record source for the report.

Edit: Slow again--or use June's idea.

[JGrots]

Alansidman,

Thanks for the reply, but when I try to open query 2 I get this pop-up (Query1.SumOfWOAmount). What do I do?

[JGrots]

June7,

I have over 100 different Work Order Numbers and I don't want to have the total shown below each W/O. I want to have it show the total in the same row as the Work Order. Can this be done?

[June7]

You want the total to show in each detail record? Yes, can be done. One is to build an aggregate (GROUP BY) query that groups on workorder and sums the Total fields. Join that query to the detail data source. The aggregate calcs will show in each row.

[alansidman]

Sorry about the error. I fixed in attached and incorporated June's suggestion

[JGrots]

June7 and Alansidman,

Thank you both for your help!! Last question, is there a way for me to create a report that automatically opens up the current week instead of having to input the number myself. For example, if week 3 are the dates 1/14 - 1/19 and the current date is 1/17, then I want the report to open week 3. If the current date is 1/21, then I want the report to open week 4. Is there a way to do this?

[alansidman]

You will have to build a table with the week numbers and the date range. Three fields. Then apply criteria to the week number in your query that determines the current week number based upon the current date. You will have to join the two tables on the week number. Look at the attached.

[JGrots]

Alansidman,

Thank you for all your time and help! It is much appreciated!

[alansidman]

You are welcome.

[June7]

Alan & JGrots, actually the weeknumber can be calculated with Format function.

Format(Date(),"ww")
That expression uses the defaults of vbSunday as first day of week and vbFirstJan1 as first day of year.

Format(Date(),"w")
I think returns day of week - better to use Weekday(Date())

Format(Date(),"www")
using more than 2 w's returns something I don't know what it is

[alansidman]

June

Thanks. Didn't think to go in that direction. Nice addition.


alan