Logging Management Visits

[BetaMike]

Hi all,
I have been asked to record Management visits to Employees that work
off-site. Our business rules state that each Employee is visited at least
once per month but this doesn't always happen in real life. The Bosses want
to view a monthly report that shows who has and who hasn't been visited along
with the names of the Managers that are not performing the required number of
visits each month...

I'm thinking along these lines: -

tblManagers
IDNo (pk)
Name

tblEmployees
IDNo (pk)
Name
SiteLocation
Jan (tick box)
Feb
Mar
Apr
May etc. etc.

tblVisits
ID (Autonumber) (pk)
Date
Time

I can't get my head out of 'Excel mode' with this one so I would appreciate
your comments/suggestions

Thanks.

 

[John Spencer]

I would suggest that you really need two tables

TblEmployees
IDNo
Name
SiteLocation
ManagerID

TblVisits
IDNo
EmployeeID
ManagerID
VisitDate

You could have a third table for managers, but since managers are employees
they normally should be in the employees table.

Those tables should enable you to get all the information you need.

 

[BetaMike]

Thanks John, I should have explained it a litte better in my first post...

I think I will need a 'Many to Many' relationship, as any Employee can be
visited by any Manager and any Manager can visit any Employee.

However, you are correct in saying that the Managers are already listed in
tblEmployees.

Thanks.

 

[BetaMike]

Well, I finally managed it by adding a Managers table as I couldn't get the
relationships to work when using tblEmployees twice over.

I then experimented with Crosstab query's to get the results I wanted. Just
like a spreadsheet Except for the missing Sites, Employees and Managers

Is there a way of including empty rows in the Crosstab? I can find them
using the 'Find Unmatched Query Wizard' but this then seperates the data into
two separate queries - Any suggestions?

Thanks.

 

[John Spencer]

Probably, it could be as simple as changing a join, but without your current
SQL and/or some idea as to your table structure it is difficult to give you
any advice beyond changing the join types.

 

[BetaMike]

Thanks for replying John,
tblEmployees has a '1 to Many' relationship with tblLinkEmployeesManagers
tblManagers has a '1 to Many' relationship with tblLinkEmployeesManagers
Therefore tblEmployees has a 'Many to Many' relationship with tblManagers.

tblEmployees
EmpID (PK)

tblManagers
MgrID (PK)

tblLinkEmployeesManagers
VisitID -autonumber- )PK)
EmpID (FK)
MgrID (FK)
Date
Comments

The problem I have is that the Crosstab query will only fetch an EmpID if it
has a matching record in tblLinkEmployeesManagers. So, I have a 'hidden' list
of EmpID's that don't show up at all. Any advice on the above would be much
appreciated

Thanks.

 

[John Spencer]

QueryOne saved as qMeetings
Parameters [Start Date] DateTime, [End Date] DateTime;
SELECT E.EMPID, EM.MgrID, EM.Date, EM.Comments
FROM TblEmployees as E INNER JOIN tblLinkEmployeesManagers as EM
ON E.EmpID = EM.EmpID
WHERE EM.Date Between [Start Date] And [End Date]

QueryTwo:
SELECT E1.EmpID, Q.MgrID, Q.Date, Q.Comments
FROM tblEmployees as E1 LEFT JOIN qMeetings as Q
ON E1.EmpID = Q.EmpID

Since I don't know what you did in the crosstab query, this is as far as I
can go.

 

[BetaMike]

John, I don't understand any of it but it works so that's the main thing

Thanks.

QueryOne saved as qMeetings
Parameters [Start Date] DateTime, [End Date] DateTime;
SELECT E.EMPID, EM.MgrID, EM.Date, EM.Comments
FROM TblEmployees as E INNER JOIN tblLinkEmployeesManagers as EM
ON E.EmpID = EM.EmpID
WHERE EM.Date Between [Start Date] And [End Date]

QueryTwo:
SELECT E1.EmpID, Q.MgrID, Q.Date, Q.Comments
FROM tblEmployees as E1 LEFT JOIN qMeetings as Q
ON E1.EmpID = Q.EmpID

Since I don't know what you did in the crosstab query, this is as far as I
can go.

Thanks for replying John,
tblEmployees has a '1 to Many' relationship with tblLinkEmployeesManagers
tblManagers has a '1 to Many' relationship with tblLinkEmployeesManagers
Therefore tblEmployees has a 'Many to Many' relationship with tblManagers.

tblEmployees
EmpID (PK)

tblManagers
MgrID (PK)

tblLinkEmployeesManagers
VisitID -autonumber- )PK)
EmpID (FK)
MgrID (FK)
Date
Comments

The problem I have is that the Crosstab query will only fetch an EmpID if
it
has a matching record in tblLinkEmployeesManagers. So, I have a 'hidden'
list
of EmpID's that don't show up at all. Any advice on the above would be
much
appreciated

Thanks.