One project, multiple employees - one query result.

[rclarke]

Hi Folks,

I tried to explain my problem previously, but from a different angle and think I didn't get across what I needed to do properly.

I'm at a bit of a dead end with my database. I want to use a query to collate information from two tables so I can export single records to a word document.

I have two tables, one for my projects and one for all departmental employees.

I want to create a query that with one row that will provide all relevant project info (name, start date, completion date etc) from the first table along with the contact details of multiple individuals from the employee table.

Is it possible to design a query to do this when details are returned from multiple records within one table, rather than four separate and related tables?

How can I return four sets of employee details within one project record on a query?

I have tried many methods to get this to work and think I am approaching it in the wrong way as nothing I have tried seems to work.

 

[jdraw]

It sounds like you have a design issue, but I don't know your situation.

If you have many people and many projects, it seems you would have to identify who is working on which project and would need a table , such as

PersonWorksOnProject

To resolve many to many relationships, you use a junction table.

see this free video tutorial
http://www.youtube.com/watch?v=7XstSSyG8fw

 

[Alansidman]

Addditionally, if you provided the tablenames and fieldnames associated with each table, the primary keys and foreign keys it might be helpful to understand your situation. Further if you were able to post a sample copy of your db with sample data, that would help also.

 

[rclarke]

Thanks all for your replies.

Yes there are many people associated with many projects, I think that's where I have been going wrong. I have tried to upload the database to the forum with no luck, will have to wait till I get back from work.

I have 2 tables.

tblTrial (my projects are clinical trials)
tblStaffDetails (list of all staff and contact details)

There are 4 roles associated with each trial;
Investigator
Coordinator
Nurse
Data Entry

I haven't gotten as far as setting up a many to many junction yet, though it does sound like the right way to go.


I want to create a query that associates each of these names and their contact details to one record. How would I go about assigning a member of staff to one of these roles in the tblTrial table, and then providing their contact details so that all the trial details and contact information appear on only one query record, ready for exporting into a word mail merge.

I'll try upload the database in an hour or two so you can see what I mean.

 

[boblarson]

I have tried to upload the database to the forum with no luck,

Have you

