Calculation trouble

[Access_Newbie]

Hi!
I'm working on an access database(and I am very new at it)...It's viewed as
a Form, and it has a subform in it. My layout for the subform is:

ID/JobNumber/Jan/Feb/Mar/Apr/May/June/July/Aug/Sep/Oct/Nov/Dec

Underneath the months, are hours worked. I can't figure out how to add all
of the months hours up and then show that total on the form also.
I hope that makes sense.....thank you so much in advance!!!!

 

[Allen Browne]

You could add another text box after Dec, and set its Control Source to:
=Nz([Jan], 0) + Nz([Feb],0) + ...

The Nz() is needed to cope with an boxes that are null (left blank.)

We should probably mention that while the approach you are using makes
perfect sense for a spreadsheet, it is not the right way to design a
database. You really need to have multiple *records* in a related table,
instead of repeating fields (Jan, Feb, ...) in one table. It's one of the
fundamental rules of relational data design.

The correct design is much more powerfully queryable, such as being able to
query any period, compare periods, find differences between periods, and so
on.

 

[access_Newbie1]

Thank you for your help Allen!
So......would it be easier to make the subform look like this?.......

JOBNUMBER INSPECTIONS DUE HOURSWORKED
123 Jan 32.00
123 Mar 40.00
123 Dec 80.00
456 Feb 8.00
456 Nov 4.00

Because I did have it like that but I changed to a more "spreadsheet" look
because I thought that it would be easier to figure out the calculation total
for the hours. I just want, in 1 record, to show how many hours were worked
for the months used for Job number 123 and so on.

And then I want to figure out a formula to display on the main form.

You could add another text box after Dec, and set its Control Source to:
=Nz([Jan], 0) + Nz([Feb],0) + ...

The Nz() is needed to cope with an boxes that are null (left blank.)

We should probably mention that while the approach you are using makes
perfect sense for a spreadsheet, it is not the right way to design a
database. You really need to have multiple *records* in a related table,
instead of repeating fields (Jan, Feb, ...) in one table. It's one of the
fundamental rules of relational data design.

The correct design is much more powerfully queryable, such as being able to
query any period, compare periods, find differences between periods, and so
on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi!
I'm working on an access database(and I am very new at it)...It's viewed
as
a Form, and it has a subform in it. My layout for the subform is:

ID/JobNumber/Jan/Feb/Mar/Apr/May/June/July/Aug/Sep/Oct/Nov/Dec

Underneath the months, are hours worked. I can't figure out how to add all
of the months hours up and then show that total on the form also.
I hope that makes sense.....thank you so much in advance!!!!

 

[Allen Browne]

Yes, that's the right design.

1. Open the subform in design view.

2. If you don't see a Form Footer section at the bottom, click Form
Header/Footer on the View menu.

3. Add a text box to the Form Footer section, and set its ControlSource to:
=Sum([HoursWorked])

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thank you for your help Allen!
So......would it be easier to make the subform look like this?.......

JOBNUMBER INSPECTIONS DUE HOURSWORKED
123 Jan 32.00
123 Mar 40.00
123 Dec 80.00
456 Feb 8.00
456 Nov 4.00

Because I did have it like that but I changed to a more "spreadsheet" look
because I thought that it would be easier to figure out the calculation
total
for the hours. I just want, in 1 record, to show how many hours were
worked
for the months used for Job number 123 and so on.

And then I want to figure out a formula to display on the main form.

You could add another text box after Dec, and set its Control Source to:
=Nz([Jan], 0) + Nz([Feb],0) + ...

The Nz() is needed to cope with an boxes that are null (left blank.)

We should probably mention that while the approach you are using makes
perfect sense for a spreadsheet, it is not the right way to design a
database. You really need to have multiple *records* in a related table,
instead of repeating fields (Jan, Feb, ...) in one table. It's one of the
fundamental rules of relational data design.

The correct design is much more powerfully queryable, such as being able
to
query any period, compare periods, find differences between periods, and
so
on.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

message

Hi!
I'm working on an access database(and I am very new at it)...It's
viewed
as
a Form, and it has a subform in it. My layout for the subform is:

ID/JobNumber/Jan/Feb/Mar/Apr/May/June/July/Aug/Sep/Oct/Nov/Dec

Underneath the months, are hours worked. I can't figure out how to add
all
of the months hours up and then show that total on the form also.
I hope that makes sense.....thank you so much in advance!!!!