Inteligent combos (1 Viewer)

[guinness]

Hi guys

I've attached a rudimentary example db to give an example of what I'm trying to achieve.

There is a form on the db called Resourcing with several combos.

When I choose a Training_Type from the first combo it automatically filters the Project_Title combo to show only the projects that match that training type. When I choose a Trainer_Name the text box for team automatically completes.

The bit I'm struggling with is Trainer_Name. As you will see from the table Course_Details not every trainer can deliver every Project_Title. So what I want is to display only the trainers associated with the project title chosen rather than displaying the whole team in the Trainer_Name combo.

Someone suggested a junction table but I'm not sure how this would answer my question. I'm thinking some sort of Dlookup but don't know how I would write it when it has to include data from four fields.

Any Ideas?

 

[jdraw]

I recommend you do more research on Normalization.

It seems your thoughts of a junction table would identify which Employees can teach which Courses.

Courses ----> EmployeeWhoCanTeachCourse<------Employees

Good luck.

 

[guinness]

Thanks jdraw

Perhaps because I've never used a junction query I just can't quite get my head round how it will help me achieve what I'm trying to do which is end up with a combo box showing only the trainers associated with each course.

Does anyone have an example of this working in practice?

 

[jdraw]

The issue is a many to many relationship between Employees and Courses. Not all Employees can teach every Course and not all Courses can be taught by all Employees.
You need to identify which Employees can teach which Course.

Here is a free, video tutorial that shows how to break down a Many to Many to two 1 to many relationships by means of a junction table.

You can query the junction table to find out exactly which Employees(trainers) are associated with each Course.

 

[guinness]

Ok

So I've followed the steps in the video and created a junction table. Called Junction.

On that new table I typed the detail of who delivers what.

What now?

I've attached the db with the new table and data in case I've done something wrong

Thanks

 

[jdraw]

Please tell us what Employees_1, Employees_2, Employees_3 represent? Would you have Employees_89 if there were 89 Employees?
Do not use Lookup fields within Tables.
What do you mean by Trainer_1, Trainer_2, Trainer_3 and Trainer_4?

Instead of manipulating the current tables and relationships, how about stepping back and telling readers --in plain English -- What is the "business" this data base is intended to support? We don't know exactly the purpose of the database.

What is a Team? Who can be Team Leader? Why is there a CourseDetails and CourseDetails_1?
Nstead of a field ID in every table- which can be confusing - a better approach would be to have fields ActivityID, TrainingTypeID... makes it very clear which ID is involved.

 

[guinness]

Please tell us what Employees_1, Employees_2, Employees_3 represent? Would you have Employees_89 if there were 89 Employees?
Do not use Lookup fields within Tables.
What do you mean by Trainer_1, Trainer_2, Trainer_3 and Trainer_4?

Trainer_1 etc are no longer relevant and could be removed. When I did this I was trying to find a way to get all the trainers listed against a project on one line. I now know that this won't work. There are examples however where possibly ten trainers may be able to deliver a project whilst there are other examples where only one or two would have the expertise.

Instead of manipulating the current tables and relationships, how about stepping back and telling readers --in plain English -- What is the "business" this data base is intended to support? We don't know exactly the purpose of the database.

The purpose of the db is to allow a training manager to schedule trainers to deliver the projects on the training plan. At the moment when they choose a Type it filters Project_Title based on that Type. I simply want it to go one stage further and only present trainers who can deliver that project.

What is a Team? Who can be Team Leader? Why is there a CourseDetails and CourseDetails_1?

There are four teams. The team leader is irrelevant. When you select a trainer the team text box will auto-complete. On my real db that allows me to report on the activities of each team.

Nstead of a field ID in every table- which can be confusing - a better approach would be to have fields ActivityID, TrainingTypeID... makes it very clear which ID is involved.

Point taken. Again you are only seeing a small section of a larger db. I just threw together enough to show people what I was looking for help with which is a combo box on the resourcing form.

 

[jdraw]

My questions and comments were meant to help vet the database structure against the underlying business need.
From your response I am supposing that

A TeamManager schedules 1 to many Trainers
Trainers deliver 1 or many Projects according to a TrainingPlan
A Project is identified by a TrainingType
A Trainer may be capable of delivering 0 or many Projects (based on TrainingType)

 

[guinness]

Thanks Jdraw. It looks like you've figured it out.

