sum columns

[Saylindara]

I need to display days of the week in a subform and hours worked per day. I
then need to total the hours and perform calculations on the total, which I
also need to display. After trawling through these posts I concluded that the
best way would be to have each day as a field and the hours as a row. I then
created a query so that I could total the hours for the week. Only trouble is
I can't work out how to do that. I have tried [Day1]+[Day2] etc.,
sum([Day1]+[Day2]) and variations on the same theme, but I either get no
results or it tells me off for doing it wrong. Where am I going wrong?

 

[Ron2006]

The way that MGF described the solution is basically what you want to
do.

However.

The caviat is that this calculation will be off for two weeks out of
each year - The first week and the last week of the year.

Depending on how you want to accumulate the hours for that unique week
you may want to group by a calculated date.

If you want to total (for instance) the time for the last week of
December of 2008 as week one (of 2009) then you could create a
calculated date / week as follows (air code but you can see what I am
aiming at.)


SELECT DatePart("ww", date_column) + (7 - datepart("w",date_column))
As theWeek, SUM(hours_worked) As
TotalHours
FROM table_name
GROUP BY theWeek

Essentially you want to calculate a date that represents the
weekending date and group by that date.


Ron

 

[Saylindara]

Clever stuff, I shall keep that as I may need it. My needs were, I think,
simpler. I only have days of the week and hours worked.

I tried

Select [Day] As theWeek, SUM([hours]) As TotalHours FROM [EmployeeHours]
GROUP BY ([Day])

but it didn't work.

 

[Ron2006]

You will have to be more specific as to the exact structure of the
table you are summing on.

Show us a couple of records of example data so we can sort of see what
you are looking at.

(Hopefully [Day] is not the actual field name. That is a reserved word
and can get Access very confused if you are not very, very carefull.)

Ron

 

[Saylindara]

[EmployeeDay] [EmployeeHours]
Monday 8.0
Tuesday 0.0
Wednesday 4.0
Thursday 8.0
Friday 8.0

Total 28.0 is what I would like

 

[John W. Vinson]

I need to display days of the week in a subform and hours worked per day. I
then need to total the hours and perform calculations on the total, which I
also need to display. After trawling through these posts I concluded that the
best way would be to have each day as a field and the hours as a row. I then
created a query so that I could total the hours for the week. Only trouble is
I can't work out how to do that. I have tried [Day1]+[Day2] etc.,
sum([Day1]+[Day2]) and variations on the same theme, but I either get no
results or it tells me off for doing it wrong. Where am I going wrong?

If you have seven (or fewer) records displayed in a Subform, you can put a
textbox in the subform Footer with a control source

=Sum([EmployeeHours])

This sums the *table field*, not the control name, so if your field and
textbox are both named EmployeeHours you may need to change the name of the
textbox (say to txtEmployeeHours).

 

[Saylindara]

Subform footers don't display do they?

I need to display days of the week in a subform and hours worked per day. I
then need to total the hours and perform calculations on the total, which I
also need to display. After trawling through these posts I concluded that the
best way would be to have each day as a field and the hours as a row. I then
created a query so that I could total the hours for the week. Only trouble is
I can't work out how to do that. I have tried [Day1]+[Day2] etc.,
sum([Day1]+[Day2]) and variations on the same theme, but I either get no
results or it tells me off for doing it wrong. Where am I going wrong?

If you have seven (or fewer) records displayed in a Subform, you can put a
textbox in the subform Footer with a control source

=Sum([EmployeeHours])

This sums the *table field*, not the control name, so if your field and
textbox are both named EmployeeHours you may need to change the name of the
textbox (say to txtEmployeeHours).

 

[John W. Vinson]

Subform footers don't display do they?

Sure... if you use a Continuous Form view rather than a Datasheet. Continuous
is more flexible and controllable anyway, I rarely or never use datasheets.

 

[Saylindara]

My subforms are all on tabs. E.g for employees the jobs they do, the hours
they work, the courses they should do/have done/haven't done etc. Does that
mean I've set it up all wrong? I may have to hang myself.

 

[John W. Vinson]

My subforms are all on tabs. E.g for employees the jobs they do, the hours
they work, the courses they should do/have done/haven't done etc. Does that
mean I've set it up all wrong? I may have to hang myself.

Ummm?

You've done nothing wrong. It makes no difference whatsoever whether the
subforms are on tabs or not. The Forms you are using as subforms can all be in
either Single view (rather rare), Datasheet view (Microsoft's default), or
Continuous view (my preference).

 

[Saylindara]

Well, you live and learn. Panic over.
When I first started this database and posted I had to look up to see what
an MVP was. And there you all were - with pictures! Trouble is now I can
picture you shaking your head in disbelief, rolling your eyes, throwing your
arms in the air in horror, or head in hands 'more in sorrow than in anger'
etc.
Thanks again.
Merry Christmas.

 

[John W. Vinson]

Well, you live and learn. Panic over.

When I first started this database and posted I had to look up to see what
an MVP was. And there you all were - with pictures!

Well, some of us, but yeah... real live human beings (some folks post what
appear to be search terms for web bots, at least the first time).

Trouble is now I can
picture you shaking your head in disbelief, rolling your eyes, throwing your
arms in the air in horror, or head in hands 'more in sorrow than in anger'

LOL... certainly not in your case! For me it was more a sympathetic wince and
a quick reply to (well) assuage your panic.

etc.
Thanks again.
Merry Christmas.

A most merry Christmas to you as well!