Enter new record in a form based on Junction Table 1

[Kysteratwork]

He all,

I have a rather frustrating problem:
1. I have two tables, one with event details (tblEvent) and another one with contact names and the respective contact details (tblContacts)
2. I have created a junction table (tblJunction), so that I can have a many-to-many relationship (i.e. one contact can take part in multiple events and one event can have multiple contacts present)
3. I created a query which contains tblEvent, tblContacts and tblJunction
And here is the thing - I cannot enter a new record on the basis of the tables (i.e. I would like to add more data in the tblJunction), which would ultimately allow me to create a form (tblsEvents) with a subform (tblContacts) that is updatable and where I can enter new records...

I am desparate, can anyone help me out please?

Thank you in advance,

Kysteratwork

 

[dhookom]

I would create a form for Events or Contacts with a subform based on the junction table. Use a combo box in the subform to select either the event or the contact. Use the Link Master Child properties to maintain integrity between the form and subform.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[willir]

Kysteratwork

Duane has steered you in the right direction.

Your subform is only based on the tblJunction. Specifically, it should have, at a minimum, two fields -- one is a foreign key pointing to the primary key on tblEvents; the other is a foreign key pointing to the primary key tblContacts. sbfrmJunction should be a Contineous form, and a Single form!

Assuming the following...

tblContacts
ContactID - primary key
ContactLN - contact last name
ContactFN - contact first name

tblEvent
EventID - primary key
EventTitle
EventDate

tblJunction
ContactID - foreign key to tblContacts.ContactID
EventID - foreign key to tblEvent.EventID

Primary key will depend. If a contact can have many events, and an event can have many contacts BUT a contact can not have the same event more than once AND an event can not have the same contact more than once, then the Primary Key could be ContactID + EventID.

If this assumption fails, then you need to differentiate the Junction record to determine the primary key. For example, perhaps[/] include the EventDate

tblJunction
ContactID - foreign key to tblContacts.ContactID
EventID - foreign key to tblEvent.EventID
EventDate

Primary key = ContactID + EventID + EventID

Or use a separate primary key independent of Contact and Event. (But becareful not to create duplicate entires!)

tblJunction
JunctionID - primary key
ContactID - foreign key to tblContacts.ContactID
EventID - foreign key to tblEvent.EventID

...Moving on
If the main form is based on tblContacts, then hide the ContactID foreign key control on the sbfrmJunction and change the JunctionID control to a combo box. The row source should be something like...
SELECT EventID, EventTitle FROM tblEvent
or
SELECT EventID, EventTitle, EventDate FROM tblEvent

The bound column will be EventID, and "hide" the column by using a 0" width.

Did I loose you on this? Okay, open sbfrmJunction open in "Edit" mode, make sure your have the Properties window open. (from the menu, View -> Properties) Select the combo box. (To change a control text box to a combo box, right click on the text box and select "Change to" and select Combo box from the pick list.) Click on the "Data" tab in the "Properties" window. Then select row source. Either type in your SQL statement or use the query builder by clicking on the "..." elipse command button to the right of the field when you select "Row source". REMEMBER - this combo box will query the tblEvents.

Now select the "Format" tab on the Property window. Column count should match the number of columns in the SQL SELECT statement defined in the Row source. Columns widths could be 0";1.5" if you just include EventID and EventTitle, or 0";1.5";0.7" if you also include the date.

To hide ContactID text control field on sbfrmJunction, select the control and set the "Visbile" value to "No" on the "Format" tab in the "Properties" window.

If your main form is based on tblEvent, then on sbfrmJunction, make ContactID into a combo box and format it to display the name of the contact, and "hide" EventID.

...Moving on to something cool.
You can either add a command button next to the combo box that opens up the corresponding form and points to the appropriate record. For example, if the main form is based on tblEvents, then sbfrmJunction will display the contacts. When the end user clicks on the command button, the frmContact opens up and displays the information for the selected contact. You can use the wizard for the commad button to perform this niffty task.

OR, you can have a corresponding single form sbfrmJunctionSingle that displays the info for the selected record on sbfrmJunction. Same idea as using the command button, but more user friendly if you have the "real estate" on the form to perform such task. This task would have be done manually since there is no wizard. Hint: You would use Me.Parent.sbfrmJunctionSingle.Requery for the OnCurrent event for sbfrmJunction.

