Table Relationship Help

[GendoPose]

Hi all,

I have 2 tables, one called Users and one called Teams.

Users has these fields;

ID
FullName
Role
CurrentTeam
PreviousTeams

Teams has these fields;

ID
TeamName
Location
CurrentMembers
PreviousMembers

Role and Location are both lookups from other tables, named respectively.

What I'd like to do is when I update the CurrentTeam in the Users table, it updates the CurrentMembers in the Teams table, e.g I change UserA's current to TeamA, I want the record for TeamA to show UserA in CurrentMembers.

The reason I have 2 seperate tables like this is because there are 2 seperate forms, one to show teams and one to show users, so people can see what User is working on what project in one team, and what team currently has what Users working on that project.

Any help at all?

Thanks guys.

EDIT; Also should mention, they need to be multiple value fields, as they can be in more than one current team/previous team, and each team will have more than one current/previous user.

 

[CJ_London]

you need to normalise your tables - something along these lines

Users
UserID
FullName
Role

Teams
TeamID
TeamName
Location

CurrentTeam
ID
TeamID
UserID

what User is working on what project in one team

You dont have a project field at the moment so where it goes depends on whether projects are specific or not to one team

 

[plog]

That's a lot of fundamental errors you want to commit.

First, distinct data should be stored distinctly. If you want to store multiple values in a single field, you are doing it wrong. Usually it means you need a new table, and that's true in your case.

Second, you shouldn't store calculated values in a table, instead you should calculate them in a query. That is exactly what current and previous data are--calculated.

Together, those mean your table structure should look like this:

Users
UserID, FirstName, LastName, Role,
2, Steve, Jones, Center
5, Jim, Bell, Forward
17, Larry, Smith, Guard

Teams
TeamID, TeamName, TeamLocation
14, Lakers, Los Angeles
19, Celtics, Boston

TeamMembers
TeamID, UserID, CurrentMember
14, 2, True
19, 2, False
14, 5, True
19, 17, True


Then you use queries to determine that Steve and Jim are currently with the Lakers, Larry is with the Celtics and Steve used to play with the Celtics.

You might want to think about moving the Role to the TeamMembers table, and possibly using a datefield instead of just the Yes/No field for CurrentMember.

 

[GendoPose]

you need to normalise your tables - something along these lines

Users
UserID
FullName
Role

Teams
TeamID
TeamName
Location

CurrentTeam
ID
TeamID
UserID

You dont have a project field at the moment so where it goes depends on whether projects are specific or not to one team

The project won't be entered into the table I think, it's just to divide up users and teams to give an overview of each.

Now I have the following tables;

Team, User, CurrentTeam, PreviousTeam, each with the structure as outlined above, where do I go from here?

 

[RainLover]

How about you create your tables and their relationships.

Once you have done that then post a pick of the relationships so we can get the design just right before proceeding.

 

[RainLover]

I would also suggest that you learn and apply a naming convention. This will help explain what is what when we look at the database for the first time.

It will also help you if you have to come back in a year's time to fix something.

 

[GendoPose]

That's a lot of fundamental errors you want to commit.

First, distinct data should be stored distinctly. If you want to store multiple values in a single field, you are doing it wrong. Usually it means you need a new table, and that's true in your case.

Second, you shouldn't store calculated values in a table, instead you should calculate them in a query. That is exactly what current and previous data are--calculated.

Together, those mean your table structure should look like this:

Users
UserID, FirstName, LastName, Role,
2, Steve, Jones, Center
5, Jim, Bell, Forward
17, Larry, Smith, Guard

Teams
TeamID, TeamName, TeamLocation
14, Lakers, Los Angeles
19, Celtics, Boston

TeamMembers
TeamID, UserID, CurrentMember
14, 2, True
19, 2, False
14, 5, True
19, 17, True


Then you use queries to determine that Steve and Jim are currently with the Lakers, Larry is with the Celtics and Steve used to play with the Celtics.

You might want to think about moving the Role to the TeamMembers table, and possibly using a datefield instead of just the Yes/No field for CurrentMember.

They're not calculated, there's no calculation involved here at all. Also, Current and PreviousMembers aren't yes/no fields, they're fields with the names of Users in that show who was and currently is in the team.

 

[GendoPose]

These are my table relationships.

 

[plog]

Calculated doesn't just mean numbers. Anything that is discernible from the data you have, but not technically part of your data is calculated. Give me 5 siblings and their birthdates and I can calculate who the middle child is, I don't need to store that specific piece of data.

