Search results

Hi arnel Appreciate it. It is working the way I originally intended. I have run into the issue as I explained in the later comment now :( I apologise for this.

One more thing to add: I can add a CourseID FK in the EmployeeXCourse table which gives me the required results but then the issue is when I enter data in the EmployeeXCourse subform, the CourseID field remains at 0, which kind of fails the point. So any solution around that would also be...

Hi jdraw yes you are right. the records aren't duplicated, rather mismatched, if that makes sense?

Hi Doc Apologies. What I am trying to achieve here is that some of the courses are mandatory according to roles assigned to employees (the same course may be delivered to other employees even though its not mandatory). So I assign mandatory courses to roles in a RoleXCourse table. The table...

For reference, here are the images: And the output of the two:

Hi all In the attached db, I am trying to create a query, MandatoryTrainingDeliveredQ, which takes in info from MandatoryEmployeeTrainingQ and EmployeeXCourse_JT, using EmployeeID as the common field. I am however getting duplicate values. The short description of db is as follows: EmployeeT -...

I actually hadn't thought of this scenario. Totally makes sense why to not change the existing record. Thank you for bringing this up!

Yes, sorry thats what I meant. CertificationDate is a field in a table and then ExpiresOn is created in the query like you created CourseID2. My bad for not being clear on it.

So basically what I have is ValidFor field and CertificationDate field. I then have an ExpiresOn field which is just CertificationDate+ValidFor. and in the conditional formatting I set up red highlight if ExpiresOn is less than =Date(), yellow if ExpiresOn is between =Date() and Date()+45, and...

Yes, thats what the field precisely is for. For confidentiality reasons I couldnt upload the original db which is more extensive. It was just this bit I couldnt get to work. I am thinking I can use the CourseID2 technique with the validity as well and do the conditional formatting for date...

Hi Mike RoleXEmployee_JT sub form shows roles assigned to the employee in the parent form, and EmployeeRoleTrainingQ subform shows trainings mandatory for that employee and the roles assigned to them.

Hi Bob Looks like exactly what I wanted! What a neat solution, thanks!

Hi all Please refer to the db attached. Before I ask the question, here are the details of the db (and apologies for a long thread): CourseListT, EmployeeT, PositionT, and ProviderT are self-explanatory Course_JT (PK DeliverID) keeps records of courses provided by providers e.g., one course...

Hi doc Thanks for the spot regarding relationship not being with PK. I certainly overlooked that totally somehow.

That's done the trick! Thank you very much MajP!

Hi Larry I liked the idea you proposed but very soon figured out I would have to change alot elsewhere in the db :D It doesn't help that I have had to make changes this late into the development of db. But I appreciate your time and suggestions very much. Thank you :)

Hi all Apologies for late response. Been off work due to unseen circumstances. Appreciate all the help.

Hi Larry If I understand correctly, you do not want me to have separate CourseListT and Course_JT? In this case, wouldn't it mean that every record in course table must have an employee ID attached to it, thus stopping me from adding courses which we will be delivering in the future? As I...

Hi doc Its not so much as duplicating records but creating records which dont exist in the table. I have attached the example db if you could kindly take a look and share your thoughts?

Hi MajP I tried to change the CourseID field in EmployeeXCourse_JT to DeliverID field and it started to produce even weirder results! I have attached the example db in the previous post, if you could be kind enought to please have a look at it?