Looks like I've figured it out too as I have now been able to restrict the list of trainers names based on the Project_Type selected. My only issue now is that I would like the list to show all trainers if Type and Project_Title are blank.

Back to the drawing board:banghead:

 

[jdraw]

You would get a list of all trainers from the Trainers table.
You would get a list of Approved trainers(by ProjectType) from the junction table.

 

[guinness]

Thanks again jdraw.

What I would like it to do is:

If the project title is not null: 'Display authorised trainers
SELECT [Trainers_Projects].[Trainer_Name] FROM Trainers_Projects WHERE Project_Title=[Forms]![Resourcing].[Cmb_Project_Title] ORDER BY [Trainer_Name];
if the project Title Is Null: 'Display all trainers
SELECT [Employees]![Trainer_Name] FROM Employees ORDER BY [Trainer_Name];

My reason is that sometimes we schedule trainers for things other than delivery so there would be no entry in the Training_Type or Project_Title box.

The problem is that I don't know how to write this in to the fields row source.

 

[jdraw]

Can you post your latest database with the Trainers_Projects table?

Where and how did you want these queries to execute?

 

[guinness]

Thanks for getting back to me jdraw

I've attached the latest copy of the db.

The Query is to run as the row source property of the Trainer_Name field on the form Resourcing

 

[jdraw]

Resourcing form has a Create Records button - presumably to populate the Resourcing table.
So why would you want to use the resourcing form with the modified Recordsource?

I can see a query like this

SELECT Trainers_Projects.Trainer_Name, Trainers_Projects.Project_Title
FROM Trainers_Projects
WHERE (((Trainers_Projects.Project_Title)=[Enter Project Title]));

to get the Trainers who are capable/authorized for a Project.

Your current relationships mean nothing to me. I prefer to work from a model that you can test against your requirements.

I am confused as to WHAT you are really trying to do. Especially since you have changed your relationships, and do not show all your tables in the "model".

I think you need clear description of the business in order to help you and readers to communicate.

Here's a sample of a narrative relating to a database from RogersAccessLibrary.

ZYX Laboratories requires an employee tracking database. They want to track information about employees, the employee's job history, and their certifications. Employee information includes first name, middle initial, last name, social security number, address, city, state, zip, home phone, cell phone, email address. Job history would include job title, job description, pay grade, pay range, salary, and date of promotion. For certifications, they want certification type and date achieved.
An employee can have multiple jobs over time, (ie, Analyst, Sr. Analyst, QA Administrator). Employees can also earn certifications necessary for their job.

Here's the link to the tutorial - it leads from this narrative to a normalized structure and ERD.

 

[guinness]

Hi Jdraw

What you have is only part of the database that I am building which may explain why you don't see everything that you expect. There are however enough fields and tables in my sample to let you see what I am trying to do.

I already have working code in the Row Source property (see attached screenshot) of the Trainer_Name field on the resourcing form which returns the authorised trainers. All I want is to re-write this code so that if no project title has been selected it returns all trainers.

This resourcing form is the input method to the resourcing table. When the user has entered all data it populates the resourcing table. Then what happens in the full database is a crosstab query graphically displays dates on the left hand column, trainer names across the top and their activities below like a gantt chart. This allows the manager to see who has availability for training requests as they arrive.

Not all activities are training activities. Trainers could be on Holiday, sick, admin, designing, travelling etc. Hence not every record created will have a Training_Type or Project_Title.

I hope this makes some sort of sense.

 

[guinness]

The article you linked to looks very useful. Probably if I had read that first I wouldn't be where I am now

Thank you

 

[jdraw]

I think it is a great tutorial to help with database design. Hope it works for you.

If you learn by watching, then see the links in post #2 here.

Good luck

 

[RainLover]

This pic you posted.

It does not show all the Combos of which there are 5.

I realise that for a newbie that you may not understand why we ask for so much information, but it is sometimes unavoidable.

 

[guinness]

Hi RanLover

If you go to post 13 there is a copy of the db. All I wanted to point out with the picture is where I want to put the sql if that makes sense?

Any help much appreciated.

Guinness

 

[RainLover]

Hi RanLover

If you go to post 13 there is a copy of the db. All I wanted to point out with the picture is where I want to put the sql if that makes sense?

Any help much appreciated.

Guinness

If you put your Logic hat on you should realise that I have already downloaded that DB because how else would I know how many boxes there are.

If a new person comes along do you then want them to read every prior post. Of course not. So I am suggesting that it is up to you to feed us as much information as possible as simple as you can.