Relationship problems!

[oo0tommyk0oo]

Hi all,



As in life, I am finding Access relationships troublesome and confusing....

I have created a database running off 3 tables.

1. Employees (list of employee names and there managers)
2. Mangers (list of managers and their Hierarchy)
3. Seat (list of seats and who or what sits in them)

I have 2 Query's

1. Employee - which pulls all employee data and manager data together
2. Manager - which shows all managers owned seats

I believe i have these running well, but i would like a way to show a Agile/mobile seat (a seat that more than one person sits in). We have agile areas here where Agile 1 will be a group of say 10 people sharing a group of 6 seats. I would like on my Query's for seat# to say Agile 1, if they are in agile 1, at the moment it just comes up blank.

I have attached the example which i think will better explain things

Thank you for any help.

Tom

[Rod]

I would like to help you more but the look of v2007/v2010 is so different from v2002/v2003 that pictures will only confuse you more. I suggest you appeal for someone with a compatible Access version.

However you posted about relationships. I took your database and changed it into a better design.



Attachment 3781

• The seat table now contains one meaningless primary key, one attribute for storing the seat reference and three foreign keys.
• I have assumed that any one seat may be assigned to an employee, a manager or an agile group in which case only one of the foreign keys has a value.
• You had relationships between names. Don't do that for db relationships, use the primary and foreign key structure. Relationships between names are OK for queries.
• Making the foreign key indices unique allowed me to specify one-to-one relationships between an employee and a seat, and between a manager and a seat. (If managers have more than one seat allocated then the one-to-one is wrong.)
• All other relationships are one-to-many as indicated.
• Don't use 'Name' on its own as the name of a table field. It's a reserved word and just about every Access object has a name property. Calling a table field 'Name' will cause problems later on. (I forgot to change the attribute on tblManager.)
• Remember you can change these relationships when defining queries.

OK, that's about as far as I can go without giving specific instructions and as I said at the beginning v2007 is different from v2002 in its GUI.

[oo0tommyk0oo]

Sorry for the late reply, went away this weekend,

Thank you so much for the reply, I do have office 2007 here on one of our machines, so will use that for reference.

I will see if i can recreate my database to match what you have said. Will report back.

Thank you once again.

Tom

[oo0tommyk0oo]

Hi again,

I have managed to set up the relationships as you showed and i believe i have entered the data correctly into the table. However i am having probelms running query's now.

I basically would like a query to show the basic info from each table.
-seat number
-employee name
-Manager
-Team

My query always comes back with an error.

Thank you for any help

attached is what i have so far

[oo0tommyk0oo]

1 more thing i have just noticed. With Seat. It will either have a Employee or a group of employees (agile group) actually sitting in it, or maybe empty and that employee or agile group, or empty seat will be under a manager. The relevance of this will only come later when running reports. We often need to know how many seats a manager has (empty or filled), or how many employees a manager has. Also should i repeat the managers name in employee as each manager will need a seat as well and this will need to be counted in their total.

Thank you for any help

Tom

[Rod]

Hi Tom,

I imagine that this project concerns a sports organisation and that seats are assigned to managers and employees, either as individuals or as a group. My question is do these assignations last for a season or are they different for each event (game)?

Also when you talk of empty seats, is this some kind of count of who did not use their assignation for that event? When you speak of an empty seat being under a manager (figuratively, not literally ) do you mean that the seat is assigned but empty for the event - or something else?

I know tblManager requires a little more work to build in the management hierarchy but that does not affect any part of the database.

No don't denormalise by duplicating a manager name in the employee table. The employee table already has a foreign key 'pointing' to a manager.

I think from what you say we may need another table for game or event. We also need to differentiate between the concepts of a seat being unassigned and a seat being unoccupied.

[Rod]

Just had a look at your query. The project has nothing to do with a sports organisation, does it?

The query worked fine for me. What error are you getting?

[Rod]

Hi Tom,

I took a closer look at the situation. I had to remove the default values of 0 (zero) for the foreign keys on the Seat table. I don't know whether you have defaults for other foreign keys; if so, remove them as well.

I also introduced a foreign key of ManagerID on the Mobile table. This allows for an employee to have a reporting manager and also belong to a team where the team has a different manager.

Then as a masochistic mental exercise I built a single query to achieve what I think you wanted to do. Why masochistic? Well all the columns are derived by using nested Iif functions to three levels. Here's the result.