Which means you don't need that CurrentTeam table. You just need a team table and something to designate if it is a current team or not (Yes/No field or a datefield).

 

[GendoPose]

Calculated doesn't just mean numbers. Anything that is discernible from the data you have, but not technically part of your data is calculated. Give me 5 siblings and their birthdates and I can calculate who the middle child is, I don't need to store that specific piece of data.

Which means you don't need that CurrentTeam table. You just need a team table and something to designate if it is a current team or not (Yes/No field or a datefield).

Ok, that's fair.

It's not about whether this team is current though, it's about what User is in that Team currently, so a list of users that are.

 

[GendoPose]

So far, my table relationships and query designs are like this;

However, when I uncheck for example UserA in the CurrentTeams table for TeamA, when I go onto the CurrentUsers table, it still shows UserA having TeamA checked in the CurrentTeam list, which is what I was trying to get around in the first place.

Also, my querys are now the datasources for my forms, but I can't udpate any of the information through the forms, why's that?

 

[plog]

I'll try again: You shouldn't have that many tables. You should have the 3 I identified in my first post. All of your 'Current' and 'Previous' tables are unnecessary. Likewise, your Roles and Locations tables aren't necessary either. You need just teh 3 tables I described in my first post.

 

[RainLover]

Your use of ID as the name of a PK does not really spell out which table it is the primary key of.

The Four Tables in the Middle are wrong but the tables on the left and right are correct.

I am referring to your snap of the relationships.

 

[GendoPose]

I seem to be getting 2 people telling me 2 different ways to go about this.

I'll try again: You shouldn't have that many tables. You should have the 3 I identified in my first post. All of your 'Current' and 'Previous' tables are unnecessary. Likewise, your Roles and Locations tables aren't necessary either. You need just teh 3 tables I described in my first post.

So how would you do it if I delete the Current and Previous tables and then move Roles and Locations to the Users and Teams tables?

Your use of ID as the name of a PK does not really spell out which table it is the primary key of.

The Four Tables in the Middle are wrong but the tables on the left and right are correct.

I am referring to your snap of the relationships.

I've now changed my table ID's to these:

 

[RainLover]

I seem to be getting 2 people telling me 2 different ways to go about this.

So how would you do it if I delete the Current and Previous tables and then move Roles and Locations to the Users and Teams tables?

I've now changed my table ID's to these:

GendoPose

I have been keeping my remarks mainly to the naming of your objects.

For now I will leave the design advice to Plog.

One suggest is in tblUsers FullName. Most people use two fields. FirstName and LastName. This allows sorting and filtering to be done on either field. Which most likely would be the LastName.

GendoPose, It is worth the trouble of spending extra time on getting this right. If you do you will save a lot of time and heartaches later.

 

[GendoPose]

GendoPose

I have been keeping my remarks mainly to the naming of your objects.

For now I will leave the design advice to Plog.

One suggest is in tblUsers FullName. Most people use two fields. FirstName and LastName. This allows sorting and filtering to be done on either field. Which most likely would be the LastName.

GendoPose, It is worth the trouble of spending extra time on getting this right. If you do you will save a lot of time and heartaches later.

Sorry, I was getting you confused with CJ London who made the suggestion earlier.

I've tried following Plog's design and now I have the TeamMembers table, with TeamID, UserID and CurrentMember as Yes/No. I will change this to a date field eventually when I've worked around this.

I've then created the 2 queries to show CurrentTeamMembers and PreviousTeamMembers, which show the TeamID, FName, LName, Role and CurrentMember, both with criteria as True or False on the CurrentMember depending on the query.

How would I then put these into 2 forms, one that shows the Users and their Current/Previous teams and another that shows Teams and their Current/Previous members?

 

[RainLover]

GendoPose

Is it possible for you to post a copy of your Database so I can have a closer look.

If you can then please do so but it must be in Access 2003

I cannot view later than 2003.

 

[GendoPose]

GendoPose

Is it possible for you to post a copy of your Database so I can have a closer look.

If you can then please do so but it must be in Access 2003

I cannot view later than 2003.

Here you are, thank you for this

 

[RainLover]

GendoPose

I have some things I have to do.

Will get back to you in a couple of hours.

 

[GendoPose]

GendoPose

I have some things I have to do.

Will get back to you in a couple of hours.

Ok, thank you