junction questions

[Jessica]

Hello All,


Ok I think I'm getting this many to many relationships but I just have a
few questions. I setup a form using my main table which has a primary
key that I type in a UPC number, on that form I have a subform which I
am using a junction table that refers to my main table and another table
called Case Stickers. My subform shows two fields UPC and Case Sticker
PK Number. My first question is...

1. Before I've heard abount junction tables I had two fields in my main
table called case sticker 1 and case sticker 2. Now that I have set up a
junction table can I delete them two fields?

2. Everything is working but do I really need to type in the UPC again
since I have to type it in on the main form?

3. This may be a stupid question but when I layout a report to I grab
the UPC field from the main table or the junction table.



TIA,
Jass

 

[Graham Mandeno]

Hi Jass

Answers inline...

Hello All,


Ok I think I'm getting this many to many relationships but I just have a
few questions. I setup a form using my main table which has a primary key
that I type in a UPC number, on that form I have a subform which I am
using a junction table that refers to my main table and another table
called Case Stickers. My subform shows two fields UPC and Case Sticker PK
Number. My first question is...

1. Before I've heard abount junction tables I had two fields in my main
table called case sticker 1 and case sticker 2. Now that I have set up a
junction table can I delete them two fields?

Yes, any case sticker fields in your main table are now obsolete. Make sure
you have created junction table records for each of then before you delete
them though!

2. Everything is working but do I really need to type in the UPC again
since I have to type it in on the main form?

No, this is not necessary. You need to set up the "link fields" between
your main form and your subform. With the main form in design view, select
the subform control (click on the border of the box containing the subform)
and show the properties sheet.
Set LinkMasterFields to the name of the UPC field in your main table, and
set LinkChildFields to the name of the UPC field in your join table.
Then the UPC will automatically be filled in for the join records. You can
even delete the UPC textbox from the subform, as it will always show the
same value anyway.

3. This may be a stupid question but when I layout a report to I grab the
UPC field from the main table or the junction table.

You would base a report on a query containing all three tables (main table,
junction table and stickers table) joined on their related fields. It
doesn't really matter which of the UPC fields you use, because in any given
row they will both be the same.

 

[Jessica]

Thank you so much Graham it worked beautifully. I know how to make
lookup fields in tables could I make one for the Case Sticker field in
the subform so that the use could select from the
case Sticker table.

Thanks again,
Jess

 

[Graham Mandeno]

Hi Jess

Actually, making lookup fields in tables is NOT a good idea. besides the
fact that tables are for storing data, not for looking things up, they are a
suspected cause of database corruption.

However, it is a VERY good idea to use combo boxes on forms to look up
related values, and this is what you should be doing here.

Bind the combo box (ControlSource) to the StickerID field in your junction
table and make the RowSource of the combo box a query returning the
StickerID (column 1) and the StickerName (column 2). Set the following
other properties:
ColumnCount: 2
BoundColumn: 1
ColumnWidths: 0

This will hide the ID column and show you the related text.