Attachment 3820

The query design grid looks like this.

Attachment 3821

I believe this is close to what you want but I need to better understand the relationship between managers and teams.

Let me know and we can refine this.

[oo0tommyk0oo]

Hi Rob,

Thank you for your help.

The last query is very close but perhaps more complicated than it needs to be.

The seating is for an office. Basically the seats belong to certain managers and they place their employees in them. I need to keep track of each seat (who sits there if anyone, and which manager controls the seat) The manager will always be the same for the employee and seat (its just sometimes there is know one sitting there, but it will still belong to a manager)

The agile seats come in when there are more than one employee sitting in the same seat (they share) often a agile group will contain 10 employees sharing 6 seats. The manager will be the same for all employees in that group.

I hope this explains it better. I believe the 1st part has been resolved its just the troublesome agile seats.

Thank you again for any help

Tom

[Rod]

Now I think I understand. The database design may be a little off. For each seat there is an 'owner' (manager) and an optional 'occupier' (manager, employee, or agile group). I'm still wondering about teams. Can an agile group be a constituent of a team? Can an employee be: not in a team; in more than one team? Does a team have a manager per se? Might this team manager be different from the reporting manager? Are you interested in modelling any of this?

I admit I had my tongue in my cheek when I post that query. I wanted to do it all in one, however complicated. I would not consider such a query in a professional Access project. This is the reason I did not publish the SQL. A simpler and more maintainable way of achieveing the same result is to use separate queries and concatenate them via UNION.

I'll take a look today and get back to you with any recommended db changes and some thoughts on those agile groups.

[Rod]

I have made two changes to my database design.
1. I have added a foreign key to the Seat table, OwnerID. This indicates the manager who ‘owns’ the seat whether it is occupied or not.
2. I have left ManagerID on the Mobile table but deleted the relationship to the Manager table.
Attachment 3830
I think it’s time to make some observations about the design and then conduct some walkthroughs.
· Most foreign keys are optional; the one exception is ManagerID in the Employee table.
· For optional foreign keys make sure the Required property is set to ‘No.’
· For mandatory foreign keys make sure the Required property is set to ‘Yes.’
· Do not specify a default value for any foreign key.
· ManagerID exists (unrelated) on the Mobile table purely for reference and checking the consistency of your data.
· The database manager will provide a certain level of data integrity checking but there are additional business rules that you must program for yourself.
· The division of people into employees and managers is an ‘artificial’ distinction made purely for the purpose of this project. In plain English: it would simply complicate everything to combine them into one table (although I have seen it done!).

Ø For each seat it is possible to tell:
o Which manager ‘owns’ it or whether it is as yet unowned, by the presence or absence of OwnerID.
o If unowned it cannot be occupied, a business rule you must program yourself.
o If owned then it may be occupied by one and only one (another business rule) of the manager him/herself, an employee, or assigned to an agile group. The presence or absence of EmployeeID, AgileID and ManagerID indicates the type of occupancy and who occupies.
Ø For an employee:
o Each employee must have a manager. (If the set-up is correct the RDBMS will take care of checking this as it’s a mandatory foreign key.)
o Each employee may or may not be a member of an agile group given by the presence or absence of AgileID.
o If a member of an agile group the employee cannot have a specific seat. (Another business rule you must program.) The range of seats available may be determined by interrogating the Seat table through the Mobile table.
o When specifying that an employee is a member of an agile group, you can retrieve the manager from the Mobile table.
Ø For a manager:
o A list of all employees reporting to the manager is available.
o A list of all agile groups controlled by the manager is possible (and the employees in each group if required).
o A list of all seats owned by the manager is possible (including identification of her/his own seat).

I’m still worried about teams. What are they and how do they relate?

The time spent now understanding your data and relationships will be repaid later on in your project.

[oo0tommyk0oo]

Sorry it has been so long since i logged on, had to put the database on hold for a few days. But i am eager to get back into it.

What you have looks great i am just trying now to recreate it and give it a go.

In terms of teams, i believe nothing is needed. Basically Team is linked to manager. So each manager has a team. We only use Team as some people prefer to refer to teams than managers. But for the database, it would just be a field attached to the manager.

Will let you know how it goes

Thank you again for all your help

Tom