Why won't a query show results

[Gasman]

Yes the database has other tables that keep track of the training courses and other info. I am only mentioning the Main table here because to me it makes the most sense to keep track of the Time to Complete except for the fact that it only holds current training. I actually have everything working as i need to using the main table except for previous training. (thanks for your extensive reply! aI appreciate the time you put into it)

No, I was also going to suggest what @MajP has not only suggested, but broken it down to details for you. You would be wise to follow this structure, even if it means adjusting what you currently have using queries etc.

For this situation you would need the 3 tables mentioned by @MajP at least.

 

[MajP]

I will give the SOP table another look maybe that is what you mean

I think you need the Junction table to give you the many to many signifying a unique instance of an employee taking a specific SOP training.
Many employees, can take Many courses on different dates.

 

[Design by Sue]

I have a database application that pretty much does this.

So you have a table of employees with information about employees. It would have an EmployeeID primary key.

Then you need a table of courses. This is generic information about a course and not specific information about an offering of a course.
This includes how long until you are required to renew the training (expiration) and if the course is a mandatory or optional. In your case I assume this is a generic table of SOPs with and SOP_ID instead of a training course ID

T_TrainingCourses

TrainingCourseID	Training_Title	Sponsoring_Organization	Type_Training	Required_Training	ExpirationInYears
386​	Food Handler Training	Chautauqua Cty DOH & Human Services	Classroom	No​	5​
468​	NYS Basic EMT	NYS DOH	Classroom	No​	4​
298​	TSG - GOLD Interrator Reliability - Preschool/UPK	Teaching Strategies	Online	No​	3​
348​	TSG - Interrater Reliability -Infants/Toddlers	Teaching Strategies	Online	No​	3​
357​	MAT - Renewal	PDP	Online	No​	3​

Now I need a table to hold when an employee took the course. This is specific information about a course offering and in your case include how long it took to complete
This is called a junction table and allows you to create a Many_to_Many relationship linking a Course (SOP) to an Employee and on a given date.

T_Employees_TrainingCourses

ETCC_ID	EmployeeID_FK	TrainingCourseID_FK	Time_To_Complete	CourseDate	Certificate
67​	15​	42​		11/19/19​	Yes​
68​	15​	42​		05/09/19​	Yes​
69​	16​	46​		01/16/20​	No​
72​	15​	48​		08/11/20​	Yes​
96​	5​	80​		02/01/19​	No​
103​	6​	91​		09/04/19​	Yes​
105​	5​	91​		09/04/19​	Yes​
124​	15​	120​		06/03/20​	Yes​
127​	5​	124​		08/30/19​	Yes​

You can see in this snapshot that employee 15, 5, 6 took course on certain dates and some of these offerings provided a certificate

Using this simple organization I can show all completed training, as well as current training, expired training.
View attachment 114460


View attachment 114459



That is wrong. You cannot just plow forward with a broken design if you need to meet your objectives. Your database is not correctly designed so fix it now or you will be wrestling it with complicated workarounds for here on out.

I have looked at this and what you have here is to the best of my understanding exactly what my set up is, I do have 3 tables as you described. My equivalent of your Employees_TrainingCourses though only keeps the most current training within. I think that is what has to change I need to change it so it keeps all records.

 

[MajP]

I think that is what has to change I need to change it so it keeps all records.

If so you are in luck. Just create a new record for each instance of completing review of the SOP

Currently the database stores the Employee ID, SOP Number, SOP Version and Date Completed in the Main Table

That sounds not completely correct then
SOP Table
--SOP_ID
--SOP_Number
--SOP_NAME
--SOP_Version

Since an SOP can have multiple versions you want to store that in the SOP table. A person then takes a specific versioned SOP

In your Main Table
-- EmployeeID_FK
-- SOP_ID_FK

If SOP_ID 1 is
HW 123, Hand Washing, Ver 1.0,
and SOP_ID 15
HW 123, Hand Washing, Ver 2.0

You assign employees to the correct SOP_ID related to a specific version of the SOP and do not duplicate that information in the main table

 

[Design by Sue]

I know this post is a bit old but I am finally getting back to this project. I am trying to do the correctly and have hit the first road block. I am trying to save duplicate entries now to the MainTable - previously it was limited to only one entry. The primary key is based on 3 fields, EmplloyeeNumber, SOP Number and Version. (see attached screenshot) What do I do to this to allow multiple entries so that I can add the TimeCompleted for multiple dates? I am anxious as to how this will effect the other tables within the database (if at all) and I know that I will need to limit any reports that exist to only the most current entry based on the DateCompleted. I am also attaching a screenshot of the relationships. I know it is against Access "rules" but I had hoped I could create a new table that held all of the records that I am now going to have to add to the MainTable and use that new table for the reports on the TimeCompleted that the client wants but I have exhausted all avenues for that work around so I have to plow forward with making all of the necessary changes. As I have stated it has been a long time since I worked in Access and my brother who used to be my advisor on this has passed away. I know Iwas trying to cheat but my solution of a separate table (with duplicate info) would have worked for my needs and gotten me out of this mess. Please can you bear with me and help me get this problem solved. All help greatly appreciated!!!

 

