Table Relationship Help

[RainLover]

In post number 6 there is a different way of naming the Primary and Foreign Keys.

I am going to change it in the demo for you to see.

 

[RainLover]

Here is some urgent reading for you.

http://access.mvps.org/access/lookupfields.htm

One does not have lookups designed into tables. It causes you to see the wrong thing.

It is fine to have them in forms where they are most useful.

Have a read of the link it should explain things for you.

 

[RainLover]

That is all from me just now.

I have attached a new layout which still requires some modifications.

Going to have some sleep before continuing.

 

[GendoPose]

Thank you for all the help so far, I've had a look through example and I can see where you've changed things and after reading that webpage I can see why as well.

I'll have a little look through again and try designing these forms based on this information.

 

[RainLover]

How are things going?

Have you made any progress?

Now that you are beginning to understand how one table is linked to the other and how to use a combo box in a form. You should now be writing your Table Design.

Perhaps you should find what the Main information is. Build your first table around it and then keep expanding.

 

[GendoPose]

How are things going?

Have you made any progress?

Now that you are beginning to understand how one table is linked to the other and how to use a combo box in a form. You should now be writing your Table Design.

Perhaps you should find what the Main information is. Build your first table around it and then keep expanding.

I'm getting there I think, I've been creating queries to show the information I want and then basing the forms on these queries. The only problem I can see so far is if I want to show multiple roles and locations without turning it into a multiselect lookup field, which defeats the object of rearranging the table relationships in the first place.

EDIT; actually that is my next question, how can I make the Roles and Locations box into a multi-select combo box to show more than 1 Role/Location without changing the table structure?

 

[RainLover]

You have a form named frmSubUsers.
So following the convention I posted earlier the Query to it should be named qryFrmSubUsers.
You have a bunch of tables when you only need One. I have made some adjustments for you to look at.

I got to this point and realized that there are a lot of mistakes in the design.

If the Main Form is about users then the sub form could be about the Teams the user is in or has been in.

If the Main Form is about Teams then the sub form could be about the user.

Have a look and see what I mean.

 

[RainLover]

I have attached a sample database that shows how to have a sub form which is a one to many relationship.

It also has a form that demonstrates what a Many to Many is. Actually it is two forms.

Hope this helps some more.

 

[GendoPose]

I have attached a sample database that shows how to have a sub form which is a one to many relationship.

It also has a form that demonstrates what a Many to Many is. Actually it is two forms.

Hope this helps some more.

Thanks for your help, I've actually managed to create subforms within the 2 main forms, but I will definitely take a look at your example, however I can't see any attached files to your last 2 posts?

 

[RainLover]

Sorry about the missing attachment.

I will try again.