Calculating Number of hours worked by an employee

[King]

Hi

I have following MS Acess query

Here is the query

ID Name Prgm ID Client ID Date Start Time End
Time Minutes
C4 Trisha TIP DEK0703 7 /7 /2006 10:00:00 AM 12:00:00 PM
120
C4 Trisha TIP DEK0703 7 /11/2006 3:30:00 PM 4:30:00 PM
60
C4 Trisha TIP DEK0703 7 /12/2006 2:30:00 PM 4:00:00 PM
90
C4 Trisha TIP DEK0703 7 /23/200 10:00:00 AM 11:00:00 AM
60
C4 Trisha TIP DEK0704 7 /23/2006 10:00:00 AM 11:00:00 AM
60
C4 Trisha TIP DEK0704 7 /24/2006 11:00:00 AM 12:00:00 PM
60
C4 Trisha TIP DEK0704 7 /25/2006 1:00:00 PM 2:30:00 PM
90
As you can see column 4 and column 5, they are for the meetings of the
same counselor with two different clients and it should treat as one
hour but instead treats it as 2 hours ( 60 min for column 4 and 60
minutes for column 5).

SQL View of the above query

SELECT Counselor.CoID, Counselor.CoFName, Counselor.CoLName, [Counselor
Program Administer].PrgmID AS [Counselor Program Administer_PrgmID],
[Client Program Attendance].ClientID, [Client Program
Attendance].[Session Number], [Client Program Attendance].MeetDate,
[Client Program Attendance].MeetStarttime, [Client Program
Attendance].MeetEndTime, DateDiff("n",[MeetStartTime],[MeetEndTime]) AS
Minutes
FROM (Program INNER JOIN [Client Program Attendance] ON Program.PrgmID
= [Client Program Attendance].PrgmID) INNER JOIN (Counselor INNER JOIN
[Counselor Program Administer] ON Counselor.CoID = [Counselor Program
Administer].CoID) ON Program.PrgmID = [Counselor Program
Administer].PrgmID
WHERE (((Counselor.CoID)=[ Enter Counselor ID ]))
ORDER BY [Client Program Attendance].MeetDate;

Can anyone solve this.

Thanks
MaCh

[Jan]

Hi:

I have a few clients for whom I have to solve similar problems, where
there's effectively "service given" (the time the staff spent) and
"service received" (the time the clients spent), and they could be
different if there are multiple clients in a session. My approach has
been to have a table for the session, which shows the date, staff
person, and session length (plus any info on location, type of service,
etc.), and then a separate table called something like SessionClient,
with sessionID and clientID, which is a one-to-many table with one
record for each client who attended the session. Then I can count
either staff time or client time depending on what I need.

HTH

Jan

King wrote:

Hi

I have following MS Acess query

Here is the query

ID Name Prgm ID Client ID Date Start Time End
Time Minutes C4 Trisha TIP DEK0703 7 /7 /2006 10:00:00
AM 12:00:00 PM 120 C4 Trisha TIP DEK0703 7 /11/2006
3:30:00 PM 4:30:00 PM 60 C4 Trisha TIP DEK0703 7
/12/2006 2:30:00 PM 4:00:00 PM 90 C4 Trisha TIP DEK0703
7 /23/200 10:00:00 AM 11:00:00 AM 60 C4 Trisha TIP
DEK0704 7 /23/2006 10:00:00 AM 11:00:00 AM 60 C4 Trisha TIP
DEK0704 7 /24/2006 11:00:00 AM 12:00:00 PM 60 C4 Trisha TIP
DEK0704 7 /25/2006 1:00:00 PM 2:30:00 PM 90
As you can see column 4 and column 5, they are for the meetings of
the same counselor with two different clients and it should treat as
one hour but instead treats it as 2 hours ( 60 min for column 4 and
60 minutes for column 5).

SQL View of the above query

SELECT Counselor.CoID, Counselor.CoFName, Counselor.CoLName,
[Counselor Program Administer].PrgmID AS [Counselor Program
Administer_PrgmID], [Client Program Attendance].ClientID, [Client
Program Attendance].[Session Number], [Client Program
Attendance].MeetDate, [Client Program Attendance].MeetStarttime,
[Client Program Attendance].MeetEndTime,
DateDiff("n",[MeetStartTime],[MeetEndTime]) AS Minutes FROM (Program
INNER JOIN [Client Program Attendance] ON Program.PrgmID = [Client
Program Attendance].PrgmID) INNER JOIN (Counselor INNER JOIN
[Counselor Program Administer] ON Counselor.CoID = [Counselor Program
Administer].CoID) ON Program.PrgmID = [Counselor Program
Administer].PrgmID WHERE (((Counselor.CoID)=[ Enter Counselor ID ]))
ORDER BY [Client Program Attendance].MeetDate;

Can anyone solve this.

Thanks MaCh

[Allen Browne]

This is going to need a different data structure.

It appears that one meeting can involve multiple people: perhaps 2 (as in
most of your examples), pehaps 3 (as in row 4 & 5 of your example), perhaps
more. Your existing data structure has no way to avoid an impossibly complex
combination of data, with multiple partially or completely overlapping time
periods. It will be quite impractical to query that.

Instead create 3 tables: one for persons, one for meetings, and one to
record the persons who attended the meeting:

Person table (one record for each person):
PersonID AutoNumber (primary key)
Surname
FirstName
RoleID Staff, client, whatever.

Meeting table (one record for any time people meet):
MeetingID AutoNumber (primary key)
LocationID where the meeting was held
SchedStart date/time the meeting was scheduled to start
SchedEnd date/time the meeting was scheduled to end.

