adding an additional sub table

[rmayley@puroclean.com]

I have a table for differend routes I have, Each route has a subtable for the offices that have relationships to them, Each office has a sub table for the employees in the office, and lastly each employee has a notes section. I need to add another table for the date I visited to that office since I visit them monthly. What would anyone recomend for a easy way to click the route, see the offices, see the employees and notes and when I'm done visiting the office siply click a box that date stamps the office. I will need to be able to be easily reviewed to make sure I did not miss any offices also. Thanks for any ones help with this.

[Dal Jeanis]

I believe I misunderstand your design. Could you post a picture of the table relationships?
Is an office be on more than one route, or not? (I assume not)
Can an employee be in more than one office, or not? (I assume not)

Based on my assumptions, I cobbled together this theoretical set of tables:

tblRoute
   RouteID       Primary Key, autonumber
   RouteName     text

tblOffice 
   OfficeID      Primary Key,  autonumber
   OfficeName    text
   OfficeAddress text
   RouteID       Foreign key to tblRoute 

tblEmpl
   EmplID        Primary Key,  autonumber
   EmplName      text
   OfficeID      Foreign key to tblOffice 

tblEmplNotes
   NoteID        Primary Key,  autonumber
   EmplID        Foreign key to tblEmployee 
   NoteDate      Date/time
   Notes         memo

tblVisit
   VisitID       Primary Key,  autonumber
   OfficeID      Foreign key to tblOffice 
   VisitDate     Date/time
   VisitComplete yes/no
   VisitNotes    Text

The query for all employee notes on a Route would look something like

SELECT 
   tblRoute.RouteName, 
   tblRoute.RouteID, 
   tblOffice.OfficeID, 
   tblOffice.OfficeName, 
   tblEmpl.EmplID,
   tblEmpl.EmplName, 
   tblNotes.NoteID, 
   tblNotes.NoteDate, 
   tblNotes.Notes 
FROM 
   (tblNotes INNER JOIN tblEmpl ON tblNotes.EmplID = tblEmpl.EmplID) 
INNER JOIN 
   (tblRoute INNER JOIN tblOffice ON tblRoute.RouteID = tblOffice.RouteID) 
ON tblEmpl.OfficeID = tblOffice.OfficeID
ORDER BY tblRoute.RouteName, tblOffice.OfficeID, tblEmpl.EmplID, tblNotes.NoteDate 
;

That will give you all notes for each employee on each route. Call that query for example qry_AllEmplNotesonRoutes.

You could put a WHERE RouteName = "Today's Route Name" in there and you'd be able to use it to print your route report. You might need to add a "route order" field to the office record.


Here's a query that returns just the keys for the latest note for each employee. This is aircode, typed quickly, but it would be something like this.

SELECT NoteID FROM tblEmplNotes As TEN1
WHERE NoteDate = 
(SELECT MAX(NoteDate) 
FROM tblEmplNotes As TEN2
WHERE TEN1.EmplID = TEN2.EmplID);

Call that query qry_LastNote.

Something like this would get you only the latest note for each employee on today's route:

SELECT QEN* FROM qry_AllEmplNotesonRoutes AS QEN
WHERE (QEN.NoteID IN qry_LastNote)
AND QEN.RouteName = "Today's Route Name";

That's just a real kneejerk code up there, I'm sure it will need tweaked, but it should approximate your need.