1. Run Compact and Repair first?
2. Zipped the file (right click on the file and select SEND TO > COMPRESSED FOLDER?

 

[jdraw]

I see 4 tables

tblTrials (trialId,trialName,.. other trial specific info)
tblStaff (staffId,staffFirstName, staffLastname,...other staff info)

tblRoles (RoleId, RoleName..)
and a junction table with a composite unique index
the junction table would be something such as tblTrialRolePerformedBy


tblTrialRolePerformedBy (TRPId,TrialdID FK,RoleId FK, StaffId FK,...other info specific to this relationship)


In this table TrialId,RoleId and StaffId would form a Unique Index to prevent duplicates.

The underlined field is the PK in the associated table.

 

[rclarke]

See attached.

You will see the queries that I tried to create in order to assign staff to a role within each trial. This was a work around to get each employee within one record but hasn't been working for a while.

Any tips would be greatly appreciated, thanks for looking.

 

[rclarke]

I see 4 tables

tblTrials (trialId,trialName,.. other trial specific info)
tblStaff (staffId,staffFirstName, staffLastname,...other staff info)

tblRoles (RoleId, RoleName..)
and a junction table with a composite unique index
the junction table would be something such as tblTrialRolePerformedBy


tblTrialRolePerformedBy (TRPId,TrialdID FK,RoleId FK, StaffId FK,...other info specific to this relationship)


In this table TrialId,RoleId and StaffId would form a Unique Index to prevent duplicates.

The underlined field is the PK in the associated table.

Thanks, I'll try replicate this and report back how it works out.

 

[jdraw]

Please see the proposed tables in post #6.

It is preferred to get your tables and relationships designed, and tested with some test conditions (good and bad) before getting too deep into the "details of Access/queries/vba etc".

Good luck with your project.

 

[rclarke]

Thanks Jdraw, very helpful stuff here. Now I'm left scratching my head, how can I structure a form that allows the user to select a member from my table of staff and assign to one of the four roles I have created.

I imagine it involves sub forms... and I want to do this from the main trial data collection form.

The only way I can see this working from the users point of view would be to pick a name for each of the 4 roles I have created in a subform. Would there be a way to allow me to assign that individual to that role? The end goal is to then have the contact details for each role appear in the query results all under one record (this is what I need to achieve, above all else).

I'm going to have a play about and see what I can come up with but if anyone has any ideas in the mean time please let me know, I hope I'm not barking up the wrong tree!

 

[rclarke]

Having replicated your table structure I am not having any luck I'm afraid. See image attached.

What you see here is returning four results for one trial, which is what I wanted to avoid. I need to create a query which has the coordinator, investigator, nurse and data managers contact details all within one row/result of the query, in order to be able to export it correctly within one mail merge.

I wanted to avoid having four separate tables with contact details for each role contained within each for obvious reasons - this duplicates data and increases chance of error, however at present this is the only work around I know that will allow me to produce the query results I require.

 

[jdraw]

Please state as clearly as possible WHAT you are trying to do.

Please show the SQL for the query involved. Go to Query design, click sql view and copy and paste the code in the window.

From a conceptual view if you want to assign a Staff member to a Role for a given Trial.

you could have a Form with 3 combo boxes - cboTrial, cboRole, cboStaff.
The sources of these combos would be the associated tables. You could select a Trial, then a Staff member, then a Role (whatever sequence means most to you) and have a button btnAssignStaffRoleTrial.

The click event of the button would insert a new record in the tblTrialRolePerformedBy and since in the relationships there is a unique index on the combination of field values,duplicates will be rejected. You would need an INSERT (append) query in the button's click event.

 

[rclarke]

Put simply the purpose of my database is to collect information on clinical trials along with the names and details of the individuals involved with each study. There are up to 4 people involved with any one study. The investigator, the coordinator, the research nurse and a data manager. I want to query all relevant info into one result/record so that I can export this to a single word document using the mail merge function.

When queried using the SQL below, the table design you suggest causes the results for each trial to be listed four times (see image previously attached), the contact details of each role is listed as its own record along with the trial's data, rather than within a single record. However, to export the data I require all the trial details and contact details for each role to appear within only one result.

SELECT tblTrial.TrialID, tblTrial.[Study Acronym], tblTrial.[R&D number], tblStaffDetails.Title, tblStaffDetails.FirstName, tblStaffDetails.LastName, tblRole.RoleName
FROM tblTrial INNER JOIN (tblStaffDetails INNER JOIN (tblRole INNER JOIN tblTrialRolePerformedBy ON tblRole.RoleId = tblTrialRolePerformedBy.RoleId_FK) ON (tblStaffDetails.StaffID = tblTrialRolePerformedBy.StaffId_FK) AND (tblStaffDetails.StaffID = tblTrialRolePerformedBy.StaffId_FK) AND (tblStaffDetails.StaffID = tblTrialRolePerformedBy.StaffId_FK)) ON tblTrial.TrialID = tblTrialRolePerformedBy.TrialId_FK;

I am able to work around this by having four separate tables containing contact details for each of the roles. However, this has the knock on effect of duplicating data acorss tables and isn't a normalised design. However, it does enable me to have fields specific for each role; CoordinatorFirstName, CoordinatorLastName, CoordinatorAddresss, NurseFirstName, NurseLast Name, NurseAddress etc.

Ideally I want to be able to select a name from a single list of staff and assign to a role within a trial and ensure all details for all those involved are associated to a single record ready for export.

I hope this is clear enough?

 

[jdraw]

Please post a copy of your latest data base - remove anything confidential --mdb format as I have acc2003.

 

[rclarke]

Thanks for being so patient! See attached.

(Query isn't working now I deleted data, not sure why?)

 

[jdraw]

I'm just taking a quick look at your latest database.
DO NOT USE LOOKUPS AT THE TABLE LEVEL
see http://access.mvps.org/access/lookupfields.htm

Do your self a big favor -- get something working to meet your base need and then add functionality. What is with all the fields in the tblTrial? I think you have a lot of concepts intertwined there, but we can get to that. The old KISS principle -- walk before you run.

Design and bench test before you start serious programming.

I've just opened your forms. WOW, form Staff should be only about Staff, in my view, there should be no Role checkboxes on the Form. This is only about Staff whether corporate exec or janitor or part-time consultant. A separate fact from anything to do with Trials or Roles.

Please read this document on Relational Principles (atomic/one fact/single)
http://forums.aspfree.com/attachment.php?attachmentid=4712&d=1201055452

I'm going to rename YOUR tblTrial and work to make it simple just to show some concepts.

 

[jdraw]

I could not figure out how you had 5 copies of the tblStaffDetails in relationship with Trial and Role. I had to remove your tblTrial and do a compact and repair after writing code to remove all the relationships I could find and see.

You seem to have a lot of functionality in your forms - so I'm not familiar with your design and only wanted to do something basic. You may want to keep your forms if you built them and you understand them.

I think all the numbers you have in the Trial table indicates a whole lot of additional entities that have not been discussed.

Your qry1 worked as soon as I recreated the relationships.

I have attached my changes to the database in the zip file.

 

[rclarke]

Thanks for all your input, the advice you have provided is invaluable. I am working to normalise my tables, however, this is more of a proof of concept and does not represent my final design.

Admittedly, access is new to me and this being my first project the design far from ideal. However, for a proof of concept it it will do (for the time being). I had planned to rebuild this from the ground up once I had accomplished what I wanted with the query's and exporting data and having since learned about conventions and normalisation I will do a much better job next time around.

The advice you have provided for assigning roles is great, and I probably wouldn't have worked that out on my own, but I struggle to see how this can provide the results I require as only one person should be assigned to each of these roles, whereas the example you have provided has many people in each role.

More importantly however, in order to export to mail merge I require a query which provides results all on one line within a query. See images attached for further explanation, It's a crude demonstration of what I'm trying to do. I don't know how else to explain it, perhaps what I am trying to do is inherently flawed and I'm starting to give up hope, but there must be a way to export all data associated to a trial to a mail merge template.

Does that help you understand my issue?

 

[rclarke]

To clarify, VBA code I put together exports data from each field in a form to my template document. The simplest method of getting each data field from multiple tables into one form was to create the query above containing all the data I wanted to export, opening it in a form and exporting.

 

[jdraw]

What exactly does "I require all data to show on one row" mean??

Have you considered a report with sub report(s) or whatever? You may be constraining yourself more than you imagine.
You may want to look at Allen Browne's concatenation function.
http://allenbrowne.com/func-concat.html

Part of design is ensuring you have the right business facts, and referring to those facts as you start to build your data model.

Perhaps I missed the part that 1 Staff per Role per Trial.

Since you are doing proof of concept -- there is an old concept we used that we called stub processing. Build a prototype -- simple as you can and instead of logic to prevent this or that - just put out a message eg (Constraint goes here to ensure only 1 Role per Staff per Trial) If you build these sorts of things a sub procedures or functions, you have the basic logic and where it should be invoked without getting into the nuts and bolts (and bowels) of Access.

You an demo these sorts of prototypes fairly quickly; get relatively quick user feedback and adjust the model or business facts or process flow as required.

From my perspective, and as I usually qualify it (this and $1.80 will get you a coffee somewhere), you are introducing some sort of constraint on your overall conceptual design by saying "all the data must fit on one line...".

Maybe you need an attachment to your template document? Or a standard email for everyone with a custom report???

I suggest you get all the Entities identified, some key attributes and a set of business facts that you can use to evolve a workable data model.

If the 5 relations to your staff table was your method of making things fit on 1 line, then I'm not sure what additional advice I can offer. I spent an hour trying to get rid of the 5 relationships, and another relationship that demanded I put something in a table??

Move forward slowly; make sure you're solving the right problem; get user/management feedback and participation; keep all the players involved; always refering to the evolving model and facts; get some test data to "vet" the model.

We used to play stump the model - using business facts and creating good and bad test cases- work the data through the model. If anything occurs that is not expected nor matches the current facts, then adjust the model or the test data or the facts.
Reconcile every difference that is observed.

Good luck.