MeetingPerson table (one record for each person in each meeting):
MeetingPersonID AutoNumber (primary key)
MeetingID Number which meeting this record refers to
PersonID Number which person this record is for
JoinTime date/time the person joined the meeting
LeaveTime date/time the person left the meeting.

So, if 2 persons attended a particular meeting, there would be 2 records for
that meeting in the MeetingPerson table. If 4 people attended, there are 4
records. The MeetingPerson table allows you to record when each person
arrived, and left. This gives you great flexibility, say Trisha meets with
DEK0703 who is a minor, and after 30 minutes the child agrees her mother can
join the meeting for 15 minutes, you can record this.

In the end, you have solved your problem, because the time that *Trisha*
actually attended the meeting can be easily queried form the MeetingPerson
table, since there are no overlapping records or duplicates (unless someone
wrongly records her as present in 2 meetings at once, and you can warn or
block that at the time the data is entered.)

BTW, you might currently have clients and staff in separate tables? The
structure above requires they are all in the one table, with another field
to distinguish the person's role.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"King" <pi*******@gmail.comwrote in message
news:11*********************@m73g2000cwd.googlegro ups.com...

Hi

I have following MS Acess query

Here is the query

ID Name Prgm ID Client ID Date Start Time End
Time Minutes
C4 Trisha TIP DEK0703 7 /7 /2006 10:00:00 AM 12:00:00 PM
120
C4 Trisha TIP DEK0703 7 /11/2006 3:30:00 PM 4:30:00 PM
60
C4 Trisha TIP DEK0703 7 /12/2006 2:30:00 PM 4:00:00 PM
90
C4 Trisha TIP DEK0703 7 /23/200 10:00:00 AM 11:00:00 AM
60
C4 Trisha TIP DEK0704 7 /23/2006 10:00:00 AM 11:00:00 AM
60
C4 Trisha TIP DEK0704 7 /24/2006 11:00:00 AM 12:00:00 PM
60
C4 Trisha TIP DEK0704 7 /25/2006 1:00:00 PM 2:30:00 PM
90
As you can see column 4 and column 5, they are for the meetings of the
same counselor with two different clients and it should treat as one
hour but instead treats it as 2 hours ( 60 min for column 4 and 60
minutes for column 5).

SQL View of the above query

SELECT Counselor.CoID, Counselor.CoFName, Counselor.CoLName, [Counselor
Program Administer].PrgmID AS [Counselor Program Administer_PrgmID],
[Client Program Attendance].ClientID, [Client Program
Attendance].[Session Number], [Client Program Attendance].MeetDate,
[Client Program Attendance].MeetStarttime, [Client Program
Attendance].MeetEndTime, DateDiff("n",[MeetStartTime],[MeetEndTime]) AS
Minutes
FROM (Program INNER JOIN [Client Program Attendance] ON Program.PrgmID
= [Client Program Attendance].PrgmID) INNER JOIN (Counselor INNER JOIN
[Counselor Program Administer] ON Counselor.CoID = [Counselor Program
Administer].CoID) ON Program.PrgmID = [Counselor Program
Administer].PrgmID
WHERE (((Counselor.CoID)=[ Enter Counselor ID ]))
ORDER BY [Client Program Attendance].MeetDate;

Can anyone solve this.

Thanks
MaCh

[King]

Hi Allen

Thanks for the reply.

I have a problem if I put the Client and Counselor information in the
same table. A Counselor adminsters programs and as of now I have three
tables. One for Counselor, one for Adminsters and one for Programs.

If I put Clients and staff information in the same table, is it
possible for me to track Client attendance and Counselor Programs.

Please help me. Appreciate your time and work.

MaCh
Allen Browne wrote:

This is going to need a different data structure.

It appears that one meeting can involve multiple people: perhaps 2 (as in
most of your examples), pehaps 3 (as in row 4 & 5 of your example), perhaps
more. Your existing data structure has no way to avoid an impossibly complex
combination of data, with multiple partially or completely overlapping time
periods. It will be quite impractical to query that.

Instead create 3 tables: one for persons, one for meetings, and one to
record the persons who attended the meeting:

Person table (one record for each person):
PersonID AutoNumber (primary key)
Surname
FirstName
RoleID Staff, client, whatever.

Meeting table (one record for any time people meet):
MeetingID AutoNumber (primary key)
LocationID where the meeting was held
SchedStart date/time the meeting was scheduled to start
SchedEnd date/time the meeting was scheduled to end.

MeetingPerson table (one record for each person in each meeting):
MeetingPersonID AutoNumber (primary key)
MeetingID Number which meeting this record refers to
PersonID Number which person this record is for
JoinTime date/time the person joined the meeting
LeaveTime date/time the person left the meeting.

So, if 2 persons attended a particular meeting, there would be 2 records for
that meeting in the MeetingPerson table. If 4 people attended, there are 4
records. The MeetingPerson table allows you to record when each person
arrived, and left. This gives you great flexibility, say Trisha meets with
DEK0703 who is a minor, and after 30 minutes the child agrees her mother can
join the meeting for 15 minutes, you can record this.

In the end, you have solved your problem, because the time that *Trisha*
actually attended the meeting can be easily queried form the MeetingPerson
table, since there are no overlapping records or duplicates (unless someone
wrongly records her as present in 2 meetings at once, and you can warn or
block that at the time the data is entered.)

BTW, you might currently have clients and staff in separate tables? The
structure above requires they are all in the one table, with another field
to distinguish the person's role.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"King" <pi*******@gmail.comwrote in message
news:11*********************@m73g2000cwd.googlegro ups.com...

