Adding 1 row in a table in access?

[keri]

I am currently creating a database to keep records of time worked
for drilling rigs in the oilfield industry. Here's the general idea:
I enter "timesheets" for each rig and need to total how many hours
Each rig worked for the month. I've created the table and form, but
I can't get it to total only one row. It adds all hours of all
records (running
total) instead of isolating one record.

My table:
1-31 represent days of the month.
Rig # Month Year 1 2 31 Total hrs
11 Dec 2003 8 8 10 48
88 Dec 2003 10 12 12 49

First and foremost, I need to know how to only get a total for rig 11
for days
1-31.
ie. Total hours worked is 48

The formula I used in the total hours worked field is
=Sum([1]+[2]+[3])..etc

Right now I get a running total of rig 11 and 88 in the total hours
worked field.
Ie. Total hours worked is 97 (48+49)

Thanks for all your help.

Keri

 

[Joseph Meehan]

I am currently creating a database to keep records of time worked
for drilling rigs in the oilfield industry. Here's the general idea:
I enter "timesheets" for each rig and need to total how many hours
Each rig worked for the month. I've created the table and form, but
I can't get it to total only one row. It adds all hours of all
records (running
total) instead of isolating one record.

My table:
1-31 represent days of the month.
Rig # Month Year 1 2 31 Total hrs
11 Dec 2003 8 8 10 48
88 Dec 2003 10 12 12 49

I suggest a new table design

Likely you will want a list of rigs

RIGStbl

RigName
Dateplacedinservice
Serialnumber
OriginalCost
RIGIDNUMBER
etc.

Second table

RIGIDNUMBER
HoursUsed
Date

Now you have a way of computing what you want.

Note: Generally it is a poor idea to store the results of a calculation.
Rather you re-compute the results anytime you want to see them in a query,
form or report.

First and foremost, I need to know how to only get a total for rig 11
for days
1-31.
ie. Total hours worked is 48

The formula I used in the total hours worked field is
=Sum([1]+[2]+[3])..etc

Right now I get a running total of rig 11 and 88 in the total hours
worked field.
Ie. Total hours worked is 97 (48+49)

Thanks for all your help.

Keri

 

[Guest]

Maybe I'm missing something here but it sounds like u have ONE table and are
running this report directly off that table.

Why don't u have a table for each rig?

 

[keri]

here's the set up.

rig inventory table.
rig #
company name
company id
type
location
active

daily hours worked table.
entry#
rig#
Month (lookup)
year (lookup)
1
2
3
4
5
31
Total Hours Worked (1 rig for the entire month)

That was the original plan, but it may not work.
What would a table for each rig contain? This is an interesting idea.
I don't really care what it looks like, or how many other tables i have
to make, but I must have each rig give totals for the entire month.

Thanks
Keri

 

[Guest]

Looking at ur setup 2 things occure to me.

One I don't think ur are familiar with relationships between tables. (U
aren't by chance a spreadsheet user r u?)

Ur setup just begs for it. In fact, u have the beginnings already. Ur Rig#
appears twice. Assuming that is a unique number - there is ur link already.
Your 1st table has data on the rig, the second has data on hours worked, etc.
linked on that rig#. U can now make all kinds of magic reports about hrs.
days, combinations of the above, etc.

BTW, I notice a field for Company name. Ever need to do hrs worked for a
particular company? Linked tables reduce that to punching a button. If all
this is new to u then u will just have to get a book and learn.

Sorry, I have no magic here. Remind ur boss that is why they call it WORK!
--
Happy Learning
SailorMike

PS: Small point but handy. U don't have to make 88 tables. Make 2, rigs and
hours worked. And if u split the table u have already u may be half way there.

 

[Guest]

I just noticed a question on this thread about relationships. If u are not
familiar with this idea reading that exchange may help. Sounds similar to
what u r doing.

 

[keri]

The Rig # is the relationship between the two tables. There is only one
rig #. The inventory is the catalyst for all the other reports. The
report used to be in excel. There was one main sheet. It was divided by
type of rig. Each rig in each type had a total at the end of the month.
That total is the hours worked. Total hours worked / Possible hours rig
could work = %. ie 245/250=98%. That rig worked to 98% of it's
capacity.

All rigs total hours by division are added up. Then divided by totaled
possible hours. = That division's capacity or utilization. What ever
you choose to call it.
ie. Rig 8 245/250
Rig 9 145/250
Rig 5 200/250

total daylight rigs 590/750 = 79 % Daylight rigs in December worked
at 79%

After all rigs in every division are totaled this way you get the final
% by adding complete total hours worked by complete possible hours
worked = % of the month for the company. That first worksheet then gets
split up about 10 different ways which made it neccessary to get it out
of excel for accuracy reasons. Cutting and Pasting a thousand times
isn't my style. In peach tree, the time sheets are a subform and
calculate. Maybe I'm going in the wrong direction. ie. joe worked 8 hrs
mon, 8 hrs tues, 5 hrs wed.

job mon tues wed thurs fri sat sun total

21 8 8 8 0 0 0 0 24
33 5 5 5 5 5 25

joe worked 49 hours for
the week
joe worked on job 21 24
hours of the week
joe worked on job 33 25
hours of the week
Anyway, That's the dream.
Thanks
Keri

 

[keri]

Last night I finally found an example that worked.
My calculation is done in a query.
I'm so excited.

Thanks for helping
Keri