473,326 Members | 2,173 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Nested queries vs. junction table

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!
Oct 12 '07 #1
3 2172
nico5038
3,080 Expert 2GB
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)
Oct 12 '07 #2
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.)
Oct 12 '07 #3
nico5038
3,080 Expert 2GB
>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)
Oct 12 '07 #4

Sign in to post your reply or Sign up for a free account.

Similar topics

12
by: Jeff Lanfield | last post by:
First of all, I apologize if coalescing is not the right term to describe my problem. I have a tree where each node has the same set of attributes (is the same entity) but child nodes should...
1
by: mksql | last post by:
As an example, I am building an authentication mechanisim that will use data in the 3 left tables to determine rights to objects in a destination table, diagrammed below. In this structure,...
5
by: ahokdac-sql | last post by:
Hi, I'm adapting access queries to sql server and I have difficulties with the following pattern : query1 : SELECT * FROM Query2 WHERE A=@param1 query 2: SELECT * FROM Table2 WHERE B=@param2 ...
3
by: Tcs | last post by:
My backend is DB2 on our AS/400. While I do HAVE DB2 PE for my PC, I haven't loaded it yet. I'm still using MS Access. And no, I don't believe this is an Access question. (But who knows? I...
5
by: Jerry Hull | last post by:
I'm working with a database developed by an untrained person over several years - and on a network that has recently been upgraded with a new server installed and MS office upgraded from 2K (I...
1
by: que576 | last post by:
I have created a junction table so that I can relate data from 2 other tables. Table 1 - Category Table (has the following fields with data) CatID (primary key) CategoryName CategoryStatus ...
2
by: Henry Stockbridge | last post by:
Hi, I need a recommendation when to add a record to a junction table that complements a many to many relationship. There will be a Contacts form, and an Interests subform with the parent/child...
1
by: bg_ie | last post by:
I'm designing a database with 3 tables called Function, Test and Scene. A Function has multiple Tests, but a Test has only one Function. A many to many relationship exists between Test and Scene...
3
by: dbertanjoli | last post by:
Hello, I have a questinarrie webform I use INSERT statement(s) to insert a new record in the User and Questions tables and then (HERE IS MY PROBLEM) I need to update my junction table...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.