Once you start using Many-to-Many subforms, you may find them really easy, and very powerful. I have seen so many applications ruined because the developer either did not identify a relationship properly as a M:M, or did not want to do a bit of extra work.

Hope this helps, and was not too technical.

Richard

 

[Kysteratwork]

Willir,

Thank you SO much! This really solves my problem. It is more complicated than I thought though, but it works great. I have tblEvent as the main form and tblContacts as subform. Indeed, the extra features you hint sound salivating and there will be no way around one of them. I really hope I wouldn't ask for too much when asking for your help (since I have only just started looking at the programming aspect of Access).

I have it clear in my mind, but in practice it looks more troubling:
I have a search form which I adapted to be linked to all the contacts (over 4000) (I downloaded it to my website:

http://homepages.bamboo.lu/kyster/searchdb.zip)

which I would like to launch from a Command Button in the SubForm (that's no problem). The result I click in that form would return the name and company into the SubForm, all visible, so that when the user looks up an event, he/she will have a list of the people that attended.

Richard, I know this may be pushing the limits. If I am asking too much, then don't worry, the info you have given me so far is already a huge step forward in my development and a great start into the New Year!

Christian

 

[willir]

Well Christian, first let me say that is a nice database you have there. True, only one table, but I really like the way you allow the end user to browse to find a file, and then create a hyper-link. Very nicely done. Stars for you.

...Moving on
A typo in my explaination, well one important one.

tblJunction
ContactID - foreign key to tblContacts.ContactID
EventID - foreign key to tblEvent.EventID
EventDate

Primary key = ContactID + EventID + EventDate

And let's call this table
tblAttendees
or
tblRoaster

Now, you need to answer a few questions.

What do you need to track in Events?
Do you need to differentiate the event and date, or perhaps an event with and without the date?

For example, say you are tracking attendence for training. John Smith attends the same training session in August 2004 and December 2004, or John Smith attends a program in Paris and New York.

Do you want to see all people who attended the August 2004 training session, or the Paris program? Then, do you want to see all attendees for a training program for 2003 and 2004?

And then there is the Company table. Another piece of the pie. I assume a company table will track the employees of the company.

Answers to these questions will affect the design.

Company -> Contact should be fairly straightforward.

But the rest is looking more and more like an attendence database, such as used by a school to track classes and students.

Hmmmm.

Here, an approach may work or at least give you some odeas...

tblContract
ConactID - primary key
ContactLN - contact last name
ContactFN - contact first name
CompanyCode - foreign key to tblCompany

tblCompany
CompanyCode - primary key, short text
CompanyName

Now, I will use the followig analogies...
- an event, like a school or univeristy course, and can be occur / taught more than once. But an event can either occur at different times or location, we need to have two tables, and tblEventProgram (school course) and tblEventDetail (specific class) with a one-to-many relationship
- locations, like a class room, determine the location of a specific event

tblEventProgram
EventProgID - primary key
EventProgName

tblEventDetail
EventDetailID - primary key
EventProgID - foreign key to tblEventProgram.EventProgID
EventDate
LocationID - foreign key to tblLocation.LocationID

tblLocation
LocationID - primary key
LocationName
etc

And then the table used to tract attendence or attendees...
tblRoaster
EventDetailID - foreign key to tblEventDetail.EventDetailID
ContactID - foreign key to tblContact.ContactID

You can add to this things such as...
Paid - numeric, currency
Role - role contact played at the vent
etc...

Did this help??

 

[Kysteratwork]

Richard,
I am having big smile on my face and burst with pride, thank you for the star!

The way you see it, is pretty much how I want it to look. What astonishes me is that you use many tables and link them, rather than putting all the data into only a couple of them. I already tried creating more, but the problem I face is that I have to migrate a lot of data (over 500 entries) into these meeting tables - we used to work with Excel ... Just not working anymore..
Here is the situation:
We are a Bank and we are doing a lot of roadshows every year to visit investors: on one-on-one meetings, conferences and roadshows where we meet various investors in one day, only. So, we can meet various investors on different dates and events and, indeed, sometimes the same event can be repeated in various years, but not necessarily.
The main objective is to be able to look up a meeting and instantly see who was met at the meeting and equally, create a new meeting and insert the contacts we met. My thought, therefore, Main table = the Meeting and subfrm, the Contacts...

My setup:

tblContacts
ContactsID - primary key
ContactsFN
ContactsLN
ContactsCompany
ContactsJobTitle
(and many other contact details)
For this I have created a frmContacts where I can also search the Contacts via frmSearchContacts

tblMeetings
MeetingID - primary key
MeetingDate
MeetingType (Roadshow, one-on-one, conference, etc)
MeetingNotes
MeetingLocation

Indeed, I should consider creating a separate table with more details of a specific event, good point!

tblComanyUniverse
This tbl includes a list of companies (over 5000). I still have to clean it up so that the names match the companies in tblsContacts (some are spelled differently and others are accronyms, etc)
CompanyID
CompanyName
CompanyParentCompany

Ultimately, I would like to set it up so that I can see in a form - for event X, these are the companies and the respective contacts we saw. Ah, but I want to find out more about the contact, so I double click on the contact and the frmContact[/](already created and works beautifully on its own) pops up. And as mentioned, our sales guys here have to fill in the fields after the meetings, too, so to make it simple for them, there could be a command button that pops up the same frmSearchContacts (probably easier to create a dublicate of it), which then fills out the respective fields in the subform (ContactsCompany, ContactsFN, ContactsLN, ContactsJobtitle).

So, what I created with your help so far is the tblJunction and a combo where the sales person can start typing part of the company name and then choose from the drop-down. A good start, and I would like to bring it that one step further for the sales guys to have it as easy as possible. Otherwise, I know their reaction... why not stick with our Excel... incurring change is difficult, indeed.

What you say, would you still recommend I create multiple tables (also keeping the migration aspect in mind)?

 

[willir]

Kysteratwork

Unfortunately, you are talking to a guy who is fairly big on "Normalization" although I realize that you have to some times consider common sense.

I suspect you are familiar with some of the following theory...
Fundamentals of Relational Database Design
Download document
Read on-line (HTML)


Micro$oft's answer...
283878 - Description of the database normalization basics
304467 - ACC2000 Defining Relationships Between Tables in a Microsoft Access Database

Okay, to answer your "question", you need to plan what information you need to gelam from a table.

Your design will indeed allow you to select a meeting and see who attended. Is this useful to your and colleagues? Is this all you need?

What about selecting a person and see which meeting or meetings they attended? This can be done with your design, but it would be more cumbersome.

Okay, now move on to the presenters, facilatators, etc. Do you need to determine who presented what? It would make sense to have had the meeting facilatator on the Meeting table. But, in my experience with the "big" presentations, you can have several fascilatators. Do you track this info too? If so how?

Using your basic design, consider the following "Roaster" table...

tblRoaster
MeetingID
contactID
RoasterType

Since your meeting table tracks date and location, etc, the primary key would remain MeetingID + ContactID - a person can attend the meeting once. Since your meeting table also includes MeetingType, you can still group the data in an appropriate manner. But by using one meetnig table, it also means that you may have to duplicate data -- Promotion ABC copied each time you have a meeting on such and such. This is why I included a Course and Class table, but in your case your design will work just fine.

Now by adding one field to the Roaster table, what have a done?

Well first, please realize that this is a "joiner" table used to capture many-to-many relationships. I personally like to call this type of table a "profile" table since it offers "colour" to the relationship. In this case, the "colour" is in what capacity did the contact attend the meeting -- guest, fascilatator, co-ordinator, presenter, subject matter expert, etc.

With one field, you can no capture a lot more information, and I suspect much of it useful information. (Who provided the food at such and such function, who was our subject matter expert at such and such meeting, which sale reps attended meetings on the East Coast.

...Moving on
You raise a very valid point about migrating data -- this step is often overlooked, and often people omit the step and have some person enter the data manually.

The "Roaster" table is your transaction table -- this is where most of your data entry will occur. You really do not need to populate it unless you want to capture info from past meetings.

The Contact table and CompanyUniverse will be fairly static where a simple import will work. (And yes, cleaning up date is so much fun -- with this point in mind, this is one reason why we use relational databases)

With your design, your meeting table is also a type of transaction table. You can still import it -- the only catch would be how to link it to a location if your want to "standardize" your locations.

What I do not see is how the CompanyUniverse links to the other tables. Is this tblContact.ContactCompany <-> tblComanyUniverse.CompanyID or Name?? If so, this linkage will be a bit tricky.

How does one migrate the data and maintain, or create the correct relationships / linkages?

One apporach...
After importing the raw data into your three tables, add a field to tblContacts.CompanyID. Then use an SQL company to use the Company field to find a match the corresponding Company in tblCompanyUniverse and use the CompanyID to populate the forign key field in tblContracts. OR use VBA code to loop through each contact and find the proper company info.


Presentation
I frequently include a presentation section in my analysis since the actual information is the deliverable for the database.

Displaying information in a many-to-many table is really not that hard once you have done it. The information is displayed in a subform. The suborm form (or report)is based only on the joiner table -- in this case, tblRoaster.

The foreign key in the subofrm that links to main form is hidden. The foreign key that links to the remaing table is converted to a combo box.

For example, you have frmMeetings. For sbfrmMRoaster (M = meetings), the MeetingID text box is hidden, and ContactsID control is changed to a combo box that uses something like...
SELECT ContactsID, ContactsLN & ", " & ContactsFN FROM tblContacts

The LN & ", " & FN will combine the two fields into one as "Smith, Mary"

You can spiff it up a bit by either using the double click event procedure to open up the Contact form and display the selected contact, or include a command button to do the same.

To display the above with emphasis on seeing what meetings a contact attended (or perhaps persented), reverse the process. frmContact is the main form with sbfrmCRoaster as the subform. ContactID is hidden and the MeetingID text field is changed to a combo box. To display the meeting location, date and type all based on the one field can be "fun". Using the field concatination techique I used will work, but I suspect it may be messy. Another approach would be to include text fields bound to MeetingID. (Yep, they can be combo boxes too, but then the form looks a little funny with three combo boxes) The bound field uses a function call that returns the desired component based on the MeetingID supplied. This may be a slicker approach.

This will work well for the display. For data entry, you still need to use a form and the subform based on tblRoaster would link to the other table.

A long post -- time for me to get back to work.

Hopefully, I have shown how this is do-able, and most of it is not hard at all. The driving force will be what type of information you want to feed back to your handlers.

Richard

 

[Kysteratwork]

Richard

I will have to absorb this and do some experimenting.

Some good points reg the objectives of the db. Indeed, I have made the frmContacts to also include a field where the meetings held with the person are detailed (and clickable)... but this field right now is empty and hypothetical, since I first have to figure out how to do the frmMeetings and how to structure it.

I will work hard to get it running, since now I already spend so much time on it, I really want to finish it... problem is, of course, that this is not my main task here at work (actually it very trivial for many people, but that's becuase they don't see how powerful a tool Access can be)...

I will revert to you, if you don't mind, should I get completely lost...

So far, you really gave me some VERY useful insights. I appreciate the time you took for it!

Christian

 

[Kysteratwork]

Richard,

I am back - I have been working a lot on my project and it's looking extremely good (collect astonishment from colleagues). I have taken up your suggestions and normalised the tables to a maximum and now understand the advantages!

I do have a question, though, particularly with regards to my search form (at

http://homepages.bamboo.lu/kyster/searchdb.zip)

I hope you can give me a tip on how to proceed, since I will apply the same principel to various forms.

The situation:
I have a form with meetings (frmMeetings) held by colleagues (detailed in subform subfrmEmployees) and detailing Meeting Attendees (subform subfrmAttendees).

I would like to use above frmSearch to return the name of an Attendee in the subfrmAttendees.

Right now, the code looks like this:

Private Sub lstCustInfo_DblClick(Cancel As Integer)
'Open frmCustomer based on the ID from lstCustInfo listbox

DoCmd.OpenForm "subfrmAttendees", , , "[ID] = " & Me.lstCustInfo
DoCmd.Close acForm, "frmSearch"

End Sub

... but of course, it merely opens the subform without the main form. I guess, I somehow have to indicate that it should return the name to frmMeetings (the main one) and then refer to the empty field in the subfrmAttendees.

You see what I mean? Can you help me with that?

Chris