Relating data through junction tables [and a list box problem]

[EDNYLaw]

Hello:



I have a database I've been creating that to store information about members of a job club. I'm not a programmer (I'm an attorney so this is pretty far outside my comfort zone), but I've created several databases and learned some rudimentary VBA to help with the databases. Here are all the table names and what they store:

tblMembers - Member info
tblSessions- Dates they've attended
tblEmployer - Employer info if member has been hired
tblMemberEmployer - Junction table to relate Members and Employers
tblAttendees - Junction table to relate Sessions and Members(Employer if applicable)

The problem I'm having is that, when I use tblMemberEmployer, I cannot figure out how I tell Access that I want to associate a particular member with a particular employer, if that member gets employed. Basically, on frmMembers, if the Member has been hired, I have a check box (ckHired) that, once checked, a button appears to open frmEmployer to enter the Employer information. However, I have no idea how to tag that Employer information to the particular member.

The 2nd problem I'm having is with a query for a listbox (lstMemberSession). The query (qryMemberSession) is meant to just query the member's name and date's he's attended and display that in the listbox. I wanted the query to reference txtLastName of frmMembers (where lstMemberSession is located) and base the query on the member's last name. I realize that it's probably better to have it reference pkMemberID to avoid returning results of people who have the same last name, but alas, I'm not sure how to do that. I'd like lstMemberSession to update based on whatever record is displayed on frmMembers. Right now, I can't get the query to display any results, even if I leave criteria blank.

I believe that about sums up my current issues with this database. If anyone could give me some insight as to what exactly is the problem with these two issues, it'd be greatly appreciated. Thanks in advance for the help.

Job Club Rev3.zip

[June7]

Answer to first issue depends on if each member could have more than one employer and if each employer can be associated with multiple members.

Should tblMemberEmployee be named tblMemberEmployer?

What is a session?

Why is fkMemberEmployerID in tblAttendees and not fkMemberID?

No records show in listbox because there is no record in tblMemberEmployee.

[EDNYLaw]

Answer to first issue depends on if each member could have more than one employer and if each employer can be associated with multiple members.

Each Member should only be associated with his or her Employer. Each Employer record would only be associated with one Member.

Should tblMemberEmployee be named tblMemberEmployer?

Yeah, this was a typo but since no queries or VBA code was based on it, it should affect anything. I did correct it however.

What is a session?

A session is just a meeting of the job club. This database is meant to collect information on each Member, one set of data being collected is what sessions of the job club have they attended.

Why is fkMemberEmployerID in tblAttendees and not fkMemberID?

fkMemberID is in tblAttendees, however I could not create a direct relationship from tblMembers!pkMemberID to tblAttendees!fkMemberID because it said that relationship already existed (tblMembers!pkMemberID to tblMemberEmployer!fkMemberID).

No records show in listbox because there is no record in tblMemberEmployee.

Therein lies the problem. For the life of me, I cannot figure out why nothing is appearing in this table. When I enter a new member, I would think the pkMemberID generated for that new member should then appear as fkMemberID on tblMemberEmployer, but it's not. However, the listbox is just based on a query (qryMemberSession) which is pulling data from tblMembers (First and Last Name) and tblSessions (session date) and then filtering the results based on the text contained in the Last Name field on frmMembers. But even if I remove the criteria, I'm not getting any results for the query which, given no error, should return the first and last name of every attendee of every session.

I've attached my latest version of the database Rev4 to reflect the updated table name.

Thanks for the help, it's greatly appreciated. I'm really at a loss for why some of these things aren't working.
Job Club Rev4.zip

[June7]

There is no form bound to tblMemberEmployer. Entering a record in tblMembers will not automatically cause a record to be created in tblMemberEmployer.

Surely an employer can be associated with more one employee/member? If a member will have only one employer, there is no need for tblMemberEmployer. Just put the EmployerID as a fk in tblEmployee. But if you want a history of all employers ever associated with each member, then would need the tblMemberEmployer junction table.

[EDNYLaw]

Thanks for all the help. The thing that made it "click" for me was learning that I needed a form to manage the junction table. I actually got the entire thing working now. Thanks for all the help, I really appreciate it.