Show only a specified list in a combo box depending on requirements

[brobertson89]

Hi all,

I have a probably simple problem that I am having a hard time trying to figure out how to do (I am also relatively new to Access, so that is not exactly helping).

I have a list of companies that my business works for. Each of these companies requires a list of training courses to be completed prior to working for them. Finally I have a list of personnel who have undertaken these courses.

What I want to be able to do is (on a form) select the company, from a combo box, that we are doing a job for and then in a second combo box have it only showing the employees who have undertaken the correct courses so that I can only select them.

Any help would be greatly appreciated.



Cheers,

Brandon

[June7]

These are dependent/cascading comboboxes. Review: http://www.datapigtechnologies.com/f...combobox2.html

[brobertson89]

Okay so I understand that a dependent/cascading combobox might be the way to go but I am kind of confused about how I would apply my tables to those given in the example.

I have three tables:

Table 1 has the following fields:
Customers - this is just a list of individual company names

Table 2 has the following fields:
ID - autonumber (this field is required because the other fields are not unique identifiers)
Customers
Courses - multiple courses can be selected for the same customer

Table 3 has the following fields:
ID - autonumber (this field is required because the other fields are not unique identifiers)
Courses - these are the courses that each person has completed
Personnel names

[June7]

Do you want to show only the personnel who have completed ALL courses for a particular company?

[brobertson89]

Sorry about the delay but yes that is correct.

[June7]

That does get tricky. Would help to work with data. If you want to provide db, follow instructions at bottom of my post.

[brobertson89]

Database2.zip

Okay I have attached a simplified version of the database, there is nothing on the form at the moment so feel free to do whatever you need to.

Thank you for your time, I really appreciate it!

[June7]

There is added complication because Course_personnel saves the course name but Customers_course saves a number ID. Really should be the same data type in both tables. Cannot join these tables on the two course fields. However, the lookup for Course field is pulling the ID from Course_personnel. That makes no sense at all. Should have Courses table that will be the source for any lookup to select a course. Each course would be a unique record in this table.

[brobertson89]

Okay I have added a table with the course names and I have redone the lookups as well as changing some naming to make it a little easier.Database2.zip

[June7]

Halfway there.

Customers_requirements has a Course field set as text but it is number data. This has no relationship to Courses because there is no number IDs in Courses.

Before proceeding you need a better understanding of relational database principles and what primary and foreign keys are.

Here is a little info on primary keys and autonumbers.......

Microsoft Access Tables: Primary Key Tips and Techniques
http://www.fmsinc.com/free/newtips/primarykey.asp

Autonumbers--What they are NOT and What They Are
http://www.utteraccess.com/wiki/index.php/Autonumbers

Use Autonumbers properly
http://access.mvps.org/access/general/gen0025.htm

A good tutorial site http://www.rogersaccesslibrary.com/

[brobertson89]

Database2.zip

Okay I have had a good read and I have made some changes. Whenever I try and use the look up wizard to determine what values a field can have, it automatically matches it to the ID field not the course name or customer name fields. I manually created the relationship to overcome this and it seems to have worked. As for the number data in a text field that was because I had an ID field however when I deleted this it kept the numbers from that field.

[June7]

1. The lookup wizard is doing what it should (although I never use it or any of the design wizards). The ID should be the value saved, not the text descriptors for technician, company, course. The links in Relationship builder should be on ID primary key and related number foreign key fields.

2. names make very poor keys, Technician Name should not be PK, ID should be

3. name parts should be in separate fields - LastName, FirstName, Middle

4. advise not to build lookups with alias in table http://access.mvps.org/access/lookupfields.htm

5. advise no spaces or special characters/punctuation (underscore is exception) in naming convention

As for your original question - see attached.