Screen view of Database Diagram (http://64.122.37.190/images/diagram.jpg)

Screen view of problem view (http://64.122.37.190/images/viewshifts.jpg)

I am working with a Access Data Project and MSDE database server. In the image you can see the tables that I am working with. I have a table, tblShifts, that has all of the shifts with clock in and out times for all buildings. I am trying to downsize this list to include only buildings that the user is matched with in tblManagerBuildings by using SUSER_SNAME() to get there current username. Seems easy enough.

Only I can't get it to work. It does give you only shifts in the buildings that you are assigned to. But when you go to delete or insert wierd things happen. If I have the query set up as it is in the "Screen view of problem view" link I get an error "The field 'shiftnum' is read-only" when I try to insert or delete. So I tried another way. First I set up a view that gives you all of the buildings that you have access to.

View all of the user's buildings (http://64.122.37.190/images/viewMyBuildings.jpg)

Then I connect that to tblShifts and get all shifts in the user's buildings:

Shift table connected to user's buildings (http://64.122.37.190/images/viewShifts2.jpg)

If I output the BuildingNum column when you go to delete a record nothing happens. If I output the username column I get the "The field 'shiftnum' is read only" error. If I don't output any of the columns from viewMyBuildings then when I delete a row it Deletes the Building and the User from ALL of the tables. Including all of the shifts associated with the building. AWWW! I know that this is possible. My problem is probably with having a many - to - many relationship, but I have been working on the for a day and a half and I think I took care of the many to many relationship properly. If anyone has any input PLEASE HELP!

Your structure does look correct. Why are the user and employee tables separate? The link from shift is to employee but you are trying to get to user and there is no path to user from shift.

Users are seperate from employees. The username will match the Windows Authentication username. The users will be managers that aren't in the employee database because they don't clock in or out. I could combine them but there are going to be 150 employees and under 5 users.

Let me say it another way - shifts have nothing to do with users - that means that they don't belong in the same query. You have two separate 1-many relationships that you are trying to combine in a single query (shifts and buildings) and (buildings and users). The result of this is a cartesian product which is not updatable.

Thanks for the input. That makes sense. Can you think of a better way to structure this? I tried connecting tblMangerBuildings.BuildingNum to tblShifts.building thinking that this would connect the shift to the user but it had the same results.

As a workaround I created a trigger that set a flag column tblShifts.deleted to 1 for the row being deleted then ran a sql command that deleted all rows where tblShifts.deleted is equal to 1. The workaround seems to do the job.

Thanks Again

The problem is with the logic of the relationship. It doesn't make sense to me to have user control deletes from shift. since many users can be assigned to the same building, why would you want to perform the deletes with that criteria? If Sam and Dave are both users assigned to Graceland why would you want Dave to delete records of the shift workers managing the property? What will Sam think when he logs in and there are no shift records for him?

That is a good point, in most cases that would make sense. But I think it does in ours. There are 4 people involved, building manager, operation manager, general manager and systems administrator. The mass majority of the time the building manager will be the only one making changes to the building. The administrator needs to be able to make changes because they do payroll. The operations manager needs to be able to step in and cover for the building manager. Even though the general manager will only be viewing the reports they are not a security risk as they are the senior employee. So in our case it seems that there will always be at least 3 people that need to be able to make changes. Let me know if this makes sense or if in your opinion it would be better in the long run to create 3 seperate interfaces, or maybe I don't need to and there is a better approach. I am fresh out of college so I don't have a lot of experiance in what works in the long run.

Modifying data is one thing, deleting records via an illogical path is another. Why would you delete time records anyway? Wouldn't they logically be archived after a certain point? And the archiving wouldn't have anything to do with a user, it would have to do with a timeframe and be semi-automated. No one but the employee himself should ever be allowed to modify time in/out records. And even the employee should only be allowed to change the record until the close of the period whatever that might be in your application. I assume these hours worked records are used to calculate payroll. That requires a lot more structure/security than most other applications.