Hi

I have following MS Acess query

Here is the query

ID Name Prgm ID Client ID Date Start Time End
Time Minutes
C4 Trisha TIP DEK0703 7 /7 /2006 10:00:00 AM 12:00:00 PM
120
C4 Trisha TIP DEK0703 7 /11/2006 3:30:00 PM 4:30:00 PM
60
C4 Trisha TIP DEK0703 7 /12/2006 2:30:00 PM 4:00:00 PM
90
C4 Trisha TIP DEK0703 7 /23/200 10:00:00 AM 11:00:00 AM
60
C4 Trisha TIP DEK0704 7 /23/2006 10:00:00 AM 11:00:00 AM
60
C4 Trisha TIP DEK0704 7 /24/2006 11:00:00 AM 12:00:00 PM
60
C4 Trisha TIP DEK0704 7 /25/2006 1:00:00 PM 2:30:00 PM
90
As you can see column 4 and column 5, they are for the meetings of the
same counselor with two different clients and it should treat as one
hour but instead treats it as 2 hours ( 60 min for column 4 and 60
minutes for column 5).

SQL View of the above query

SELECT Counselor.CoID, Counselor.CoFName, Counselor.CoLName, [Counselor
Program Administer].PrgmID AS [Counselor Program Administer_PrgmID],
[Client Program Attendance].ClientID, [Client Program
Attendance].[Session Number], [Client Program Attendance].MeetDate,
[Client Program Attendance].MeetStarttime, [Client Program
Attendance].MeetEndTime, DateDiff("n",[MeetStartTime],[MeetEndTime]) AS
Minutes
FROM (Program INNER JOIN [Client Program Attendance] ON Program.PrgmID
= [Client Program Attendance].PrgmID) INNER JOIN (Counselor INNER JOIN
[Counselor Program Administer] ON Counselor.CoID = [Counselor Program
Administer].CoID) ON Program.PrgmID = [Counselor Program
Administer].PrgmID
WHERE (((Counselor.CoID)=[ Enter Counselor ID ]))
ORDER BY [Client Program Attendance].MeetDate;

Can anyone solve this.

Thanks
MaCh

[Allen Browne]

Programs are separate entities, so it makes sense to have them in their own
table.

Counselor and clients are both persons. There are potentially other kinds of
persons you may deal with as well, such as family members of clients,
doctors at other clinics, possibly volunteers/trainees/work experience
students. I find is easiest to put all these people in one table, so you can
connect them up in any way you may need.

