Junction Tables

[music_al]

OK, now Im converted about the evil of Lookup Fields and I will NEVER use them again.

But, if Im using a Junction tables do I use the Primary Key of the tables or the linked fields ?

So, if I have

tbl_Club
Club_ID
Club_Name

tbl_Members
Member_ID
Member_Name

Do I use...

JnTbl_Club_Members
Club_ID_FK
Member_ID_FK

OR

JnTbl_Club_Members
Club_Name_FK
Member_Name_FK

 

[gemma-the-husky]

the first

you use the index - because that wont change - but in your query, you can link based on the index, and include the name

one reason is that its more efficient for access to use long numbers to link tables.


the other thing is that this way, you can just change the name if it was entered incorrectly, without causing any issues

although you can have cascading updates to do this - its not a great idea - its not instant, and may go wrong

 

[music_al]

How does this attachment look ??

 

[gemma-the-husky]

your attachment IS correct - its not a bad thing to store the keys - although it does mean you as designer need to understand whats going on, But it shouldnt matter ot your users, because they should never need to see a table directly.

doing it this way, means that if you change the name Tennis, to Lawn Tennis in the base table - thats all you need to do - one change - your forms etc will pick up the change automatically

The trouble is, if you design the junction table with lookup fields, then when you open the table, instead of seeing the stored values (1,2 etc) you see the looked up value - try it and see.

so if you do that, it can make it hard to see exactly what is going on in the database. It also means that every time you put that field on a form, access automatically gives you a combo box - which MAY be what you want, but it may not be.

Because access is doing things for you automatically, you can start to lose track of your data representation, which may lead to problems in a big system.

The underlying idea is that the data storage ought to be really divorced form the data presentation, wihch is why it makes sense not to use these "helpful" facilites.

Another one is the caption property in a table - which then instead of using your fieldname, applies a different field name as standard in your queries, and on your forms. But try using that one, and then see how awkward it quickly becomes.

------------
Note that in your form, the data is used directly from the tables, so it is sorted in the order of the primary key in the table. If instead you base it on a query, you can sort the data, so that the names appear in alphabetical order, say. And this is where you get more control - because you can determine to present the names in the way YOu want

eg
A. Surname or
Albert Surname or
Surname, Albert

and sort them in the order you want.

And you can add extra columns to the combo box to show other stuff assocaited with the person.

Using a stored query also makes it easier to use a standard within your app, as comoboboxes on different forms can all use the same query.


Hope this helps

 

[music_al]

Thanks Dave

Just one questin though. I dont really see why its a good idea to represent data on a form trough a query. I have managed to sort data on a form in the past by just selecting in that control and clicking the sort button.

Also, I didnt think a query could be edited to update the table its taken from.


Al

 

[georgedwilkinson]

I dont really see why its a good idea to represent data on a form trough a query.

You get a little better control with a query. Some IT departments won't allow Access developers to go directly to a table as a record source.

I have managed to sort data on a form in the past by just selecting in that control and clicking the sort button.

That is a valid thing to do if you're sorting data displayed in the form. Microsoft spent bazillions of dollars putting in all kinds of good functionality and I think we should use it where it makes sense.

Also, I didnt think a query could be edited to update the table its taken from.

Yes, it can...as long as it is up-datable. Check out the infamous Allen Browne's take on the up-datability of data: http://www.allenbrowne.com/ser-61.html

 

[georgedwilkinson]

I'd like to re-iterate that nothing I said in post #6 contradicts Dave's (AKA Gemma-the-husky) advice.

You get more control with queries AND a user can choose to sort the form differently later.

I think the thing Dave is advising you against using is lookups at the table level (correct me if I'm wrong), in fact, doing any presentation work at all at the table level. I have done it in the past and it quickly becomes a pain when things don't look like you think they should look or behave the way you think they should behave.

 

[gemma-the-husky]

George

yes, thats what i was saying - in fact music-al started this thread by saying he wouldn't be using lookups in tables, and posted an example database demostrating how he was using FKs.