Sum particular fields on a subform

[jwleonard]

I have a subform that contains a history of workorder statuses. It shows evertime the status changes and I want to create a summary of how long each workorder was in each status.

For instance a workorder might go from "Awaiting Maintenance" for 3 days to "Awaiting Part" for 2 days and back to "Awaiting Maintenance" for 1 day. I want a group of text boxes to show how long it was in "Awaiting Mainenance" (4 days total in the example above) and how long it was in "Awaiting Part" (2 days in the example above). I also want to show total days.

I have the subform that shows each status and how long it was in that status by subtracting the status start date from the end date. This shows the days for each status change; now I want to add the days for each status to get the overall time for each one.

In excel I would use sumif to specify which rows to sum but I have now idea how to do this in Access.

 

[Ron_dK]

You might do a search on datediff.

 

[jwleonard]

Thanks but the DateDiff will only get me the number of days for each status which is what I already have.

I could have been more clear in ewhat I was asking though.

I have two subform fields that I am concerned with; Status & Days. Lets say I have the following:
Status Days
A 2
B 3
A 4
B 1

I want to show on the form that the total of all A's = 6 and the total of all B's = 4. If I just use sum I get 10 because it adds every row, I need it to add each status seperately.

It is a continuous form, I forgot to mention that before.

*There should be space in the example above to line up the numbers under days, but when I post it removes the extra spaces*

 

[fibayne]

Hi
Try adding a Form Footer to your sub form, doing the calc there rather than within Detail should total as you want.
Fi

 

[jwleonard]

I have tried putting it in the footer, that is where my overall total is now. The problem is I can't get the sub total of each status.

 

[fibayne]

have you created a field to sub total each status in the Form Footer individually ?

 

[jwleonard]

fibayne, sorry it took me so long to respond; I didn't get an e-mail saying another person had replied. I couldn't get it to total each status in the footer; I could only get a total for all statuses together.
I did find a way to make it work though; I created a subform for each status by placing the status in the query as criteria. Then created a calculated field for to get the total. I placed the subforms on my mainform so they only show up like a text box with a label.
My only concern is if this will slow things down when it gets filled with lots of data since there are so many subforms on the main form.