[plog]

The primary key is based on 3 fields, EmplloyeeNumber, SOP Number and Version. (see attached screenshot) What do I do to this to allow multiple entries so that I can add the TimeCompleted for multiple dates?
...
I will need to limit any reports that exist to only the most current entry based on the DateCompleted.

See how that 1st statement makes the 2nd one incomplete? You will need both DateCompleted and TimeCompleted in some sort of sub-query system to determine the most current entry. Or, to quote a wise man who on Jun 10th said:

DateCompleted and Completion Time shouldn't be seperate fields. It's called a Date/Time field because it can accomodate a date and a time in the same field. So those 2 fields should become one.

Now, for the primary key issue--make an autonumber and have that be the primary key.

And lastly, don't use spaces in field names, it's going to make coding and querying that much more difficult later on.

 

[Design by Sue]

So I am trying to add the primary key, an autonumber field. Since this is an existing database obviously I need to remove the current primary key(s) and add an ID column. So far I have been able to create the statement to add the ID column but am stumped on updating the table to add the numbers to the existing records. I am thinking I need to do this before I make it the primary key because I can't assign a primary key to a field with no records. (see I have been trying to figure it out - really!) Here is what I have so far that does add the column ID but what is the correct code for auto incrementing the ID column and then making it the primary key. I am not finding the answer on line. Thanks for your help!!!!

ALTER TABLE [Main TBL] ADD COLUMN ID TEXT(25)

 

[plog]

Step 0 of any data work--make a backup of the whole database. Then in the database you are going to work in, make a backup of the specific tables you will be changing.

You should be able to do all the field additions in the design view of the tables and get the right data into those new fields with an APPEND query.

1. Remove the primary key from the 3 fields in Main.
2. Add an autonumber primary key to Main (MainID).
3. Add a new number field (MainForeignID) to any NotMain tables that need to hold MainID as a foreign key back to Main.
4. Build an APPEND query using Main and NotMain linking them by the old primary keys (EmployeeNumber, SOPNumber, SOPVersion) and have MainID saved into MainForeignID of the NotMain table.
5. Verify that NotMain now has the right MainForeignID in it

Do #4 and #5 for all NotMain tables that need their foreign keys adjusted. That should get all your tables realigned.

 

[Design by Sue]

WOW! That is a lot of information ... Thanks!

(trust me I have backkups! I keep poking at this and if I did not I would be totally screwed)

1. Please can you look at this relationship image and tell me what of these you meanbyt NotMain tables that would need this update?

2. I am having problems with adding the autonumber primary field to the Main Table. I can add the field but can't get it to autonumber (Can't make it the primary key until it has numbers in it.)

3 Then the append code will be another problem.

I am so sorry - I really am in the weeds!

I really appreciate you sticking with me

 

[Design by Sue]

OK!!! I did get the autonumber field to work!! and made it the primary key. So please ignore question 2

I will continue to poke at this and let you know if I make any more progress

 

[plog]

Hold on. You've got bigger problems. Give me 15 minutes to type this out.

 

[plog]

First, and this is now irrelevant, but steps 3, 4, 5 that I typed before aren't needed. I was explaining how to get your new primary key into an external table as a foreign key:

W3Schools.com

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

It's a good concept to learn, but not needed because you had not external tables that needed it as a foreign key and you have larger structural issues. Which are:

1. No autonumber primary keys. We just went over this with Main, but you could use them in a lot of the tables. It makes it easier because you just have to work with 1 field to link tables and it future proofs things because when you use real world values to link tables they sometimes (even if not often) can change which requires a lot of changes in a lot of tables. Autonumbers aren't real data, they exist just to link tables together so there will never be any need to change them to reflect changing real world conditions.

2. Circular references. There should only be one way to trace a path between tables. Your screenshot is an explicit loop of relationships and that's wrong. Worse still, there's hidden loops that you haven't explicitly defined. SOPNumber is in MainTbl, SOPTbl, JDSOPTbl, SOPSubVersionTbl, SOPVersionTbl which means all of those tables are in a huge spider web relationship with another. That is wrong. Autonumber primary keys will help the clutter, but even after adding those you've got too many tables linked together.

To figure this out I think you need to start with a new blank database, copy a low level table into it (e.g. DepartmentTbl, TypeOfProcedureTbl, SOPSubVersionTbl), add an autonumber primary key like you did to Main above, open the Relationship Tool and add that table to it. Then, copy in another table you think is related to that table. Add a primary key to it, update the other table to use that primary key instead of the actual field(s) you currently use, add that new table to the Relationship Tool and join them appropriately by that new primary/foreign key fields.

Do this one table at a time, adding primary keys, updating existing tables to use foreign key and adding them to the Relationship tool. But, when you add a new table to the relationship tool, it must only be linked to 1 existing table and not create any loops. If you feel the need to make it link to two tables you are on the wrong path and must figure out which table that new table is actually linked to.

 

[Design by Sue]

Thanks for your help - I will check into this. I really appreciate your taking the time to help me