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!
3 2172
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)
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.)
>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)
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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,...
|
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
...
|
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...
|
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...
|
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
...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
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...
|
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....
|
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...
| |