Summing time worked by an individual on a weekly basis

[CompostDave]

Hi, I'm building a database for a team of 28 people. The bulk of the form is
working fine based around dates and the adviser name. However I'm now trying
to build a query to show how many hours the guys have worked per week. At the
moment it works if only one person has input data for a week but as soon as
other data is added, the query counts that to. Can anyone suggest how to
calculate the hours worked per person without it counting for other people.

The query which calculates the time is:
SELECT tblDates.WeekNo, Sum(nz([TotalHours])) AS TotalWeekAdimTime,
Sum(nz([HoursTaken])) AS HolidayHoursTaken, Sum((nz([ToilHoursTaken]))) AS
TotalToilHoursTaken, Sum(nz([TotalDayTimeMins]/60)) AS TotalDayEventHours
FROM ((tblDates LEFT JOIN qryEventTime ON tblDates.Date = qryEventTime.Date)
LEFT JOIN tblHoliday ON tblDates.Date = tblHoliday.Date) LEFT JOIN
qryAdminTime ON tblDates.Date = qryAdminTime.Date
WHERE (((qryEventTime.AdviserName)=[Forms]![frmMenu]![Combo2])) OR
(((tblHoliday.AdviserName)=[Forms]![frmMenu]![Combo2])) OR
(((qryAdminTime.AdviserName)=[Forms]![frmMenu]![Combo2]))
GROUP BY tblDates.WeekNo;

Do I need to do this through a macro / crosstab query or is there a simple
way of doing it??

 

[kc-mass]

Hi,

In your group by clause you need to group by "AdviserName" from some field.

Regards

Kevin

 

[kc-mass]

Make that: In your GROUP BY clause you need to group by "AdviserName" from
some table
as well as WeekNo. That will give you the sum of hours by advisor by week.

Regards

Kevin

Hi,

In your group by clause you need to group by "AdviserName" from some
field.

Regards

Kevin

Hi, I'm building a database for a team of 28 people. The bulk of the form
is
working fine based around dates and the adviser name. However I'm now
trying
to build a query to show how many hours the guys have worked per week. At
the
moment it works if only one person has input data for a week but as soon
as
other data is added, the query counts that to. Can anyone suggest how to
calculate the hours worked per person without it counting for other
people.

The query which calculates the time is:
SELECT tblDates.WeekNo, Sum(nz([TotalHours])) AS TotalWeekAdimTime,
Sum(nz([HoursTaken])) AS HolidayHoursTaken, Sum((nz([ToilHoursTaken])))
AS
TotalToilHoursTaken, Sum(nz([TotalDayTimeMins]/60)) AS TotalDayEventHours
FROM ((tblDates LEFT JOIN qryEventTime ON tblDates.Date =
qryEventTime.Date)
LEFT JOIN tblHoliday ON tblDates.Date = tblHoliday.Date) LEFT JOIN
qryAdminTime ON tblDates.Date = qryAdminTime.Date
WHERE (((qryEventTime.AdviserName)=[Forms]![frmMenu]![Combo2])) OR
(((tblHoliday.AdviserName)=[Forms]![frmMenu]![Combo2])) OR
(((qryAdminTime.AdviserName)=[Forms]![frmMenu]![Combo2]))
GROUP BY tblDates.WeekNo;

Do I need to do this through a macro / crosstab query or is there a
simple
way of doing it??

 

[compostdave]

Thanks Kevin,

