Calculating in Queries

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.
--
Fendi Girl

hi Fendi,

Fendi Girl wrote:
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 --

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.
--
Dave Hargis, Microsoft Access MVP


"Fendi Girl" wrote:

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.
--
Fendi Girl

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


"Stefan Hoffmann" wrote:

hi Fendi,

Fendi Girl wrote:
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 --

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.
--
Fendi Girl


"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Fendi Girl" wrote:

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.
--
Fendi Girl

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

"Fendi Girl" wrote in message
...
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.
--
Fendi Girl


"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Fendi Girl" wrote:

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.
--
Fendi Girl

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
'================================================= ===


Jeff Boyce wrote:
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

"Fendi Girl" wrote in message
...
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.
--
Fendi Girl


"Klatuu" wrote:

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.
--
Dave Hargis, Microsoft Access MVP


"Fendi Girl" wrote:

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.
--
Fendi Girl