If you don't want to do that, you can keep separate tables.
In the MeetingPerson table, replace
PersonID Number which person this record is for
with 2 fields:
CounselorID Number the counsellor this record is for
ClientID Number the counsellee this record is for
and add a validation rule that exactly one of those 2 fields must present
(i.e. any record of meeting attendence must have a counsellor or a client,
but not both.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"King" <pi*******@gmail.comwrote in message
news:11*********************@p79g2000cwp.googlegro ups.com...

Hi Allen

Thanks for the reply.

I have a problem if I put the Client and Counselor information in the
same table. A Counselor adminsters programs and as of now I have three
tables. One for Counselor, one for Adminsters and one for Programs.

If I put Clients and staff information in the same table, is it
possible for me to track Client attendance and Counselor Programs.

Please help me. Appreciate your time and work.

MaCh
Allen Browne wrote:

>This is going to need a different data structure.

It appears that one meeting can involve multiple people: perhaps 2 (as in
most of your examples), pehaps 3 (as in row 4 & 5 of your example),
perhaps
more. Your existing data structure has no way to avoid an impossibly
complex
combination of data, with multiple partially or completely overlapping
time
periods. It will be quite impractical to query that.

Instead create 3 tables: one for persons, one for meetings, and one to
record the persons who attended the meeting:

Person table (one record for each person):
PersonID AutoNumber (primary key)
Surname
FirstName
RoleID Staff, client, whatever.

Meeting table (one record for any time people meet):
MeetingID AutoNumber (primary key)
LocationID where the meeting was held
SchedStart date/time the meeting was scheduled to start
SchedEnd date/time the meeting was scheduled to end.

MeetingPerson table (one record for each person in each meeting):
MeetingPersonID AutoNumber (primary key)
MeetingID Number which meeting this record refers to
PersonID Number which person this record is for
JoinTime date/time the person joined the meeting
LeaveTime date/time the person left the meeting.

So, if 2 persons attended a particular meeting, there would be 2 records
for
that meeting in the MeetingPerson table. If 4 people attended, there are
4
records. The MeetingPerson table allows you to record when each person
arrived, and left. This gives you great flexibility, say Trisha meets
with
DEK0703 who is a minor, and after 30 minutes the child agrees her mother
can
join the meeting for 15 minutes, you can record this.

In the end, you have solved your problem, because the time that *Trisha*
actually attended the meeting can be easily queried form the
MeetingPerson
table, since there are no overlapping records or duplicates (unless
someone
wrongly records her as present in 2 meetings at once, and you can warn or
block that at the time the data is entered.)

BTW, you might currently have clients and staff in separate tables? The
structure above requires they are all in the one table, with another
field
to distinguish the person's role.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"King" <pi*******@gmail.comwrote in message
news:11*********************@m73g2000cwd.googlegr oups.com...

Hi

I have following MS Acess query

Here is the query

ID Name Prgm ID Client ID Date Start Time End
Time Minutes
C4 Trisha TIP DEK0703 7 /7 /2006 10:00:00 AM 12:00:00 PM
120
C4 Trisha TIP DEK0703 7 /11/2006 3:30:00 PM 4:30:00 PM
60
C4 Trisha TIP DEK0703 7 /12/2006 2:30:00 PM 4:00:00 PM
90
C4 Trisha TIP DEK0703 7 /23/200 10:00:00 AM 11:00:00 AM
60
C4 Trisha TIP DEK0704 7 /23/2006 10:00:00 AM 11:00:00 AM
60
C4 Trisha TIP DEK0704 7 /24/2006 11:00:00 AM 12:00:00 PM
60
C4 Trisha TIP DEK0704 7 /25/2006 1:00:00 PM 2:30:00 PM
90
As you can see column 4 and column 5, they are for the meetings of the
same counselor with two different clients and it should treat as one
hour but instead treats it as 2 hours ( 60 min for column 4 and 60
minutes for column 5).

SQL View of the above query

SELECT Counselor.CoID, Counselor.CoFName, Counselor.CoLName, [Counselor
Program Administer].PrgmID AS [Counselor Program Administer_PrgmID],
[Client Program Attendance].ClientID, [Client Program
Attendance].[Session Number], [Client Program Attendance].MeetDate,
[Client Program Attendance].MeetStarttime, [Client Program
Attendance].MeetEndTime, DateDiff("n",[MeetStartTime],[MeetEndTime]) AS
Minutes
FROM (Program INNER JOIN [Client Program Attendance] ON Program.PrgmID
= [Client Program Attendance].PrgmID) INNER JOIN (Counselor INNER JOIN
[Counselor Program Administer] ON Counselor.CoID = [Counselor Program
Administer].CoID) ON Program.PrgmID = [Counselor Program
Administer].PrgmID
WHERE (((Counselor.CoID)=[ Enter Counselor ID ]))
ORDER BY [Client Program Attendance].MeetDate;

Can anyone solve this.

Thanks
MaCh

[King]

Dear Allen

Firstly thanks for your valuable time.

I wanted to clear my question now. My intention is to track the time
spent by Counselor with Clients. I also need to keep track as which
counselor is meeting with which Client.

When I run a query for number of hours spent by a Client, it works
perfect.

The problem is only when I run the query to count number of hours spent
by a Counselor.

In the case where we put a validation rule that only field cant exist,
then how will I track Counselor's Clients.

Thanks

MaCh

Allen Browne wrote:

Programs are separate entities, so it makes sense to have them in their own
table.

Counselor and clients are both persons. There are potentially other kinds of
persons you may deal with as well, such as family members of clients,
doctors at other clinics, possibly volunteers/trainees/work experience
students. I find is easiest to put all these people in one table, so you can
connect them up in any way you may need.

If you don't want to do that, you can keep separate tables.
In the MeetingPerson table, replace
PersonID Number which person this record is for
with 2 fields:
CounselorID Number the counsellor this record is for
ClientID Number the counsellee this record is for
and add a validation rule that exactly one of those 2 fields must present
(i.e. any record of meeting attendence must have a counsellor or a client,
but not both.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"King" <pi*******@gmail.comwrote in message
news:11*********************@p79g2000cwp.googlegro ups.com...

Hi Allen

Thanks for the reply.

I have a problem if I put the Client and Counselor information in the
same table. A Counselor adminsters programs and as of now I have three
tables. One for Counselor, one for Adminsters and one for Programs.

If I put Clients and staff information in the same table, is it
possible for me to track Client attendance and Counselor Programs.

Please help me. Appreciate your time and work.

MaCh
Allen Browne wrote:

This is going to need a different data structure.

It appears that one meeting can involve multiple people: perhaps 2 (as in
most of your examples), pehaps 3 (as in row 4 & 5 of your example),
perhaps
more. Your existing data structure has no way to avoid an impossibly
complex
combination of data, with multiple partially or completely overlapping
time
periods. It will be quite impractical to query that.

Instead create 3 tables: one for persons, one for meetings, and one to
record the persons who attended the meeting:

Person table (one record for each person):
PersonID AutoNumber (primary key)
Surname
FirstName
RoleID Staff, client, whatever.

Meeting table (one record for any time people meet):
MeetingID AutoNumber (primary key)
LocationID where the meeting was held
SchedStart date/time the meeting was scheduled to start
SchedEnd date/time the meeting was scheduled to end.

MeetingPerson table (one record for each person in each meeting):
MeetingPersonID AutoNumber (primary key)
MeetingID Number which meeting this record refers to
PersonID Number which person this record is for
JoinTime date/time the person joined the meeting
LeaveTime date/time the person left the meeting.

So, if 2 persons attended a particular meeting, there would be 2 records
for
that meeting in the MeetingPerson table. If 4 people attended, there are
4
records. The MeetingPerson table allows you to record when each person
arrived, and left. This gives you great flexibility, say Trisha meets
with
DEK0703 who is a minor, and after 30 minutes the child agrees her mother
can
join the meeting for 15 minutes, you can record this.

In the end, you have solved your problem, because the time that *Trisha*
actually attended the meeting can be easily queried form the
MeetingPerson
table, since there are no overlapping records or duplicates (unless
someone
wrongly records her as present in 2 meetings at once, and you can warn or
block that at the time the data is entered.)

BTW, you might currently have clients and staff in separate tables? The
structure above requires they are all in the one table, with another
field
to distinguish the person's role.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"King" <pi*******@gmail.comwrote in message
news:11*********************@m73g2000cwd.googlegro ups.com...
Hi

I have following MS Acess query

Here is the query

ID Name Prgm ID Client ID Date Start Time End
Time Minutes
C4 Trisha TIP DEK0703 7 /7 /2006 10:00:00 AM 12:00:00 PM
120
C4 Trisha TIP DEK0703 7 /11/2006 3:30:00 PM 4:30:00 PM
60
C4 Trisha TIP DEK0703 7 /12/2006 2:30:00 PM 4:00:00 PM
90
C4 Trisha TIP DEK0703 7 /23/200 10:00:00 AM 11:00:00 AM
60
C4 Trisha TIP DEK0704 7 /23/2006 10:00:00 AM 11:00:00 AM
60
C4 Trisha TIP DEK0704 7 /24/2006 11:00:00 AM 12:00:00 PM
60
C4 Trisha TIP DEK0704 7 /25/2006 1:00:00 PM 2:30:00 PM
90
As you can see column 4 and column 5, they are for the meetings of the
same counselor with two different clients and it should treat as one
hour but instead treats it as 2 hours ( 60 min for column 4 and 60
minutes for column 5).

SQL View of the above query

SELECT Counselor.CoID, Counselor.CoFName, Counselor.CoLName, [Counselor
Program Administer].PrgmID AS [Counselor Program Administer_PrgmID],
[Client Program Attendance].ClientID, [Client Program
Attendance].[Session Number], [Client Program Attendance].MeetDate,
[Client Program Attendance].MeetStarttime, [Client Program
Attendance].MeetEndTime, DateDiff("n",[MeetStartTime],[MeetEndTime]) AS
Minutes
FROM (Program INNER JOIN [Client Program Attendance] ON Program.PrgmID
= [Client Program Attendance].PrgmID) INNER JOIN (Counselor INNER JOIN
[Counselor Program Administer] ON Counselor.CoID = [Counselor Program
Administer].CoID) ON Program.PrgmID = [Counselor Program
Administer].PrgmID
WHERE (((Counselor.CoID)=[ Enter Counselor ID ]))
ORDER BY [Client Program Attendance].MeetDate;

Can anyone solve this.

Thanks
MaCh

[Allen Browne]

One meeting contains 2 more more people.
There is therefore a one-to-many relation between Meeting and MeetingPerson.

Each entry in MeetingPerson defines ONE of the people in the meeting. There
are 2 records in MeetingPerson if there are 2 people at the meeting. Each
record must be fore one person only.

Whether do do that by putting all the people in one table (so MeetingPerson
has a foreign key to the Person table), or by using separate tables for
Client and Counselor (so MeetingPerson has 2 foreign keys, and you then have
to ensure that each record uses exactly 1 of them) is up to you.

Ultimately, you get to query the time spent by the counsellor in the meeting
very simply, by querying their records in the MeetingPerson table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"King" <pi*******@gmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

Dear Allen

Firstly thanks for your valuable time.

I wanted to clear my question now. My intention is to track the time
spent by Counselor with Clients. I also need to keep track as which
counselor is meeting with which Client.

When I run a query for number of hours spent by a Client, it works
perfect.

The problem is only when I run the query to count number of hours spent
by a Counselor.

In the case where we put a validation rule that only field cant exist,
then how will I track Counselor's Clients.

Thanks

MaCh

Allen Browne wrote:

>Programs are separate entities, so it makes sense to have them in their
own
table.

Counselor and clients are both persons. There are potentially other kinds
of
persons you may deal with as well, such as family members of clients,
doctors at other clinics, possibly volunteers/trainees/work experience
students. I find is easiest to put all these people in one table, so you
can
connect them up in any way you may need.

If you don't want to do that, you can keep separate tables.
In the MeetingPerson table, replace
PersonID Number which person this record is for
with 2 fields:
CounselorID Number the counsellor this record is for
ClientID Number the counsellee this record is for
and add a validation rule that exactly one of those 2 fields must present
(i.e. any record of meeting attendence must have a counsellor or a
client,
but not both.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"King" <pi*******@gmail.comwrote in message
news:11*********************@p79g2000cwp.googlegr oups.com...

Hi Allen

Thanks for the reply.

I have a problem if I put the Client and Counselor information in the
same table. A Counselor adminsters programs and as of now I have three
tables. One for Counselor, one for Adminsters and one for Programs.

If I put Clients and staff information in the same table, is it
possible for me to track Client attendance and Counselor Programs.

Please help me. Appreciate your time and work.

MaCh
Allen Browne wrote:
This is going to need a different data structure.

It appears that one meeting can involve multiple people: perhaps 2 (as
in
most of your examples), pehaps 3 (as in row 4 & 5 of your example),
perhaps
more. Your existing data structure has no way to avoid an impossibly
complex
combination of data, with multiple partially or completely overlapping
time
periods. It will be quite impractical to query that.

Instead create 3 tables: one for persons, one for meetings, and one to
record the persons who attended the meeting:

Person table (one record for each person):
PersonID AutoNumber (primary key)
Surname
FirstName
RoleID Staff, client, whatever.

Meeting table (one record for any time people meet):
MeetingID AutoNumber (primary key)
LocationID where the meeting was held
SchedStart date/time the meeting was scheduled to start
SchedEnd date/time the meeting was scheduled to end.

MeetingPerson table (one record for each person in each meeting):
MeetingPersonID AutoNumber (primary key)
MeetingID Number which meeting this record refers to
PersonID Number which person this record is for
JoinTime date/time the person joined the meeting
LeaveTime date/time the person left the meeting.

So, if 2 persons attended a particular meeting, there would be 2
records
for
that meeting in the MeetingPerson table. If 4 people attended, there
are
4
records. The MeetingPerson table allows you to record when each person
arrived, and left. This gives you great flexibility, say Trisha meets
with
DEK0703 who is a minor, and after 30 minutes the child agrees her
mother
can
join the meeting for 15 minutes, you can record this.

In the end, you have solved your problem, because the time that
*Trisha*
actually attended the meeting can be easily queried form the
MeetingPerson
table, since there are no overlapping records or duplicates (unless
someone
wrongly records her as present in 2 meetings at once, and you can warn
or
block that at the time the data is entered.)

BTW, you might currently have clients and staff in separate tables?
The
structure above requires they are all in the one table, with another
field
to distinguish the person's role.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"King" <pi*******@gmail.comwrote in message
news:11*********************@m73g2000cwd.googlegr oups.com...
Hi

I have following MS Acess query

Here is the query

ID Name Prgm ID Client ID Date Start Time End
Time Minutes
C4 Trisha TIP DEK0703 7 /7 /2006 10:00:00 AM 12:00:00 PM
120
C4 Trisha TIP DEK0703 7 /11/2006 3:30:00 PM 4:30:00 PM
60
C4 Trisha TIP DEK0703 7 /12/2006 2:30:00 PM 4:00:00 PM
90
C4 Trisha TIP DEK0703 7 /23/200 10:00:00 AM 11:00:00
AM
60
C4 Trisha TIP DEK0704 7 /23/2006 10:00:00 AM 11:00:00
AM
60
C4 Trisha TIP DEK0704 7 /24/2006 11:00:00 AM 12:00:00
PM
60
C4 Trisha TIP DEK0704 7 /25/2006 1:00:00 PM 2:30:00
PM
90
As you can see column 4 and column 5, they are for the meetings of
the
same counselor with two different clients and it should treat as one
hour but instead treats it as 2 hours ( 60 min for column 4 and 60
minutes for column 5).

SQL View of the above query

SELECT Counselor.CoID, Counselor.CoFName, Counselor.CoLName,
[Counselor
Program Administer].PrgmID AS [Counselor Program Administer_PrgmID],
[Client Program Attendance].ClientID, [Client Program
Attendance].[Session Number], [Client Program Attendance].MeetDate,
[Client Program Attendance].MeetStarttime, [Client Program
Attendance].MeetEndTime, DateDiff("n",[MeetStartTime],[MeetEndTime])
AS
Minutes
FROM (Program INNER JOIN [Client Program Attendance] ON
Program.PrgmID
= [Client Program Attendance].PrgmID) INNER JOIN (Counselor INNER
JOIN
[Counselor Program Administer] ON Counselor.CoID = [Counselor
Program
Administer].CoID) ON Program.PrgmID = [Counselor Program
Administer].PrgmID
WHERE (((Counselor.CoID)=[ Enter Counselor ID ]))
ORDER BY [Client Program Attendance].MeetDate;

Can anyone solve this.

Thanks
MaCh

[King]

Do you mean the Meeting person table will have the following fields

MeetingPersonID, Client ID, Counselor ID, MeetDate, StartTime, EndTime

If it is so, dont you think there will a problem when a counselor meets
with two clients at the same time.

I am sorry if I am understood you in a wrong way. I am a student and
its my first project which is for a non profit organization.

Thanks

MaCh

Allen Browne wrote:

One meeting contains 2 more more people.
There is therefore a one-to-many relation between Meeting and MeetingPerson.

Each entry in MeetingPerson defines ONE of the people in the meeting. There
are 2 records in MeetingPerson if there are 2 people at the meeting. Each
record must be fore one person only.

Whether do do that by putting all the people in one table (so MeetingPerson
has a foreign key to the Person table), or by using separate tables for
Client and Counselor (so MeetingPerson has 2 foreign keys, and you then have
to ensure that each record uses exactly 1 of them) is up to you.

Ultimately, you get to query the time spent by the counsellor in the meeting
very simply, by querying their records in the MeetingPerson table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"King" <pi*******@gmail.comwrote in message
news:11**********************@h48g2000cwc.googlegr oups.com...

Dear Allen

Firstly thanks for your valuable time.

I wanted to clear my question now. My intention is to track the time
spent by Counselor with Clients. I also need to keep track as which
counselor is meeting with which Client.

When I run a query for number of hours spent by a Client, it works
perfect.

The problem is only when I run the query to count number of hours spent
by a Counselor.

In the case where we put a validation rule that only field cant exist,
then how will I track Counselor's Clients.

Thanks

MaCh

Allen Browne wrote:

Programs are separate entities, so it makes sense to have them in their
own
table.

Counselor and clients are both persons. There are potentially other kinds
of
persons you may deal with as well, such as family members of clients,
doctors at other clinics, possibly volunteers/trainees/work experience
students. I find is easiest to put all these people in one table, so you
can
connect them up in any way you may need.

If you don't want to do that, you can keep separate tables.
In the MeetingPerson table, replace
PersonID Number which person this record is for
with 2 fields:
CounselorID Number the counsellor this record is for
ClientID Number the counsellee this record is for
and add a validation rule that exactly one of those 2 fields must present
(i.e. any record of meeting attendence must have a counsellor or a
client,
but not both.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"King" <pi*******@gmail.comwrote in message
news:11*********************@p79g2000cwp.googlegro ups.com...
Hi Allen

Thanks for the reply.

I have a problem if I put the Client and Counselor information in the
same table. A Counselor adminsters programs and as of now I have three
tables. One for Counselor, one for Adminsters and one for Programs.

If I put Clients and staff information in the same table, is it
possible for me to track Client attendance and Counselor Programs.

Please help me. Appreciate your time and work.

MaCh
Allen Browne wrote:
This is going to need a different data structure.

It appears that one meeting can involve multiple people: perhaps 2 (as
in
most of your examples), pehaps 3 (as in row 4 & 5 of your example),
perhaps
more. Your existing data structure has no way to avoid an impossibly
complex
combination of data, with multiple partially or completely overlapping
time
periods. It will be quite impractical to query that.

Instead create 3 tables: one for persons, one for meetings, and one to
record the persons who attended the meeting:

Person table (one record for each person):
PersonID AutoNumber (primary key)
Surname
FirstName
RoleID Staff, client, whatever.

Meeting table (one record for any time people meet):
MeetingID AutoNumber (primary key)
LocationID where the meeting was held
SchedStart date/time the meeting was scheduled to start
SchedEnd date/time the meeting was scheduled to end.

MeetingPerson table (one record for each person in each meeting):
MeetingPersonID AutoNumber (primary key)
MeetingID Number which meeting this record refers to
PersonID Number which person this record is for
JoinTime date/time the person joined the meeting
LeaveTime date/time the person left the meeting.

So, if 2 persons attended a particular meeting, there would be 2
records
for
that meeting in the MeetingPerson table. If 4 people attended, there
are
4
records. The MeetingPerson table allows you to record when each person
arrived, and left. This gives you great flexibility, say Trisha meets
with
DEK0703 who is a minor, and after 30 minutes the child agrees her
mother
can
join the meeting for 15 minutes, you can record this.

In the end, you have solved your problem, because the time that
*Trisha*
actually attended the meeting can be easily queried form the
MeetingPerson
table, since there are no overlapping records or duplicates (unless
someone
wrongly records her as present in 2 meetings at once, and you can warn
or
block that at the time the data is entered.)

BTW, you might currently have clients and staff in separate tables?
The
structure above requires they are all in the one table, with another
field
to distinguish the person's role.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"King" <pi*******@gmail.comwrote in message
news:11*********************@m73g2000cwd.googlegro ups.com...
Hi

I have following MS Acess query

Here is the query

ID Name Prgm ID Client ID Date Start Time End
Time Minutes
C4 Trisha TIP DEK0703 7 /7 /2006 10:00:00 AM 12:00:00 PM
120
C4 Trisha TIP DEK0703 7 /11/2006 3:30:00 PM 4:30:00 PM
60
C4 Trisha TIP DEK0703 7 /12/2006 2:30:00 PM 4:00:00 PM
90
C4 Trisha TIP DEK0703 7 /23/200 10:00:00 AM 11:00:00
AM
60
C4 Trisha TIP DEK0704 7 /23/2006 10:00:00 AM 11:00:00
AM
60
C4 Trisha TIP DEK0704 7 /24/2006 11:00:00 AM 12:00:00
PM
60
C4 Trisha TIP DEK0704 7 /25/2006 1:00:00 PM 2:30:00
PM
90
As you can see column 4 and column 5, they are for the meetings of
the
same counselor with two different clients and it should treat as one
hour but instead treats it as 2 hours ( 60 min for column 4 and 60
minutes for column 5).

SQL View of the above query

SELECT Counselor.CoID, Counselor.CoFName, Counselor.CoLName,
[Counselor
Program Administer].PrgmID AS [Counselor Program Administer_PrgmID],
[Client Program Attendance].ClientID, [Client Program
Attendance].[Session Number], [Client Program Attendance].MeetDate,
[Client Program Attendance].MeetStarttime, [Client Program
Attendance].MeetEndTime, DateDiff("n",[MeetStartTime],[MeetEndTime])
AS
Minutes
FROM (Program INNER JOIN [Client Program Attendance] ON
Program.PrgmID
= [Client Program Attendance].PrgmID) INNER JOIN (Counselor INNER
JOIN
[Counselor Program Administer] ON Counselor.CoID = [Counselor
Program
Administer].CoID) ON Program.PrgmID = [Counselor Program
Administer].PrgmID
WHERE (((Counselor.CoID)=[ Enter Counselor ID ]))
ORDER BY [Client Program Attendance].MeetDate;

Can anyone solve this.

Thanks
MaCh

[Allen Browne]

No. The fields for the Meeting table have not changed since the first reply.

It is the MeetingPerson table that has the foreign keys.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"King" <pi*******@gmail.comwrote in message
news:11*********************@h48g2000cwc.googlegro ups.com...

Do you mean the Meeting person table will have the following fields

MeetingPersonID, Client ID, Counselor ID, MeetDate, StartTime, EndTime

If it is so, dont you think there will a problem when a counselor meets
with two clients at the same time.

I am sorry if I am understood you in a wrong way. I am a student and
its my first project which is for a non profit organization.

Thanks

MaCh

Allen Browne wrote:

>One meeting contains 2 more more people.
There is therefore a one-to-many relation between Meeting and
MeetingPerson.

Each entry in MeetingPerson defines ONE of the people in the meeting.
There
are 2 records in MeetingPerson if there are 2 people at the meeting. Each
record must be fore one person only.

Whether do do that by putting all the people in one table (so
MeetingPerson
has a foreign key to the Person table), or by using separate tables for
Client and Counselor (so MeetingPerson has 2 foreign keys, and you then
have
to ensure that each record uses exactly 1 of them) is up to you.

Ultimately, you get to query the time spent by the counsellor in the
meeting
very simply, by querying their records in the MeetingPerson table.

, rather than allenbrowne at mvps dot org.

>>
"King" <pi*******@gmail.comwrote in message
news:11**********************@h48g2000cwc.googleg roups.com...

Dear Allen

Firstly thanks for your valuable time.

I wanted to clear my question now. My intention is to track the time
spent by Counselor with Clients. I also need to keep track as which
counselor is meeting with which Client.

When I run a query for number of hours spent by a Client, it works
perfect.

The problem is only when I run the query to count number of hours spent
by a Counselor.

In the case where we put a validation rule that only field cant exist,
then how will I track Counselor's Clients.

Thanks

MaCh

Allen Browne wrote:
Programs are separate entities, so it makes sense to have them in
their
own
table.

Counselor and clients are both persons. There are potentially other
kinds
of
persons you may deal with as well, such as family members of clients,
doctors at other clinics, possibly volunteers/trainees/work experience
students. I find is easiest to put all these people in one table, so
you
can
connect them up in any way you may need.

If you don't want to do that, you can keep separate tables.
In the MeetingPerson table, replace
PersonID Number which person this record is for
with 2 fields:
CounselorID Number the counsellor this record is for
ClientID Number the counsellee this record is for
and add a validation rule that exactly one of those 2 fields must
present
(i.e. any record of meeting attendence must have a counsellor or a
client,
but not both.)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"King" <pi*******@gmail.comwrote in message
news:11*********************@p79g2000cwp.googlegr oups.com...
Hi Allen

Thanks for the reply.

I have a problem if I put the Client and Counselor information in
the
same table. A Counselor adminsters programs and as of now I have
three
tables. One for Counselor, one for Adminsters and one for Programs.

If I put Clients and staff information in the same table, is it
possible for me to track Client attendance and Counselor Programs.

Please help me. Appreciate your time and work.

MaCh
Allen Browne wrote:
This is going to need a different data structure.

It appears that one meeting can involve multiple people: perhaps 2
(as
in
most of your examples), pehaps 3 (as in row 4 & 5 of your example),
perhaps
more. Your existing data structure has no way to avoid an
impossibly
complex
combination of data, with multiple partially or completely
overlapping
time
periods. It will be quite impractical to query that.

Instead create 3 tables: one for persons, one for meetings, and one
to
record the persons who attended the meeting:

Person table (one record for each person):
PersonID AutoNumber (primary key)
Surname
FirstName
RoleID Staff, client, whatever.

Meeting table (one record for any time people meet):
MeetingID AutoNumber (primary key)
LocationID where the meeting was held
SchedStart date/time the meeting was scheduled to start
SchedEnd date/time the meeting was scheduled to end.

MeetingPerson table (one record for each person in each meeting):
MeetingPersonID AutoNumber (primary key)
MeetingID Number which meeting this record refers to
PersonID Number which person this record is for
JoinTime date/time the person joined the meeting
LeaveTime date/time the person left the meeting.

So, if 2 persons attended a particular meeting, there would be 2
records
for
that meeting in the MeetingPerson table. If 4 people attended,
there
are
4
records. The MeetingPerson table allows you to record when each
person
arrived, and left. This gives you great flexibility, say Trisha
meets
with
DEK0703 who is a minor, and after 30 minutes the child agrees her
mother
can
join the meeting for 15 minutes, you can record this.

In the end, you have solved your problem, because the time that
*Trisha*
actually attended the meeting can be easily queried form the
MeetingPerson
table, since there are no overlapping records or duplicates (unless
someone
wrongly records her as present in 2 meetings at once, and you can
warn
or
block that at the time the data is entered.)

BTW, you might currently have clients and staff in separate tables?
The
structure above requires they are all in the one table, with
another
field
to distinguish the person's role.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"King" <pi*******@gmail.comwrote in message
news:11*********************@m73g2000cwd.googlegr oups.com...
Hi

I have following MS Acess query

Here is the query

ID Name Prgm ID Client ID Date Start Time
End
Time Minutes
C4 Trisha TIP DEK0703 7 /7 /2006 10:00:00 AM 12:00:00
PM
120
C4 Trisha TIP DEK0703 7 /11/2006 3:30:00 PM 4:30:00
PM
60
C4 Trisha TIP DEK0703 7 /12/2006 2:30:00 PM 4:00:00
PM
90
C4 Trisha TIP DEK0703 7 /23/200 10:00:00 AM
11:00:00
AM
60
C4 Trisha TIP DEK0704 7 /23/2006 10:00:00 AM
11:00:00
AM
60
C4 Trisha TIP DEK0704 7 /24/2006 11:00:00 AM
12:00:00
PM
60
C4 Trisha TIP DEK0704 7 /25/2006 1:00:00 PM
2:30:00
PM
90
As you can see column 4 and column 5, they are for the meetings
of
the
same counselor with two different clients and it should treat as
one
hour but instead treats it as 2 hours ( 60 min for column 4 and
60
minutes for column 5).

SQL View of the above query

SELECT Counselor.CoID, Counselor.CoFName, Counselor.CoLName,
[Counselor
Program Administer].PrgmID AS [Counselor Program
Administer_PrgmID],
[Client Program Attendance].ClientID, [Client Program
Attendance].[Session Number], [Client Program
Attendance].MeetDate,
[Client Program Attendance].MeetStarttime, [Client Program
Attendance].MeetEndTime,
DateDiff("n",[MeetStartTime],[MeetEndTime])
AS
Minutes
FROM (Program INNER JOIN [Client Program Attendance] ON
Program.PrgmID
= [Client Program Attendance].PrgmID) INNER JOIN (Counselor INNER
JOIN
[Counselor Program Administer] ON Counselor.CoID = [Counselor
Program
Administer].CoID) ON Program.PrgmID = [Counselor Program
Administer].PrgmID
WHERE (((Counselor.CoID)=[ Enter Counselor ID ]))
ORDER BY [Client Program Attendance].MeetDate;

Can anyone solve this.

Thanks
MaCh