I've tried that but it just puts three columns of "AdviserName" in - there
is a separate table with the advisers on ("tblAdviserName") however when I
try and join this to the query it tells me there are "Ambiguous outer joins"
- to get the database to work it has had to be built around dates rather than
each adviser (this query has "tblDates" as its core.

Is there a way I can get it to match the names from the 3 columns and group
together whilst summing the hours worked for that week?

Thanks
Dave

Make that: In your GROUP BY clause you need to group by "AdviserName" from
some table
as well as WeekNo. That will give you the sum of hours by advisor by week.

Regards

Kevin

Hi,

In your group by clause you need to group by "AdviserName" from some
field.

Regards

Kevin

Hi, I'm building a database for a team of 28 people. The bulk of the form
is
working fine based around dates and the adviser name. However I'm now
trying
to build a query to show how many hours the guys have worked per week. At
the
moment it works if only one person has input data for a week but as soon
as
other data is added, the query counts that to. Can anyone suggest how to
calculate the hours worked per person without it counting for other
people.

The query which calculates the time is:
SELECT tblDates.WeekNo, Sum(nz([TotalHours])) AS TotalWeekAdimTime,
Sum(nz([HoursTaken])) AS HolidayHoursTaken, Sum((nz([ToilHoursTaken])))
AS
TotalToilHoursTaken, Sum(nz([TotalDayTimeMins]/60)) AS TotalDayEventHours
FROM ((tblDates LEFT JOIN qryEventTime ON tblDates.Date =
qryEventTime.Date)
LEFT JOIN tblHoliday ON tblDates.Date = tblHoliday.Date) LEFT JOIN
qryAdminTime ON tblDates.Date = qryAdminTime.Date
WHERE (((qryEventTime.AdviserName)=[Forms]![frmMenu]![Combo2])) OR
(((tblHoliday.AdviserName)=[Forms]![frmMenu]![Combo2])) OR
(((qryAdminTime.AdviserName)=[Forms]![frmMenu]![Combo2]))
GROUP BY tblDates.WeekNo;

Do I need to do this through a macro / crosstab query or is there a
simple
way of doing it??

 

[kc-mass]

Maybe you should show the structure of the tables you are working with. At
the moment you have a combination of queries and tables with undefined
content.

Regards

Kevin

Thanks Kevin,

I've tried that but it just puts three columns of "AdviserName" in - there
is a separate table with the advisers on ("tblAdviserName") however when I
try and join this to the query it tells me there are "Ambiguous outer
joins"
- to get the database to work it has had to be built around dates rather
than
each adviser (this query has "tblDates" as its core.

Is there a way I can get it to match the names from the 3 columns and
group
together whilst summing the hours worked for that week?

Thanks
Dave

Make that: In your GROUP BY clause you need to group by "AdviserName"
from
some table
as well as WeekNo. That will give you the sum of hours by advisor by
week.

Regards

Kevin

Hi,

In your group by clause you need to group by "AdviserName" from some
field.

Regards

Kevin


Hi, I'm building a database for a team of 28 people. The bulk of the
form
is
working fine based around dates and the adviser name. However I'm now
trying
to build a query to show how many hours the guys have worked per week.
At
the
moment it works if only one person has input data for a week but as
soon
as
other data is added, the query counts that to. Can anyone suggest how
to
calculate the hours worked per person without it counting for other
people.

The query which calculates the time is:
SELECT tblDates.WeekNo, Sum(nz([TotalHours])) AS TotalWeekAdimTime,
Sum(nz([HoursTaken])) AS HolidayHoursTaken,
Sum((nz([ToilHoursTaken])))
AS
TotalToilHoursTaken, Sum(nz([TotalDayTimeMins]/60)) AS
TotalDayEventHours
FROM ((tblDates LEFT JOIN qryEventTime ON tblDates.Date =
qryEventTime.Date)
LEFT JOIN tblHoliday ON tblDates.Date = tblHoliday.Date) LEFT JOIN
qryAdminTime ON tblDates.Date = qryAdminTime.Date
WHERE (((qryEventTime.AdviserName)=[Forms]![frmMenu]![Combo2])) OR
(((tblHoliday.AdviserName)=[Forms]![frmMenu]![Combo2])) OR
(((qryAdminTime.AdviserName)=[Forms]![frmMenu]![Combo2]))
GROUP BY tblDates.WeekNo;

Do I need to do this through a macro / crosstab query or is there a
simple
way of doing it??

 

[Ken Snell [MVP]]

Additionally, show some sample data from the tables and show how you want
those results to be summed and displayed by your query.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Maybe you should show the structure of the tables you are working with.
At the moment you have a combination of queries and tables with undefined
content.

Regards

Kevin

Thanks Kevin,

I've tried that but it just puts three columns of "AdviserName" in -
there
is a separate table with the advisers on ("tblAdviserName") however when
I
try and join this to the query it tells me there are "Ambiguous outer
joins"
- to get the database to work it has had to be built around dates rather
than
each adviser (this query has "tblDates" as its core.

Is there a way I can get it to match the names from the 3 columns and
group
together whilst summing the hours worked for that week?

Thanks
Dave

Make that: In your GROUP BY clause you need to group by "AdviserName"
from
some table
as well as WeekNo. That will give you the sum of hours by advisor by
week.

Regards

Kevin



"kc-mass" <connearney_AT_comcast_DOT_net> wrote in message
Hi,

In your group by clause you need to group by "AdviserName" from some
field.

Regards

Kevin


Hi, I'm building a database for a team of 28 people. The bulk of the
form
is
working fine based around dates and the adviser name. However I'm now
trying
to build a query to show how many hours the guys have worked per
week. At
the
moment it works if only one person has input data for a week but as
soon
as
other data is added, the query counts that to. Can anyone suggest how
to
calculate the hours worked per person without it counting for other
people.

The query which calculates the time is:
SELECT tblDates.WeekNo, Sum(nz([TotalHours])) AS TotalWeekAdimTime,
Sum(nz([HoursTaken])) AS HolidayHoursTaken,
Sum((nz([ToilHoursTaken])))
AS
TotalToilHoursTaken, Sum(nz([TotalDayTimeMins]/60)) AS
TotalDayEventHours
FROM ((tblDates LEFT JOIN qryEventTime ON tblDates.Date =
qryEventTime.Date)
LEFT JOIN tblHoliday ON tblDates.Date = tblHoliday.Date) LEFT JOIN
qryAdminTime ON tblDates.Date = qryAdminTime.Date
WHERE (((qryEventTime.AdviserName)=[Forms]![frmMenu]![Combo2])) OR
(((tblHoliday.AdviserName)=[Forms]![frmMenu]![Combo2])) OR
(((qryAdminTime.AdviserName)=[Forms]![frmMenu]![Combo2]))
GROUP BY tblDates.WeekNo;

Do I need to do this through a macro / crosstab query or is there a
simple
way of doing it??

 

[compostdave]

Thanks both,

The basic structure works as follows:

3 data collection tables - Admin Time, Event Time and Holiday Taken.

These tables are linked by "tbl dates" - which allocates dates to week
numbers and "tblAdvisers" which holds details about each person (Name, team,
are covered).

There are 4 forms which make up the user interface - Menu (where the adviser
selects their name and the week number they are subitting for) and then three
further forms which populate the Admin, Event and Holiday tables.

On top of these tables are queries to calculate the totals for each activity
(e.g.. how long did they spend working at an event, how much time have they
spen on each admin task).

THe data for the Events table looks like this

Column headings
Event ID Adviser Name Date Leave Home Arrive Event Lunch Start Lunch
End Leave Event Arrive Home Location Event Type Numbers Event
Feedback MediaType
MediaAudience ArrangedBy Local Authority Area
Row values
27 Julia Pollard Mon
20/07/09 00:00 00:00 00:00 00:00 00:00 00:00 hereish Corporate 100 Moderate Radio Interview 10 Agency Arranged cabbage

At the moment I have a query which calculates how much time has been spent
on each activity and then sums this to give a total week time. The problem
occurs when I enter the data for more than one adviser - the query then adds
their data into the totals. This is what I'm trying to stop so it just
calculates per adviser.

The code for the weekly summary is

SELECT qryTotalWeekEventTime.WeekNo,
Sum((Nz([TotalWeekAdimTime]+[HolidayHoursTaken]+[TotalDayEventHours]+[TotalToilHoursTaken])))
AS TotalWeekHours, qryTotalWeekEventTime.TotalWeekAdimTime,
qryTotalWeekEventTime.HolidayHoursTaken,
qryTotalWeekEventTime.TotalDayEventHours,
qryTotalWeekEventTime.TotalToilHoursTaken
FROM qryTotalWeekEventTime
GROUP BY qryTotalWeekEventTime.WeekNo,
qryTotalWeekEventTime.TotalWeekAdimTime,
qryTotalWeekEventTime.HolidayHoursTaken,
qryTotalWeekEventTime.TotalDayEventHours,
qryTotalWeekEventTime.TotalToilHoursTaken
HAVING (((qryTotalWeekEventTime.WeekNo)=[Forms]![frmMenu]![WeekNo]));

There is no reference to advisers in this query as I haven't been able to
pull it in from the query which calculates the time per activity (included in
my first post) - would this solve te problem, and if so how do I do it.

Thanks
Dave

Additionally, show some sample data from the tables and show how you want
those results to be summed and displayed by your query.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Maybe you should show the structure of the tables you are working with.
At the moment you have a combination of queries and tables with undefined
content.

Regards

Kevin

Thanks Kevin,

I've tried that but it just puts three columns of "AdviserName" in -
there
is a separate table with the advisers on ("tblAdviserName") however when
I
try and join this to the query it tells me there are "Ambiguous outer
joins"
- to get the database to work it has had to be built around dates rather
than
each adviser (this query has "tblDates" as its core.

Is there a way I can get it to match the names from the 3 columns and
group
together whilst summing the hours worked for that week?

Thanks
Dave

:

Make that: In your GROUP BY clause you need to group by "AdviserName"
from
some table
as well as WeekNo. That will give you the sum of hours by advisor by
week.

Regards

Kevin



"kc-mass" <connearney_AT_comcast_DOT_net> wrote in message
Hi,

In your group by clause you need to group by "AdviserName" from some
field.

Regards

Kevin


Hi, I'm building a database for a team of 28 people. The bulk of the
form
is
working fine based around dates and the adviser name. However I'm now
trying
to build a query to show how many hours the guys have worked per
week. At
the
moment it works if only one person has input data for a week but as
soon
as
other data is added, the query counts that to. Can anyone suggest how
to
calculate the hours worked per person without it counting for other
people.

The query which calculates the time is:
SELECT tblDates.WeekNo, Sum(nz([TotalHours])) AS TotalWeekAdimTime,
Sum(nz([HoursTaken])) AS HolidayHoursTaken,
Sum((nz([ToilHoursTaken])))
AS
TotalToilHoursTaken, Sum(nz([TotalDayTimeMins]/60)) AS
TotalDayEventHours
FROM ((tblDates LEFT JOIN qryEventTime ON tblDates.Date =
qryEventTime.Date)
LEFT JOIN tblHoliday ON tblDates.Date = tblHoliday.Date) LEFT JOIN
qryAdminTime ON tblDates.Date = qryAdminTime.Date
WHERE (((qryEventTime.AdviserName)=[Forms]![frmMenu]![Combo2])) OR
(((tblHoliday.AdviserName)=[Forms]![frmMenu]![Combo2])) OR
(((qryAdminTime.AdviserName)=[Forms]![frmMenu]![Combo2]))
GROUP BY tblDates.WeekNo;

Do I need to do this through a macro / crosstab query or is there a
simple
way of doing it??

 

[compostdave]

Thanks for your help with this, I've worked out how to get the totals without
including records for other people - I've put in an extra layer of queries
which pull out the data for each adviser from each table and then adds it up.
However, this has now caused a new small issue, in order to get the query to
work its requiring data to be input on each form. I've set all the default
values to 0 and tried to add NZ to the query but it still doesn't seem to
work. As this timesheet is supposed to be easy for everyone to use, is there
a way to update the query so it doesn't need all 3 forms completed (afterall
the guys don't take holiday every week - even if it seems like it!). The SQL
for the working query is......

SELECT tblAdvisers.AdviserName,
Sum(Nz([TotalDayEventHours]+[SumOfHoursTaken]+[SumOfToilHoursTaken]+[SumOfTotalHours]))
AS TotalWeekTime, qryAdminTimePerAdviser.SumOfTotalHours,
qryAdvisersHolidaySummary.SumOfHoursTaken,
qryEventTimeByAdviser.TotalDayEventHours,
qryAdvisersHolidaySummary.SumOfToilHoursTaken
FROM ((tblAdvisers INNER JOIN qryAdvisersHolidaySummary ON
tblAdvisers.AdviserName = qryAdvisersHolidaySummary.AdviserName) INNER JOIN
qryEventTimeByAdviser ON tblAdvisers.AdviserName =
qryEventTimeByAdviser.AdviserName) INNER JOIN qryAdminTimePerAdviser ON
tblAdvisers.AdviserName = qryAdminTimePerAdviser.AdviserName
WHERE (((qryAdvisersHolidaySummary.WeekNo)=[Forms]![frmMenu]![WeekNo]) AND
((qryEventTimeByAdviser.WeekNo)=[Forms]![frmMenu]![WeekNo]))
GROUP BY tblAdvisers.AdviserName, qryAdminTimePerAdviser.SumOfTotalHours,
qryAdvisersHolidaySummary.SumOfHoursTaken,
qryEventTimeByAdviser.TotalDayEventHours,
qryAdvisersHolidaySummary.SumOfToilHoursTaken
HAVING (((tblAdvisers.AdviserName)=[Forms]![frmMenu]![Combo2]));


Thanks again
Dave

Thanks both,

The basic structure works as follows:

3 data collection tables - Admin Time, Event Time and Holiday Taken.

These tables are linked by "tbl dates" - which allocates dates to week
numbers and "tblAdvisers" which holds details about each person (Name, team,
are covered).

There are 4 forms which make up the user interface - Menu (where the adviser
selects their name and the week number they are subitting for) and then three
further forms which populate the Admin, Event and Holiday tables.

On top of these tables are queries to calculate the totals for each activity
(e.g.. how long did they spend working at an event, how much time have they
spen on each admin task).

THe data for the Events table looks like this

Column headings
Event ID Adviser Name Date Leave Home Arrive Event Lunch Start Lunch
End Leave Event Arrive Home Location Event Type Numbers Event
Feedback MediaType
MediaAudience ArrangedBy Local Authority Area
Row values
27 Julia Pollard Mon
20/07/09 00:00 00:00 00:00 00:00 00:00 00:00 hereish Corporate 100 Moderate Radio Interview 10 Agency Arranged cabbage

At the moment I have a query which calculates how much time has been spent
on each activity and then sums this to give a total week time. The problem
occurs when I enter the data for more than one adviser - the query then adds
their data into the totals. This is what I'm trying to stop so it just
calculates per adviser.

The code for the weekly summary is

SELECT qryTotalWeekEventTime.WeekNo,
Sum((Nz([TotalWeekAdimTime]+[HolidayHoursTaken]+[TotalDayEventHours]+[TotalToilHoursTaken])))
AS TotalWeekHours, qryTotalWeekEventTime.TotalWeekAdimTime,
qryTotalWeekEventTime.HolidayHoursTaken,
qryTotalWeekEventTime.TotalDayEventHours,
qryTotalWeekEventTime.TotalToilHoursTaken
FROM qryTotalWeekEventTime
GROUP BY qryTotalWeekEventTime.WeekNo,
qryTotalWeekEventTime.TotalWeekAdimTime,
qryTotalWeekEventTime.HolidayHoursTaken,
qryTotalWeekEventTime.TotalDayEventHours,
qryTotalWeekEventTime.TotalToilHoursTaken
HAVING (((qryTotalWeekEventTime.WeekNo)=[Forms]![frmMenu]![WeekNo]));

There is no reference to advisers in this query as I haven't been able to
pull it in from the query which calculates the time per activity (included in
my first post) - would this solve te problem, and if so how do I do it.

Thanks
Dave

Additionally, show some sample data from the tables and show how you want
those results to be summed and displayed by your query.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Maybe you should show the structure of the tables you are working with.
At the moment you have a combination of queries and tables with undefined
content.

Regards

Kevin


Thanks Kevin,

I've tried that but it just puts three columns of "AdviserName" in -
there
is a separate table with the advisers on ("tblAdviserName") however when
I
try and join this to the query it tells me there are "Ambiguous outer
joins"
- to get the database to work it has had to be built around dates rather
than
each adviser (this query has "tblDates" as its core.

Is there a way I can get it to match the names from the 3 columns and
group
together whilst summing the hours worked for that week?

Thanks
Dave

:

Make that: In your GROUP BY clause you need to group by "AdviserName"
from
some table
as well as WeekNo. That will give you the sum of hours by advisor by
week.

Regards

Kevin



"kc-mass" <connearney_AT_comcast_DOT_net> wrote in message
Hi,

In your group by clause you need to group by "AdviserName" from some
field.

Regards

Kevin


Hi, I'm building a database for a team of 28 people. The bulk of the
form
is
working fine based around dates and the adviser name. However I'm now
trying
to build a query to show how many hours the guys have worked per
week. At
the
moment it works if only one person has input data for a week but as
soon
as
other data is added, the query counts that to. Can anyone suggest how
to
calculate the hours worked per person without it counting for other
people.

The query which calculates the time is:
SELECT tblDates.WeekNo, Sum(nz([TotalHours])) AS TotalWeekAdimTime,
Sum(nz([HoursTaken])) AS HolidayHoursTaken,
Sum((nz([ToilHoursTaken])))
AS
TotalToilHoursTaken, Sum(nz([TotalDayTimeMins]/60)) AS
TotalDayEventHours
FROM ((tblDates LEFT JOIN qryEventTime ON tblDates.Date =
qryEventTime.Date)
LEFT JOIN tblHoliday ON tblDates.Date = tblHoliday.Date) LEFT JOIN
qryAdminTime ON tblDates.Date = qryAdminTime.Date
WHERE (((qryEventTime.AdviserName)=[Forms]![frmMenu]![Combo2])) OR
(((tblHoliday.AdviserName)=[Forms]![frmMenu]![Combo2])) OR
(((qryAdminTime.AdviserName)=[Forms]![frmMenu]![Combo2]))
GROUP BY tblDates.WeekNo;

Do I need to do this through a macro / crosstab query or is there a
simple
way of doing it??

 

[kc-mass]

Hi Dave
First get rid of most of the group by clauses. My recollection is that you
want to report by week and advisor only. Those should be the only group by
phrases.

Second, and I know you do not want to hear this - but, your data structure
and thus form usage is a problem here. Hours of all types ( toil, holiday,
admin, other) all belong in the same table with a designator field that
indicates what type of hours they are. That way you can have reports that
easily sum on week, advisor, type of hours, etc.

People filling in the form, probably a continuous form, should be able to
enter 8:00 to 12:00 as holiday and 12:00 to 16:00 as toil and mark each
segment appropriately.

Regards

Kevin

Thanks for your help with this, I've worked out how to get the totals
without
including records for other people - I've put in an extra layer of queries
which pull out the data for each adviser from each table and then adds it
up.
However, this has now caused a new small issue, in order to get the query
to
work its requiring data to be input on each form. I've set all the default
values to 0 and tried to add NZ to the query but it still doesn't seem to
work. As this timesheet is supposed to be easy for everyone to use, is
there
a way to update the query so it doesn't need all 3 forms completed
(afterall
the guys don't take holiday every week - even if it seems like it!). The
SQL
for the working query is......

SELECT tblAdvisers.AdviserName,
Sum(Nz([TotalDayEventHours]+[SumOfHoursTaken]+[SumOfToilHoursTaken]+[SumOfTotalHours]))
AS TotalWeekTime, qryAdminTimePerAdviser.SumOfTotalHours,
qryAdvisersHolidaySummary.SumOfHoursTaken,
qryEventTimeByAdviser.TotalDayEventHours,
qryAdvisersHolidaySummary.SumOfToilHoursTaken
FROM ((tblAdvisers INNER JOIN qryAdvisersHolidaySummary ON
tblAdvisers.AdviserName = qryAdvisersHolidaySummary.AdviserName) INNER
JOIN
qryEventTimeByAdviser ON tblAdvisers.AdviserName =
qryEventTimeByAdviser.AdviserName) INNER JOIN qryAdminTimePerAdviser ON
tblAdvisers.AdviserName = qryAdminTimePerAdviser.AdviserName
WHERE (((qryAdvisersHolidaySummary.WeekNo)=[Forms]![frmMenu]![WeekNo]) AND
((qryEventTimeByAdviser.WeekNo)=[Forms]![frmMenu]![WeekNo]))
GROUP BY tblAdvisers.AdviserName, qryAdminTimePerAdviser.SumOfTotalHours,
qryAdvisersHolidaySummary.SumOfHoursTaken,
qryEventTimeByAdviser.TotalDayEventHours,
qryAdvisersHolidaySummary.SumOfToilHoursTaken
HAVING (((tblAdvisers.AdviserName)=[Forms]![frmMenu]![Combo2]));


Thanks again
Dave

Thanks both,

The basic structure works as follows:

3 data collection tables - Admin Time, Event Time and Holiday Taken.

These tables are linked by "tbl dates" - which allocates dates to week
numbers and "tblAdvisers" which holds details about each person (Name,
team,
are covered).

There are 4 forms which make up the user interface - Menu (where the
adviser
selects their name and the week number they are subitting for) and then
three
further forms which populate the Admin, Event and Holiday tables.

On top of these tables are queries to calculate the totals for each
activity
(e.g.. how long did they spend working at an event, how much time have
they
spen on each admin task).

THe data for the Events table looks like this

Column headings
Event ID Adviser Name Date Leave Home Arrive Event Lunch Start Lunch
End Leave Event Arrive Home Location Event Type Numbers Event
Feedback MediaType
MediaAudience ArrangedBy Local Authority Area
Row values
27 Julia Pollard Mon
20/07/09 00:00 00:00 00:00 00:00 00:00 00:00 hereish Corporate 100
Moderate Radio Interview 10 Agency Arranged cabbage

At the moment I have a query which calculates how much time has been
spent
on each activity and then sums this to give a total week time. The
problem
occurs when I enter the data for more than one adviser - the query then
adds
their data into the totals. This is what I'm trying to stop so it just
calculates per adviser.

The code for the weekly summary is

SELECT qryTotalWeekEventTime.WeekNo,
Sum((Nz([TotalWeekAdimTime]+[HolidayHoursTaken]+[TotalDayEventHours]+[TotalToilHoursTaken])))
AS TotalWeekHours, qryTotalWeekEventTime.TotalWeekAdimTime,
qryTotalWeekEventTime.HolidayHoursTaken,
qryTotalWeekEventTime.TotalDayEventHours,
qryTotalWeekEventTime.TotalToilHoursTaken
FROM qryTotalWeekEventTime
GROUP BY qryTotalWeekEventTime.WeekNo,
qryTotalWeekEventTime.TotalWeekAdimTime,
qryTotalWeekEventTime.HolidayHoursTaken,
qryTotalWeekEventTime.TotalDayEventHours,
qryTotalWeekEventTime.TotalToilHoursTaken
HAVING (((qryTotalWeekEventTime.WeekNo)=[Forms]![frmMenu]![WeekNo]));

There is no reference to advisers in this query as I haven't been able to
pull it in from the query which calculates the time per activity
(included in
my first post) - would this solve te problem, and if so how do I do it.

Thanks
Dave

Additionally, show some sample data from the tables and show how you
want
those results to be summed and displayed by your query.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"kc-mass" <connearney_AT_comcast_DOT_net> wrote in message
Maybe you should show the structure of the tables you are working
with.
At the moment you have a combination of queries and tables with
undefined
content.

Regards

Kevin


message
Thanks Kevin,

I've tried that but it just puts three columns of "AdviserName" in -
there
is a separate table with the advisers on ("tblAdviserName") however
when
I
try and join this to the query it tells me there are "Ambiguous
outer
joins"
- to get the database to work it has had to be built around dates
rather
than
each adviser (this query has "tblDates" as its core.

Is there a way I can get it to match the names from the 3 columns
and
group
together whilst summing the hours worked for that week?

Thanks
Dave

:

Make that: In your GROUP BY clause you need to group by
"AdviserName"
from
some table
as well as WeekNo. That will give you the sum of hours by advisor
by
week.

Regards

Kevin



"kc-mass" <connearney_AT_comcast_DOT_net> wrote in message
Hi,

In your group by clause you need to group by "AdviserName" from
some
field.

Regards

Kevin


message
Hi, I'm building a database for a team of 28 people. The bulk of
the
form
is
working fine based around dates and the adviser name. However
I'm now
trying
to build a query to show how many hours the guys have worked per
week. At
the
moment it works if only one person has input data for a week but
as
soon
as
other data is added, the query counts that to. Can anyone
suggest how
to
calculate the hours worked per person without it counting for
other
people.

The query which calculates the time is:
SELECT tblDates.WeekNo, Sum(nz([TotalHours])) AS
TotalWeekAdimTime,
Sum(nz([HoursTaken])) AS HolidayHoursTaken,
Sum((nz([ToilHoursTaken])))
AS
TotalToilHoursTaken, Sum(nz([TotalDayTimeMins]/60)) AS
TotalDayEventHours
FROM ((tblDates LEFT JOIN qryEventTime ON tblDates.Date =
qryEventTime.Date)
LEFT JOIN tblHoliday ON tblDates.Date = tblHoliday.Date) LEFT
JOIN
qryAdminTime ON tblDates.Date = qryAdminTime.Date
WHERE (((qryEventTime.AdviserName)=[Forms]![frmMenu]![Combo2]))
OR
(((tblHoliday.AdviserName)=[Forms]![frmMenu]![Combo2])) OR
(((qryAdminTime.AdviserName)=[Forms]![frmMenu]![Combo2]))
GROUP BY tblDates.WeekNo;

Do I need to do this through a macro / crosstab query or is
there a
simple
way of doing it??

 

[compostdave]

Thanks Kevin,

If I used a single table as you suggest, can I still collect the comments
which go with what the guys are up to?

To give you a picture of how the database works at the moment;
The data entry form is split into 3 sections (which are how we define the
bulk of activities undertaken by the team) - Admin, Holidays and Events. The
admin is pretty simple, start and finish time and then they select from a
list what activity they did in that time (expenses for example). Holiday is
fairly self explanatory but they count it in hours hence the input of date
and hours taken (we're trying to keep it as simple as possible). The event
form is more complicated as it requires them to input travel times (leave
home, arrive event and reversed), lunch time (Start and end - not included in
their working week so is subtracted from day hours but has to be recorded as
we have a duty to make sure they are taking breaks). It then records
location, numbers spoken to, general assesment of the event, who arranged it
and then asks for the focus of the event and a summation of comments they
received - the team work on a variety of projects so we collect comments on
each one.

Just to make it that bit more exciting, the team are spread around the UK so
the final program has to be simple to use.

If I were to use a more centralised structure for the hours, is it still
possible to collate all the info required? and would it require a substantial
rebuild of what I've already got to do it?

Please bear in mind I'm a newcomer to Access so am building this much by
trial and error!

Thanks again for your help.
Dave

Hi Dave
First get rid of most of the group by clauses. My recollection is that you
want to report by week and advisor only. Those should be the only group by
phrases.

Second, and I know you do not want to hear this - but, your data structure
and thus form usage is a problem here. Hours of all types ( toil, holiday,
admin, other) all belong in the same table with a designator field that
indicates what type of hours they are. That way you can have reports that
easily sum on week, advisor, type of hours, etc.

People filling in the form, probably a continuous form, should be able to
enter 8:00 to 12:00 as holiday and 12:00 to 16:00 as toil and mark each
segment appropriately.

Regards

Kevin

Thanks for your help with this, I've worked out how to get the totals
without
including records for other people - I've put in an extra layer of queries
which pull out the data for each adviser from each table and then adds it
up.
However, this has now caused a new small issue, in order to get the query
to
work its requiring data to be input on each form. I've set all the default
values to 0 and tried to add NZ to the query but it still doesn't seem to
work. As this timesheet is supposed to be easy for everyone to use, is
there
a way to update the query so it doesn't need all 3 forms completed
(afterall
the guys don't take holiday every week - even if it seems like it!). The
SQL
for the working query is......

SELECT tblAdvisers.AdviserName,
Sum(Nz([TotalDayEventHours]+[SumOfHoursTaken]+[SumOfToilHoursTaken]+[SumOfTotalHours]))
AS TotalWeekTime, qryAdminTimePerAdviser.SumOfTotalHours,
qryAdvisersHolidaySummary.SumOfHoursTaken,
qryEventTimeByAdviser.TotalDayEventHours,
qryAdvisersHolidaySummary.SumOfToilHoursTaken
FROM ((tblAdvisers INNER JOIN qryAdvisersHolidaySummary ON
tblAdvisers.AdviserName = qryAdvisersHolidaySummary.AdviserName) INNER
JOIN
qryEventTimeByAdviser ON tblAdvisers.AdviserName =
qryEventTimeByAdviser.AdviserName) INNER JOIN qryAdminTimePerAdviser ON
tblAdvisers.AdviserName = qryAdminTimePerAdviser.AdviserName
WHERE (((qryAdvisersHolidaySummary.WeekNo)=[Forms]![frmMenu]![WeekNo]) AND
((qryEventTimeByAdviser.WeekNo)=[Forms]![frmMenu]![WeekNo]))
GROUP BY tblAdvisers.AdviserName, qryAdminTimePerAdviser.SumOfTotalHours,
qryAdvisersHolidaySummary.SumOfHoursTaken,
qryEventTimeByAdviser.TotalDayEventHours,
qryAdvisersHolidaySummary.SumOfToilHoursTaken
HAVING (((tblAdvisers.AdviserName)=[Forms]![frmMenu]![Combo2]));


Thanks again
Dave

Thanks both,

The basic structure works as follows:

3 data collection tables - Admin Time, Event Time and Holiday Taken.

These tables are linked by "tbl dates" - which allocates dates to week
numbers and "tblAdvisers" which holds details about each person (Name,
team,
are covered).

There are 4 forms which make up the user interface - Menu (where the
adviser
selects their name and the week number they are subitting for) and then
three
further forms which populate the Admin, Event and Holiday tables.

On top of these tables are queries to calculate the totals for each
activity
(e.g.. how long did they spend working at an event, how much time have
they
spen on each admin task).

THe data for the Events table looks like this

Column headings
Event ID Adviser Name Date Leave Home Arrive Event Lunch Start Lunch
End Leave Event Arrive Home Location Event Type Numbers Event
Feedback MediaType
MediaAudience ArrangedBy Local Authority Area
Row values
27 Julia Pollard Mon
20/07/09 00:00 00:00 00:00 00:00 00:00 00:00 hereish Corporate 100
Moderate Radio Interview 10 Agency Arranged cabbage

At the moment I have a query which calculates how much time has been
spent
on each activity and then sums this to give a total week time. The
problem
occurs when I enter the data for more than one adviser - the query then
adds
their data into the totals. This is what I'm trying to stop so it just
calculates per adviser.

The code for the weekly summary is

SELECT qryTotalWeekEventTime.WeekNo,
Sum((Nz([TotalWeekAdimTime]+[HolidayHoursTaken]+[TotalDayEventHours]+[TotalToilHoursTaken])))
AS TotalWeekHours, qryTotalWeekEventTime.TotalWeekAdimTime,
qryTotalWeekEventTime.HolidayHoursTaken,
qryTotalWeekEventTime.TotalDayEventHours,
qryTotalWeekEventTime.TotalToilHoursTaken
FROM qryTotalWeekEventTime
GROUP BY qryTotalWeekEventTime.WeekNo,
qryTotalWeekEventTime.TotalWeekAdimTime,
qryTotalWeekEventTime.HolidayHoursTaken,
qryTotalWeekEventTime.TotalDayEventHours,
qryTotalWeekEventTime.TotalToilHoursTaken
HAVING (((qryTotalWeekEventTime.WeekNo)=[Forms]![frmMenu]![WeekNo]));

There is no reference to advisers in this query as I haven't been able to
pull it in from the query which calculates the time per activity
(included in
my first post) - would this solve te problem, and if so how do I do it.

Thanks
Dave

:

Additionally, show some sample data from the tables and show how you
want
those results to be summed and displayed by your query.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"kc-mass" <connearney_AT_comcast_DOT_net> wrote in message
Maybe you should show the structure of the tables you are working
with.
At the moment you have a combination of queries and tables with
undefined
content.

Regards

Kevin


message
Thanks Kevin,

I've tried that but it just puts three columns of "AdviserName" in -
there
is a separate table with the advisers on ("tblAdviserName") however
when
I
try and join this to the query it tells me there are "Ambiguous
outer
joins"
- to get the database to work it has had to be built around dates
rather
than
each adviser (this query has "tblDates" as its core.

Is there a way I can get it to match the names from the 3 columns
and
group
together whilst summing the hours worked for that week?

Thanks
Dave

:

Make that: In your GROUP BY clause you need to group by
"AdviserName"
from
some table
as well as WeekNo. That will give you the sum of hours by advisor
by
week.

Regards

Kevin



"kc-mass" <connearney_AT_comcast_DOT_net> wrote in message
Hi,

In your group by clause you need to group by "AdviserName" from
some
field.

Regards

Kevin


message
Hi, I'm building a database for a team of 28 people. The bulk of
the
form
is
working fine based around dates and the adviser name. However
I'm now
trying
to build a query to show how many hours the guys have worked per
week. At
the
moment it works if only one person has input data for a week but
as
soon
as
other data is added, the query counts that to. Can anyone
suggest how
to
calculate the hours worked per person without it counting for
other
people.

The query which calculates the time is:
SELECT tblDates.WeekNo, Sum(nz([TotalHours])) AS
TotalWeekAdimTime,
Sum(nz([HoursTaken])) AS HolidayHoursTaken,
Sum((nz([ToilHoursTaken])))
AS
TotalToilHoursTaken, Sum(nz([TotalDayTimeMins]/60)) AS
TotalDayEventHours
FROM ((tblDates LEFT JOIN qryEventTime ON tblDates.Date =
qryEventTime.Date)
LEFT JOIN tblHoliday ON tblDates.Date = tblHoliday.Date) LEFT
JOIN
qryAdminTime ON tblDates.Date = qryAdminTime.Date
WHERE (((qryEventTime.AdviserName)=[Forms]![frmMenu]![Combo2]))
OR
(((tblHoliday.AdviserName)=[Forms]![frmMenu]![Combo2])) OR
(((qryAdminTime.AdviserName)=[Forms]![frmMenu]![Combo2]))
GROUP BY tblDates.WeekNo;

Do I need to do this through a macro / crosstab query or is
there a
simple
way of doing it??

 

[kc-mass]

I could be wrong but I believe the comments are related to the event, not
the hours toiled.

If needed you can record the start time and stop time of each period of
interest (travel to site, travel home, holiday hours, sick hours, et al)

Thanks Kevin,

If I used a single table as you suggest, can I still collect the comments
which go with what the guys are up to?

To give you a picture of how the database works at the moment;
The data entry form is split into 3 sections (which are how we define the
bulk of activities undertaken by the team) - Admin, Holidays and Events.
The
admin is pretty simple, start and finish time and then they select from a
list what activity they did in that time (expenses for example). Holiday
is
fairly self explanatory but they count it in hours hence the input of date
and hours taken (we're trying to keep it as simple as possible). The event
form is more complicated as it requires them to input travel times (leave
home, arrive event and reversed), lunch time (Start and end - not included
in
their working week so is subtracted from day hours but has to be recorded
as
we have a duty to make sure they are taking breaks). It then records
location, numbers spoken to, general assesment of the event, who arranged
it
and then asks for the focus of the event and a summation of comments they
received - the team work on a variety of projects so we collect comments
on
each one.

Just to make it that bit more exciting, the team are spread around the UK
so
the final program has to be simple to use.

If I were to use a more centralised structure for the hours, is it still
possible to collate all the info required? and would it require a
substantial
rebuild of what I've already got to do it?

Please bear in mind I'm a newcomer to Access so am building this much by
trial and error!

Thanks again for your help.
Dave

Hi Dave
First get rid of most of the group by clauses. My recollection is that
you
want to report by week and advisor only. Those should be the only group
by
phrases.

Second, and I know you do not want to hear this - but, your data
structure
and thus form usage is a problem here. Hours of all types ( toil,
holiday,
admin, other) all belong in the same table with a designator field that
indicates what type of hours they are. That way you can have reports
that
easily sum on week, advisor, type of hours, etc.

People filling in the form, probably a continuous form, should be able to
enter 8:00 to 12:00 as holiday and 12:00 to 16:00 as toil and mark each
segment appropriately.

Regards

Kevin

Thanks for your help with this, I've worked out how to get the totals
without
including records for other people - I've put in an extra layer of
queries
which pull out the data for each adviser from each table and then adds
it
up.
However, this has now caused a new small issue, in order to get the
query
to
work its requiring data to be input on each form. I've set all the
default
values to 0 and tried to add NZ to the query but it still doesn't seem
to
work. As this timesheet is supposed to be easy for everyone to use, is
there
a way to update the query so it doesn't need all 3 forms completed
(afterall
the guys don't take holiday every week - even if it seems like it!).
The
SQL
for the working query is......

SELECT tblAdvisers.AdviserName,
Sum(Nz([TotalDayEventHours]+[SumOfHoursTaken]+[SumOfToilHoursTaken]+[SumOfTotalHours]))
AS TotalWeekTime, qryAdminTimePerAdviser.SumOfTotalHours,
qryAdvisersHolidaySummary.SumOfHoursTaken,
qryEventTimeByAdviser.TotalDayEventHours,
qryAdvisersHolidaySummary.SumOfToilHoursTaken
FROM ((tblAdvisers INNER JOIN qryAdvisersHolidaySummary ON
tblAdvisers.AdviserName = qryAdvisersHolidaySummary.AdviserName) INNER
JOIN
qryEventTimeByAdviser ON tblAdvisers.AdviserName =
qryEventTimeByAdviser.AdviserName) INNER JOIN qryAdminTimePerAdviser ON
tblAdvisers.AdviserName = qryAdminTimePerAdviser.AdviserName
WHERE (((qryAdvisersHolidaySummary.WeekNo)=[Forms]![frmMenu]![WeekNo])
AND
((qryEventTimeByAdviser.WeekNo)=[Forms]![frmMenu]![WeekNo]))
GROUP BY tblAdvisers.AdviserName,
qryAdminTimePerAdviser.SumOfTotalHours,
qryAdvisersHolidaySummary.SumOfHoursTaken,
qryEventTimeByAdviser.TotalDayEventHours,
qryAdvisersHolidaySummary.SumOfToilHoursTaken
HAVING (((tblAdvisers.AdviserName)=[Forms]![frmMenu]![Combo2]));


Thanks again
Dave

:

Thanks both,

The basic structure works as follows:

3 data collection tables - Admin Time, Event Time and Holiday Taken.

These tables are linked by "tbl dates" - which allocates dates to week
numbers and "tblAdvisers" which holds details about each person (Name,
team,
are covered).

There are 4 forms which make up the user interface - Menu (where the
adviser
selects their name and the week number they are subitting for) and
then
three
further forms which populate the Admin, Event and Holiday tables.

On top of these tables are queries to calculate the totals for each
activity
(e.g.. how long did they spend working at an event, how much time have
they
spen on each admin task).

THe data for the Events table looks like this

Column headings
Event ID Adviser Name Date Leave Home Arrive Event Lunch Start
Lunch
End Leave Event Arrive Home Location Event Type Numbers Event
Feedback MediaType
MediaAudience ArrangedBy Local Authority Area
Row values
27 Julia Pollard Mon
20/07/09 00:00 00:00 00:00 00:00 00:00 00:00 hereish Corporate 100
Moderate Radio Interview 10 Agency Arranged cabbage

At the moment I have a query which calculates how much time has been
spent
on each activity and then sums this to give a total week time. The
problem
occurs when I enter the data for more than one adviser - the query
then
adds
their data into the totals. This is what I'm trying to stop so it just
calculates per adviser.

The code for the weekly summary is

SELECT qryTotalWeekEventTime.WeekNo,
Sum((Nz([TotalWeekAdimTime]+[HolidayHoursTaken]+[TotalDayEventHours]+[TotalToilHoursTaken])))
AS TotalWeekHours, qryTotalWeekEventTime.TotalWeekAdimTime,
qryTotalWeekEventTime.HolidayHoursTaken,
qryTotalWeekEventTime.TotalDayEventHours,
qryTotalWeekEventTime.TotalToilHoursTaken
FROM qryTotalWeekEventTime
GROUP BY qryTotalWeekEventTime.WeekNo,
qryTotalWeekEventTime.TotalWeekAdimTime,
qryTotalWeekEventTime.HolidayHoursTaken,
qryTotalWeekEventTime.TotalDayEventHours,
qryTotalWeekEventTime.TotalToilHoursTaken
HAVING (((qryTotalWeekEventTime.WeekNo)=[Forms]![frmMenu]![WeekNo]));

There is no reference to advisers in this query as I haven't been able
to
pull it in from the query which calculates the time per activity
(included in
my first post) - would this solve te problem, and if so how do I do
it.

Thanks
Dave

:

Additionally, show some sample data from the tables and show how you
want
those results to be summed and displayed by your query.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"kc-mass" <connearney_AT_comcast_DOT_net> wrote in message
Maybe you should show the structure of the tables you are working
with.
At the moment you have a combination of queries and tables with
undefined
content.

Regards

Kevin


message
Thanks Kevin,

I've tried that but it just puts three columns of "AdviserName"
in -
there
is a separate table with the advisers on ("tblAdviserName")
however
when
I
try and join this to the query it tells me there are "Ambiguous
outer
joins"
- to get the database to work it has had to be built around dates
rather
than
each adviser (this query has "tblDates" as its core.

Is there a way I can get it to match the names from the 3 columns
and
group
together whilst summing the hours worked for that week?

Thanks
Dave

:

Make that: In your GROUP BY clause you need to group by
"AdviserName"
from
some table
as well as WeekNo. That will give you the sum of hours by
advisor
by
week.

Regards

Kevin



"kc-mass" <connearney_AT_comcast_DOT_net> wrote in message
Hi,

In your group by clause you need to group by "AdviserName"
from
some
field.

Regards

Kevin


message
Hi, I'm building a database for a team of 28 people. The bulk
of
the
form
is
working fine based around dates and the adviser name. However
I'm now
trying
to build a query to show how many hours the guys have worked
per
week. At
the
moment it works if only one person has input data for a week
but
as
soon
as
other data is added, the query counts that to. Can anyone
suggest how
to
calculate the hours worked per person without it counting for
other
people.

The query which calculates the time is:
SELECT tblDates.WeekNo, Sum(nz([TotalHours])) AS
TotalWeekAdimTime,
Sum(nz([HoursTaken])) AS HolidayHoursTaken,
Sum((nz([ToilHoursTaken])))
AS
TotalToilHoursTaken, Sum(nz([TotalDayTimeMins]/60)) AS
TotalDayEventHours
FROM ((tblDates LEFT JOIN qryEventTime ON tblDates.Date =
qryEventTime.Date)
LEFT JOIN tblHoliday ON tblDates.Date = tblHoliday.Date) LEFT
JOIN
qryAdminTime ON tblDates.Date = qryAdminTime.Date
WHERE
(((qryEventTime.AdviserName)=[Forms]![frmMenu]![Combo2]))
OR
(((tblHoliday.AdviserName)=[Forms]![frmMenu]![Combo2])) OR
(((qryAdminTime.AdviserName)=[Forms]![frmMenu]![Combo2]))
GROUP BY tblDates.WeekNo;

Do I need to do this through a macro / crosstab query or is
there a
simple
way of doing it??

 

[kc-mass]

Hi Dave

I realize I never answered your direct question. If you want to get that
query going without the inapplicable records in the other tables change
your inner joins to left joins. Inner joins say that a matching record has
to exist in both tables a left join says give me all of the advisor records
and only those records in the tblHolidays that match.

Also if you were going to persue that one table for hours you might start
with something like this:

Let's say you start with 3 tables

tblAdvisor
AdvisorID
AdvisorName
Phone
Fields for whatever else you collect

tblEvent
EventID
FKAdvisorID
EventName
EventDate
EventType
EventLocation
EventAttendance
EventFeedback
EventMediaType
Other fields

tblTimeRecord
FKAdvisorID
FKEventID
DateOf
StartTime
EndTime
TypeOfTime (Toil, Lunch, Holiday, TravelTo, TravelFrom, Etc.)
**************************************************
Regards

Kevin

Thanks Kevin,

If I used a single table as you suggest, can I still collect the comments
which go with what the guys are up to?

To give you a picture of how the database works at the moment;
The data entry form is split into 3 sections (which are how we define the
bulk of activities undertaken by the team) - Admin, Holidays and Events.
The
admin is pretty simple, start and finish time and then they select from a
list what activity they did in that time (expenses for example). Holiday
is
fairly self explanatory but they count it in hours hence the input of date
and hours taken (we're trying to keep it as simple as possible). The event
form is more complicated as it requires them to input travel times (leave
home, arrive event and reversed), lunch time (Start and end - not included
in
their working week so is subtracted from day hours but has to be recorded
as
we have a duty to make sure they are taking breaks). It then records
location, numbers spoken to, general assesment of the event, who arranged
it
and then asks for the focus of the event and a summation of comments they
received - the team work on a variety of projects so we collect comments
on
each one.

Just to make it that bit more exciting, the team are spread around the UK
so
the final program has to be simple to use.

If I were to use a more centralised structure for the hours, is it still
possible to collate all the info required? and would it require a
substantial
rebuild of what I've already got to do it?

Please bear in mind I'm a newcomer to Access so am building this much by
trial and error!

Thanks again for your help.
Dave

Hi Dave
First get rid of most of the group by clauses. My recollection is that
you
want to report by week and advisor only. Those should be the only group
by
phrases.

Second, and I know you do not want to hear this - but, your data
structure
and thus form usage is a problem here. Hours of all types ( toil,
holiday,
admin, other) all belong in the same table with a designator field that
indicates what type of hours they are. That way you can have reports
that
easily sum on week, advisor, type of hours, etc.

People filling in the form, probably a continuous form, should be able to
enter 8:00 to 12:00 as holiday and 12:00 to 16:00 as toil and mark each
segment appropriately.

Regards

Kevin

Thanks for your help with this, I've worked out how to get the totals
without
including records for other people - I've put in an extra layer of
queries
which pull out the data for each adviser from each table and then adds
it
up.
However, this has now caused a new small issue, in order to get the
query
to
work its requiring data to be input on each form. I've set all the
default
values to 0 and tried to add NZ to the query but it still doesn't seem
to
work. As this timesheet is supposed to be easy for everyone to use, is
there
a way to update the query so it doesn't need all 3 forms completed
(afterall
the guys don't take holiday every week - even if it seems like it!).
The
SQL
for the working query is......

SELECT tblAdvisers.AdviserName,
Sum(Nz([TotalDayEventHours]+[SumOfHoursTaken]+[SumOfToilHoursTaken]+[SumOfTotalHours]))
AS TotalWeekTime, qryAdminTimePerAdviser.SumOfTotalHours,
qryAdvisersHolidaySummary.SumOfHoursTaken,
qryEventTimeByAdviser.TotalDayEventHours,
qryAdvisersHolidaySummary.SumOfToilHoursTaken
FROM ((tblAdvisers INNER JOIN qryAdvisersHolidaySummary ON
tblAdvisers.AdviserName = qryAdvisersHolidaySummary.AdviserName) INNER
JOIN
qryEventTimeByAdviser ON tblAdvisers.AdviserName =
qryEventTimeByAdviser.AdviserName) INNER JOIN qryAdminTimePerAdviser ON
tblAdvisers.AdviserName = qryAdminTimePerAdviser.AdviserName
WHERE (((qryAdvisersHolidaySummary.WeekNo)=[Forms]![frmMenu]![WeekNo])
AND
((qryEventTimeByAdviser.WeekNo)=[Forms]![frmMenu]![WeekNo]))
GROUP BY tblAdvisers.AdviserName,
qryAdminTimePerAdviser.SumOfTotalHours,
qryAdvisersHolidaySummary.SumOfHoursTaken,
qryEventTimeByAdviser.TotalDayEventHours,
qryAdvisersHolidaySummary.SumOfToilHoursTaken
HAVING (((tblAdvisers.AdviserName)=[Forms]![frmMenu]![Combo2]));


Thanks again
Dave

:

Thanks both,

The basic structure works as follows:

3 data collection tables - Admin Time, Event Time and Holiday Taken.

These tables are linked by "tbl dates" - which allocates dates to week
numbers and "tblAdvisers" which holds details about each person (Name,
team,
are covered).

There are 4 forms which make up the user interface - Menu (where the
adviser
selects their name and the week number they are subitting for) and
then
three
further forms which populate the Admin, Event and Holiday tables.

On top of these tables are queries to calculate the totals for each
activity
(e.g.. how long did they spend working at an event, how much time have
they
spen on each admin task).

THe data for the Events table looks like this

Column headings
Event ID Adviser Name Date Leave Home Arrive Event Lunch Start
Lunch
End Leave Event Arrive Home Location Event Type Numbers Event
Feedback MediaType
MediaAudience ArrangedBy Local Authority Area
Row values
27 Julia Pollard Mon
20/07/09 00:00 00:00 00:00 00:00 00:00 00:00 hereish Corporate 100
Moderate Radio Interview 10 Agency Arranged cabbage

At the moment I have a query which calculates how much time has been
spent
on each activity and then sums this to give a total week time. The
problem
occurs when I enter the data for more than one adviser - the query
then
adds
their data into the totals. This is what I'm trying to stop so it just
calculates per adviser.

The code for the weekly summary is

SELECT qryTotalWeekEventTime.WeekNo,
Sum((Nz([TotalWeekAdimTime]+[HolidayHoursTaken]+[TotalDayEventHours]+[TotalToilHoursTaken])))
AS TotalWeekHours, qryTotalWeekEventTime.TotalWeekAdimTime,
qryTotalWeekEventTime.HolidayHoursTaken,
qryTotalWeekEventTime.TotalDayEventHours,
qryTotalWeekEventTime.TotalToilHoursTaken
FROM qryTotalWeekEventTime
GROUP BY qryTotalWeekEventTime.WeekNo,
qryTotalWeekEventTime.TotalWeekAdimTime,
qryTotalWeekEventTime.HolidayHoursTaken,
qryTotalWeekEventTime.TotalDayEventHours,
qryTotalWeekEventTime.TotalToilHoursTaken
HAVING (((qryTotalWeekEventTime.WeekNo)=[Forms]![frmMenu]![WeekNo]));

There is no reference to advisers in this query as I haven't been able
to
pull it in from the query which calculates the time per activity
(included in
my first post) - would this solve te problem, and if so how do I do
it.

Thanks
Dave

:

Additionally, show some sample data from the tables and show how you
want
those results to be summed and displayed by your query.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"kc-mass" <connearney_AT_comcast_DOT_net> wrote in message
Maybe you should show the structure of the tables you are working
with.
At the moment you have a combination of queries and tables with
undefined
content.

Regards

Kevin


message
Thanks Kevin,

I've tried that but it just puts three columns of "AdviserName"
in -
there
is a separate table with the advisers on ("tblAdviserName")
however
when
I
try and join this to the query it tells me there are "Ambiguous
outer
joins"
- to get the database to work it has had to be built around dates
rather
than
each adviser (this query has "tblDates" as its core.

Is there a way I can get it to match the names from the 3 columns
and
group
together whilst summing the hours worked for that week?

Thanks
Dave

:

Make that: In your GROUP BY clause you need to group by
"AdviserName"
from
some table
as well as WeekNo. That will give you the sum of hours by
advisor
by
week.

Regards

Kevin



"kc-mass" <connearney_AT_comcast_DOT_net> wrote in message
Hi,

In your group by clause you need to group by "AdviserName"
from
some
field.

Regards

Kevin


message
Hi, I'm building a database for a team of 28 people. The bulk
of
the
form
is
working fine based around dates and the adviser name. However
I'm now
trying
to build a query to show how many hours the guys have worked
per
week. At
the
moment it works if only one person has input data for a week
but
as
soon
as
other data is added, the query counts that to. Can anyone
suggest how
to
calculate the hours worked per person without it counting for
other
people.

The query which calculates the time is:
SELECT tblDates.WeekNo, Sum(nz([TotalHours])) AS
TotalWeekAdimTime,
Sum(nz([HoursTaken])) AS HolidayHoursTaken,
Sum((nz([ToilHoursTaken])))
AS
TotalToilHoursTaken, Sum(nz([TotalDayTimeMins]/60)) AS
TotalDayEventHours
FROM ((tblDates LEFT JOIN qryEventTime ON tblDates.Date =
qryEventTime.Date)
LEFT JOIN tblHoliday ON tblDates.Date = tblHoliday.Date) LEFT
JOIN
qryAdminTime ON tblDates.Date = qryAdminTime.Date
WHERE
(((qryEventTime.AdviserName)=[Forms]![frmMenu]![Combo2]))
OR
(((tblHoliday.AdviserName)=[Forms]![frmMenu]![Combo2])) OR
(((qryAdminTime.AdviserName)=[Forms]![frmMenu]![Combo2]))
GROUP BY tblDates.WeekNo;

Do I need to do this through a macro / crosstab query or is
there a
simple
way of doing it??

 

[compostdave]

Thanks Kevin,

Very helpful. I'll have a fiddle with it and see how I get on. I've got a
couple of weeks before it has to be ready for use so I'll see what I can do
to make it better!

Thanks again for all your help
Dave

Hi Dave

I realize I never answered your direct question. If you want to get that
query going without the inapplicable records in the other tables change
your inner joins to left joins. Inner joins say that a matching record has
to exist in both tables a left join says give me all of the advisor records
and only those records in the tblHolidays that match.

Also if you were going to persue that one table for hours you might start
with something like this:

Let's say you start with 3 tables

tblAdvisor
AdvisorID
AdvisorName
Phone
Fields for whatever else you collect

tblEvent
EventID
FKAdvisorID
EventName
EventDate
EventType
EventLocation
EventAttendance
EventFeedback
EventMediaType
Other fields

tblTimeRecord
FKAdvisorID
FKEventID
DateOf
StartTime
EndTime
TypeOfTime (Toil, Lunch, Holiday, TravelTo, TravelFrom, Etc.)
**************************************************
Regards

Kevin

Thanks Kevin,

If I used a single table as you suggest, can I still collect the comments
which go with what the guys are up to?

To give you a picture of how the database works at the moment;
The data entry form is split into 3 sections (which are how we define the
bulk of activities undertaken by the team) - Admin, Holidays and Events.
The
admin is pretty simple, start and finish time and then they select from a
list what activity they did in that time (expenses for example). Holiday
is
fairly self explanatory but they count it in hours hence the input of date
and hours taken (we're trying to keep it as simple as possible). The event
form is more complicated as it requires them to input travel times (leave
home, arrive event and reversed), lunch time (Start and end - not included
in
their working week so is subtracted from day hours but has to be recorded
as
we have a duty to make sure they are taking breaks). It then records
location, numbers spoken to, general assesment of the event, who arranged
it
and then asks for the focus of the event and a summation of comments they
received - the team work on a variety of projects so we collect comments
on
each one.

Just to make it that bit more exciting, the team are spread around the UK
so
the final program has to be simple to use.

If I were to use a more centralised structure for the hours, is it still
possible to collate all the info required? and would it require a
substantial
rebuild of what I've already got to do it?

Please bear in mind I'm a newcomer to Access so am building this much by
trial and error!

Thanks again for your help.
Dave

Hi Dave
First get rid of most of the group by clauses. My recollection is that
you
want to report by week and advisor only. Those should be the only group
by
phrases.

Second, and I know you do not want to hear this - but, your data
structure
and thus form usage is a problem here. Hours of all types ( toil,
holiday,
admin, other) all belong in the same table with a designator field that
indicates what type of hours they are. That way you can have reports
that
easily sum on week, advisor, type of hours, etc.

People filling in the form, probably a continuous form, should be able to
enter 8:00 to 12:00 as holiday and 12:00 to 16:00 as toil and mark each
segment appropriately.

Regards

Kevin



Thanks for your help with this, I've worked out how to get the totals
without
including records for other people - I've put in an extra layer of
queries
which pull out the data for each adviser from each table and then adds
it
up.
However, this has now caused a new small issue, in order to get the
query
to
work its requiring data to be input on each form. I've set all the
default
values to 0 and tried to add NZ to the query but it still doesn't seem
to
work. As this timesheet is supposed to be easy for everyone to use, is
there
a way to update the query so it doesn't need all 3 forms completed
(afterall
the guys don't take holiday every week - even if it seems like it!).
The
SQL
for the working query is......

SELECT tblAdvisers.AdviserName,
Sum(Nz([TotalDayEventHours]+[SumOfHoursTaken]+[SumOfToilHoursTaken]+[SumOfTotalHours]))
AS TotalWeekTime, qryAdminTimePerAdviser.SumOfTotalHours,
qryAdvisersHolidaySummary.SumOfHoursTaken,
qryEventTimeByAdviser.TotalDayEventHours,
qryAdvisersHolidaySummary.SumOfToilHoursTaken
FROM ((tblAdvisers INNER JOIN qryAdvisersHolidaySummary ON
tblAdvisers.AdviserName = qryAdvisersHolidaySummary.AdviserName) INNER
JOIN
qryEventTimeByAdviser ON tblAdvisers.AdviserName =
qryEventTimeByAdviser.AdviserName) INNER JOIN qryAdminTimePerAdviser ON
tblAdvisers.AdviserName = qryAdminTimePerAdviser.AdviserName
WHERE (((qryAdvisersHolidaySummary.WeekNo)=[Forms]![frmMenu]![WeekNo])
AND
((qryEventTimeByAdviser.WeekNo)=[Forms]![frmMenu]![WeekNo]))
GROUP BY tblAdvisers.AdviserName,
qryAdminTimePerAdviser.SumOfTotalHours,
qryAdvisersHolidaySummary.SumOfHoursTaken,
qryEventTimeByAdviser.TotalDayEventHours,
qryAdvisersHolidaySummary.SumOfToilHoursTaken
HAVING (((tblAdvisers.AdviserName)=[Forms]![frmMenu]![Combo2]));


Thanks again
Dave

:

Thanks both,

The basic structure works as follows:

3 data collection tables - Admin Time, Event Time and Holiday Taken.

These tables are linked by "tbl dates" - which allocates dates to week
numbers and "tblAdvisers" which holds details about each person (Name,
team,
are covered).

There are 4 forms which make up the user interface - Menu (where the
adviser
selects their name and the week number they are subitting for) and
then
three
further forms which populate the Admin, Event and Holiday tables.

On top of these tables are queries to calculate the totals for each
activity
(e.g.. how long did they spend working at an event, how much time have
they
spen on each admin task).

THe data for the Events table looks like this

Column headings
Event ID Adviser Name Date Leave Home Arrive Event Lunch Start
Lunch
End Leave Event Arrive Home Location Event Type Numbers Event
Feedback MediaType
MediaAudience ArrangedBy Local Authority Area
Row values
27 Julia Pollard Mon
20/07/09 00:00 00:00 00:00 00:00 00:00 00:00 hereish Corporate 100
Moderate Radio Interview 10 Agency Arranged cabbage

At the moment I have a query which calculates how much time has been
spent
on each activity and then sums this to give a total week time. The
problem
occurs when I enter the data for more than one adviser - the query
then
adds
their data into the totals. This is what I'm trying to stop so it just
calculates per adviser.

The code for the weekly summary is

SELECT qryTotalWeekEventTime.WeekNo,
Sum((Nz([TotalWeekAdimTime]+[HolidayHoursTaken]+[TotalDayEventHours]+[TotalToilHoursTaken])))
AS TotalWeekHours, qryTotalWeekEventTime.TotalWeekAdimTime,
qryTotalWeekEventTime.HolidayHoursTaken,
qryTotalWeekEventTime.TotalDayEventHours,
qryTotalWeekEventTime.TotalToilHoursTaken
FROM qryTotalWeekEventTime
GROUP BY qryTotalWeekEventTime.WeekNo,
qryTotalWeekEventTime.TotalWeekAdimTime,
qryTotalWeekEventTime.HolidayHoursTaken,
qryTotalWeekEventTime.TotalDayEventHours,
qryTotalWeekEventTime.TotalToilHoursTaken
HAVING (((qryTotalWeekEventTime.WeekNo)=[Forms]![frmMenu]![WeekNo]));

There is no reference to advisers in this query as I haven't been able
to
pull it in from the query which calculates the time per activity
(included in
my first post) - would this solve te problem, and if so how do I do
it.

Thanks
Dave

:

Additionally, show some sample data from the tables and show how you
want
those results to be summed and displayed by your query.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/


"kc-mass" <connearney_AT_comcast_DOT_net> wrote in message
Maybe you should show the structure of the tables you are working
with.
At the moment you have a combination of queries and tables with
undefined
content.

Regards

Kevin


message
Thanks Kevin,

I've tried that but it just puts three columns of "AdviserName"
in -
there
is a separate table with the advisers on ("tblAdviserName")
however
when
I
try and join this to the query it tells me there are "Ambiguous
outer
joins"
- to get the database to work it has had to be built around dates
rather
than
each adviser (this query has "tblDates" as its core.

Is there a way I can get it to match the names from the 3 columns
and
group
together whilst summing the hours worked for that week?

Thanks
Dave

"kc-mass" wrote: