Calculating in Queries

[Fendi Girl]

Hi

How can I design a query that calculates hours for each project worked
trough the week (time sheets, like job no. 205 Mon 2hrs + Tue 7.5 hrs Wed 0+
Thu 0+ Fri 6hrs = Total15.5 hrs) and at the same time allows me inputing new
records.

 

[Stefan Hoffmann]

hi Fendi,

How can I design a query that calculates hours for each project worked
trough the week (time sheets, like job no. 205 Mon 2hrs + Tue 7.5 hrs Wed 0+
Thu 0+ Fri 6hrs = Total15.5 hrs) and at the same time allows me inputing new
records.

How to you store the data?

E.g.:

SELECT [job no.], Sum([TimeSpent]) AS ProjectTimeSpent
FROM [yourTable]
GROUP BY [job no.]



mfG
--> stefan <--

 

[Klatuu]

If you have a table that has a field for each day of the week, you are
already going in the wrong direction. Rather than one record with a field
for each day of the week, each day should be in a separate record with the
date and the value you need to capture.

Also, users should not be entering data directly in a table or a query, they
should be entering and view data in forms.

 

[Fendi Girl]

GROUP BY clause does not allow new record input
--
Fendi Girl

hi Fendi,

How can I design a query that calculates hours for each project worked
trough the week (time sheets, like job no. 205 Mon 2hrs + Tue 7.5 hrs Wed 0+
Thu 0+ Fri 6hrs = Total15.5 hrs) and at the same time allows me inputing new
records.

How to you store the data?

E.g.:

SELECT [job no.], Sum([TimeSpent]) AS ProjectTimeSpent
FROM [yourTable]
GROUP BY [job no.]



mfG
--> stefan <--

 

[Fendi Girl]

The purpose of the time sheet is to allow each employee to login and enter
they time sheet each week. They would be doing this in forms. Previous
developer created the time sheet so that they had a field for each day of the
week giving a total hours for each project and this is what they want to have
with the new databese.

 

[Jeff Boyce]

Dave is pointing out that how the data is stored (in tables) and how it is
presented to the users (in forms) does not have to match.

If your data isn't stored (in tables) in a structure Access understands
(i.e., relational, well-normalized), then both you and Access will have to
work much harder to do "simple" things.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

You can have a control on the form that will show the total as a
calculation. IF you are showing one person at a time you can use a
continuous form with a control in the form's header or footer to show
the total.

Another option is to use a work table to import an individual's data
into a non-normalized structure, work with this copy of the data, and
then "copy" the non-normalized data back into your normalized data
structure.

This takes a little bit of VBA code to transfer the data back and forth,
but it is not very difficult.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================