Sortting Data in changing field

[brian]

Hi, I am working on a database to track downtime on my production
line. I have setup a form so you can use a drop down menu to select
the downtime reason, and next to that reason you can insert the number
of minutes we were down.

What I want to do is be able to calculate total downtime for each of
my reasons (from the pulldown list) and be able to use this data to
calculate which reason caused the most downtime. Below I have listed
an example of fields from my table.

1:Reason for Downtime 1pull down list)
2:Reason for Downtime 2pull down list)
3owntime 1number)
4owntime 2number)

If field 1 says: 'Machine Stops' then field 3 would have '15'
Then if field 2 says: 'Lunch' then field 4 would says '30'

I would like to sort the reasons and add up the minutes.

The problem is I don't know what to use to do this. It sounds like I
am going to have to write some code so that i can search for my
different reasons then grab the number of minutes from a different
cell and add them up.

Any ideas? Should I use a querie? Macro? VB Code? Pivot Table? Any
help would be apreciated.

Thanks
Brian

 

[JulieD]

Hi Brian

um ... reason for downtime 1 / reason for downtime 2 - are these two
separate fields in your table?
or do you just have one "reason for downtime" field in your table.

This makes a difference because the first example indicates (to me, anyway)
a "non-normalized" database which makes quering (and a lot of other things)
difficult and i would suggest changing the structure of your database before
you get much further.

If however, you have only "one reason for downtime" field in your table,
matched with a field for the length of time then you can use a select query
to achieve what you're after

to do:
1. choose new query in design view
2. add the table in that stores the reason and the time - close the select
table dialog box
3. double click on the reason field
4. double click on the time field
5. click on the Total icon on the toolbar (looks like a fancy E)
6. under the reason field choose Group By (should be there already)
7. under the time field choose Sum
8. run the query (!)

Let us know how you go.

Cheers
JulieD