Not all instructors showing

[jmriddic]

Confused on how table works.

I am trying to add instructor ids to an exisiting database that an outside company built years ago. So I added an Insructor ID field to what I feel is the correct table and created an insrtuctor entry form. Probably less than half of the Instructors that are in the Instructors table are on an Excel sheet given to me by the admin assistant. I looked up a couple of them and the instructor yes/no field IS checked for them. Yet they don't show in the table.

I am attaching screenshots as I am not sure why it doesn't show every instance.

 

[pr2-eugin]

Okay this is what we call as Lookup fields.. It is very bad design.. Do not proceed with this.. Look at Evils of Lookup fields in Tables for more information..

 

[jmriddic]

I figured the lookups probably does have something to do with it but again I did not create this database nor do I feel I can really blow it up and start from scratch. Not an option if you see all the forms, tables, and other objects in the db as I am only trying to edit the database to allow them to add the id and then show the id on some reports used for laminated cards for classes so does anyone have any advice on how I can keep the table but get the records from the contacts table to show in all cases where the yes/no field is checked? So my thing right now is to figure out why don't all the instructors in this table and get them to show.

 

[Cronk]

Is ContactID a unique field in tblContacts?

If so, I can't suggest anything else (even though I share strongly the sentiments about lookups on table fields), unless you want to copy the two tables to a new database and post it. You can delete all records except a few to demonstrate missing records.

 

[jmriddic]

Yes it is and I tried to copy the database tables as you suggested. problem is the contact table has non instructors in it as well so it has about 42000 records total. I want to keep a mixture of instructors and students in the db I post so it may take a while.

 

[jmriddic]

Here is the db. I took some of the personal fields out of it.

 

[jmriddic]

Can someone take a look at this? I would appreciate any assistance.

 

[jdraw]

I took a quick look at the database. I'm not sure what tblAHAInstructors is suppose to represent. In my view it has some concoction of Instructor, Training/Courses, Monitoring info etc. I think you have a design issue that will have to be addressed at some point.

Of the 42000 records, can you determine which are current? Do you need older/outdated records?

 

[jmriddic]

The instructor table holds if they are certified to teach things like PALS,BCLS levels of CPR. And I am not planning to remove all the data from the Contacts table. My goal is to make edits to some tables and the reports(I have done that already) but I want to address this issue. The Instructor table is what is used in the report I made edits to show the Instructor ID value for printing cards out for classes. But my real question is if they are marked as an Instructor in one table, Contacts, why would they not flow over to the other table, Instructors. Less than half the people marked as Instructors were sent over to that table. I was asked by another forum person to provide a scaled down version of the db which I did.

 

[jdraw]

There is a model (pdf format) in post #5 at
http://www.access-programmers.co.uk/forums/showthread.php?t=241712
that shows how Employees and Positions can be related based on
A Position requires certain Training
An Employee can take Training
This set up was to determine in the Employees in Positions had the appropriate Training that was required. It may be useful to you just to see how the Training, the Positions, the Training required for Positions and the Training taken by Employees relate.
I think your "Instructors" table has hidden entities that may be causing you issues.

I'm not sure exactly what you mean by "flow over". Is a Contact an Instructor? Is an Instructor always a Contact?

Sounds a little like you may have subtype/supertype???

People (and some groupings in 1 table)
|
|
+------Contact
|
|
+---Instructors
|
|
+---Monitors
|
| and more

Just my $.02
Good luck.

 

[jmriddic]

What I meant is if the tables are related with 1 to 1 relationship(which I looked in the master version of the db as being the case) with the Contact ID field should that information not be in the table for Instructors. I am not show what hidden entities means so please explain to me. It might be helpful to reference the zip file from one of the post earlier in this thread where I have screenshots of the Instructor table which includes the SQL statements for the Contact ID field. Again I not the architect of the db just a repairman who has not done this in a while.

 

[Mrs.Smith]

I took a look at your screen shots, as well as your stripped down database upload...
Are your Contacts and Instructors the same thing?? Why else would you be pulling ID from Contacts into Instructors?? Or are Contacts something different - does contacts encompass MORE than just Instructors??

You stripped the database down to pretty much nothing, so i can't honestly tell you how to fix it without seeing it in full form. But if you can answer some questions, I can help you fix it without having to completely tear it apart... I just had to do a similar thing for a platform i didn't build as well.

 

[Cronk]

jmriddic

There's nothing wrong in including trainers in the tblContacts, and marking them as instructor.

Your sample database shows 15 records in tblContacts all marked as an instructor whereas tblAHCInstructors has 8.

How is the latter table populated?

 

[jmriddic]

Contacts can be students and instructors but in a Duh moment I figured this out somewhat. At least one field in the table has to be at valued for it show but it appears it only updates those who also have SSN present in the contacts tables as well.

Could it be because whoever set these tables made the SSN an index in the Contacts table with No Duplicates allow and because its a 1 to 1 it won't allow that record to show in the Instructors table?

 

[jmriddic]

Hi all,

Is my last post off base? Thanking about changing the index properties. The SSN field is set all zero length but is also set to be unique in the Index properties for the table. But there is a lot of contacts with a SSN value. its not a required field. Would that explain why those without SSN numbers don't show in the other table. My testing on some test records and testing on a couple of instructors by editing them on the Qualifications form(one with a ssn and one without) seems to support that notion.

 

[Cronk]

Is my question in #13 "off base"?

 

[jmriddic]

Nope. its populated via a Qualifications form which is partly tied to the Instructors table and some fields show from the contacts table on it as well and everyone can forget about the SSN issue. It was the way I was or was not checking off the yes no fields I guess because instructors can be added SSN or no SSN. We are going to have to do some cleaning up the data both in the db itself and from the Excel source file which is where we are getting the Instructor IDs from. So I guess right now I am ok but I will let you know on Monday if not.

 

[Mrs.Smith]

Did you manage to work it out using your SSN field adjustments?

 

[jmriddic]

Yes. Thanks for the followup