Sum Across Columns

[JStan89]

Hello all,

I'm very new to access, but see it has potential for a big payoff for a project I am doing. Currently, I'm stuck summing across the columns for Step 1 EC (est completion) - Step 8 EC. This may be a poor design on my part from inputting the data in the table. But all of these jobs being a stochastic process with always different steps in machining, I don't know how else to do it.

With all that being said, I would like each Piece Part to have a sum of days at the bottom of estimated completion and actual completion. Again, it must show the total for EACH part.

For example, as shown in the access file in report under "Piece Parts Report" the first part (No. 2 Aluminum Base Plate), it goes through 3 steps... with the steps being 1, 5, and 5 days respectively. I'd like for it to show at the bottom a total of 11 days for that part to be manufactured.

Can someone please help me get to a solution.

Thanks in advance!

 

[plog]

You need to structure your tables correctly. When you start numerating fields (i.e. Step 1, Step 2, Step 3...) its time for a new table. Instead of adding data horizontally (with more fields) you should add data vertically (with more rows).

In this instance it would be a Steps table. You would move all those numerated fields to a new table with an additional field that stores which step the record is for. The structure of that table would be this:

Steps
StepID, PieceID, StepNumber, StepType, Machine, EC, AC
1, 1, 1, Grinding (Manual), Waterjet, 1, Null
2, 1, 2, Grinding (CNC), Hurco, 5, Null
3, 1, 3, External, Other, 5, Null


Thats how your data should be. Then to get a total, you run a simple totals query to sum up the Steps table by PieceID.

 

[JStan89]

You need to structure your tables correctly. When you start numerating fields (i.e. Step 1, Step 2, Step 3...) its time for a new table. Instead of adding data horizontally (with more fields) you should add data vertically (with more rows).

In this instance it would be a Steps table. You would move all those numerated fields to a new table with an additional field that stores which step the record is for. The structure of that table would be this:

Steps
StepID, PieceID, StepNumber, StepType, Machine, EC, AC
1, 1, 1, Grinding (Manual), Waterjet, 1, Null
2, 1, 2, Grinding (CNC), Hurco, 5, Null
3, 1, 3, External, Other, 5, Null


Thats how your data should be. Then to get a total, you run a simple totals query to sum up the Steps table by PieceID.

Okay, So I created the table from your recommended structure. I'm not real sure on how to do the query. Can you help me? Sorry for my ignorance, I am very new at this. Also, should I be worried that each piece will have a different amount of steps? Will this work for multiple piece parts in the query all while giving different totals for the respective parts? I still have to be able to use my form, I hope...

 

[plog]

This SQL will provide you with the data you initially requested:

SELECT Table1.[Piece ID], Sum(Table1.EC) AS TotalEc
FROM Table1
GROUP BY Table1.[Piece ID];