Nested queries vs. junction table

[lemonade134]

Hello, I'm creating a database in Access 2003 after not using Access for about six years. I used to use nested queries, but don't see a way to do that in the newer form of Access. Instead, the books I've consulted advise using junction tables.

What I'm trying to do is use one populated table (employee names) to interface with several other tables for different reports. The one that's giving me the most trouble is one in which I'm trying to track which employees (from employee table) met with candidates and in what role they performed (host, attendee, tour, etc.), which I believe would be a one to many relationship, although I'm not clear on that either. Each candidate can meet with up to 25 employees, but the employee names remain static, so maybe it's a 1:1?

The structure of the candidate table is something like this: Candidate, Date, Employee 1, role, Employee 2, role... I can't seem to get a report that will consolidate all of the employee names that match and then sum the function that they performed (i.e. Bob was a host six times, attendee 12 times in the year 2005.)

What would be the best way to create this? I read through many of the forums, but didn't see anything that seemed to relate.

Thank you!

[nico5038]

Hello, I'm creating a database in Access 2003 after not using Access for about six years. I used to use nested queries, but don't see a way to do that in the newer form of Access. Instead, the books I've consulted advise using junction tables.

What I'm trying to do is use one populated table (employee names) to interface with several other tables for different reports. The one that's giving me the most trouble is one in which I'm trying to track which employees (from employee table) met with candidates and in what role they performed (host, attendee, tour, etc.), which I believe would be a one to many relationship, although I'm not clear on that either. Each candidate can meet with up to 25 employees, but the employee names remain static, so maybe it's a 1:1?

The structure of the candidate table is something like this: Candidate, Date, Employee 1, role, Employee 2, role... I can't seem to get a report that will consolidate all of the employee names that match and then sum the function that they performed (i.e. Bob was a host six times, attendee 12 times in the year 2005.)

What would be the best way to create this? I read through many of the forums, but didn't see anything that seemed to relate.

Thank you!

You should "model" a table tblMeeting. This table will hold the EmployeeID, the EmployeeRole, the CandidateID and the DateTime of the meeting.
The tblMeeting is the relation table between tblEmployees and tblCandidates and all reporting can be based on this table

Getting the idea ?

Nic;o)

[lemonade134]

You should "model" a table tblMeeting. This table will hold the EmployeeID, the EmployeeRole, the CandidateID and the DateTime of the meeting.
The tblMeeting is the relation table between tblEmployees and tblCandidates and all reporting can be based on this table

Getting the idea ?

Nic;o)

So does all the data (which employees & what role they played) go in the tblMeeting or in the tblCandidate?

If it's in tblMeeting, does that mean one record per employee?

(Thank you, btw.)

[nico5038]

>So does all the data (which employees & what role they played) go in the tblMeeting or in the tblCandidate?
All goes in the tblMeeting as the meeting determines the Role of the employee and the candidate that's spoken to. tblEmployee just contains the employee data and tblCandidate just the candidate data. When the data is the same you could even consider a tblPerson and add a "Type" field to indicate "Employee" or "Candidate" .

>If it's in tblMeeting, does that mean one record per employee?
No, every meeting is recorded and the employee can have multiple meetings in multiple roles for multiple datetime frames.
When you can have a meeting between multiple employees and multiple candidates at the same time, then this will require for each employee/candidate combination one record. So a groupsession with 3 employees and 2 candidates will give 6 rows.

Getting the idea ?

Nic;o)