please help me with db, stuck with option group

[LarryE]

Thank you VERY MUCH LarryE for your effort and time!

as access is interesting to me but i have like 2 out of 100 scale knowledge , i have been sitting with it 17 hours per day like almost 5-6 days now and doing it myself as well cause at least this way i can try , get an error and spend half a day to figure what the problem was. so i im not resting and i did this

View attachment 119143

this is just entry for questions and rating per 1 emp.
based on this designed

View attachment 119144

would love to see and test your design, query, stats. nice ! thank you ! )

1. What are rates? Is this how much the company pays per employee? Who pays it and when?
2. When are Certificates issued and to who? Each employee that participates?
3. Are there different types of Certificates? So, each employee may be issued different ones for each training session?

 

[TipsyWolf]

What are rates? Is this how much the company pays per employee? Who pays it and when?

rates - its just rating from 1 to 5 for 8 feedback questions like.
rateID=1 is "overall how do u feel about it" 1 2 3 4 5
rateID=2 is "rate education materials" 1 2 3
rateID=3 is "have u learn something new today" yes or no, but i prefer keep it 0 or 1 / 1 or 2
rateID=4 is "have u *****" 1 2 3 4 5
rateID=5 is "does it *****" 1 2 3 4 5
rateID=6 is "have u *****" 1 2 3 4 5
rateID=7 is "have u *****" 1 2 3 4 5
rateID=8 is "have u *****" 1 2 3 4 5
rateID=9 is "extra comment u would like to share with us"


how i did it this way

put in Rates.Rate maximum score can get and in query

SELECT DCount("[EmployeeID]","[Employees]","[EmployeeID]<=" & e.EmployeeID) AS EmployeeNumber, e.FullName AS EmployeeName, e.JobTitle, e.TrainingID, MAX(IIf(rr.RateID=1,ROUND(rr.RateResponse/r.Rate*100,0),Null)) AS Rate1_Percentage, MAX(IIf(rr.RateID=2,ROUND(rr.RateResponse/r.Rate*100,0),Null)) AS Rate2_Percentage, MAX(IIf(rr.RateID=3,ROUND(rr.RateResponse/r.Rate*100,0),Null)) AS Rate3_Percentage, MAX(IIf(rr.RateID=4,ROUND(rr.RateResponse/r.Rate*100,0),Null)) AS Rate4_Percentage, MAX(IIf(rr.RateID=5,ROUND(rr.RateResponse/r.Rate*100,0),Null)) AS Rate5_Percentage, MAX(IIf(rr.RateID=6,ROUND(rr.RateResponse/r.Rate*100,0),Null)) AS Rate6_Percentage, MAX(IIf(rr.RateID=7,ROUND(rr.RateResponse/r.Rate*100,0),Null)) AS Rate7_Percentage, MAX(IIf(rr.RateID=8,ROUND(rr.RateResponse/r.Rate*100,0),Null)) AS Rate8_Percentage
FROM (Employees AS e INNER JOIN RateResponse AS rr ON e.EmployeeID = rr.EmployeeID) INNER JOIN Rates AS r ON rr.RateID = r.RateID
GROUP BY e.FullName, e.EmployeeID, e.JobTitle, e.TrainingID
ORDER BY DCount("[EmployeeID]","[Employees]","[EmployeeID]<=" & e.EmployeeID);

if u have a better idea - it would be lovely !

When are Certificates issued and to who? Each employee that participates?

when training is over. trainer has 1 week to enter all data to make them. after trainer enters all data, his next step is to generate certificates. yes, everyone who participated regardless of scores.

Are there different types of Certificates? So, each employee may be issued different ones for each training session?

yes, there are 2 types of cert. here we can link it to TrainingTypeID.
design of certificates are the same, but there are few changes depending on TrainingTypeID, full name, and project name which i need to include in CustomerSite (i will just add extra field for it)

 

[TipsyWolf]

@LarryE , Sir not sure if i already mentioned, guess i didn't, but i found an option to save \ store pdf outside of the db. so dont spend your time on it.
deepseek helped me with it. once again - thank you for your time !

 

[LarryE]

@LarryE , Sir not sure if i already mentioned, guess i didn't, but i found an option to save \ store pdf outside of the db. so dont spend your time on it.
deepseek helped me with it. once again - thank you for your time !

Do you wish to keep track of your training fees earned?

 

[TipsyWolf]

Do you wish to keep track of your training fees earned?

we dont have training fees. its legal department contracts etc. we dont see it, we dont touch it. maybe later i would like to have something to calc $ per trained emp or per trainer or similar, but for now i would prefer to keep it without contracts as i dont have any info about it right now.

i would dive deeper into issuing certificates \ creating report.
- it has unique number based on customerSite \ location, TrainingType.
- this unique number should be saved into table so we can track them.
- as i get the design correctly. from TrainingAattendance form it should some button to generate it. ( i think later i will be able to change this part to maybe auto-save report to root \ certificates \
- guess it would be useful to have checkbox that shows was this cert. (employees' certificate) printed \ saved \ generated. or once it has unique number ... maybe it should be set up via query to compare employees vs certificated generated. but this part is totally blind for me as i totally dont know how i would structure it thus i have no clue how it should work.

damn, u saved me already 100 hours for my last 2 weeks. at minimum for now, and its counting
this app shows that for the last 7 days access was an active window for > 50 hours. not to include txt, chrome etc where i find solutions.

thank you once again !

 

[LarryE]

we dont have training fees. its legal department contracts etc. we dont see it, we dont touch it. maybe later i would like to have something to calc $ per trained emp or per trainer or similar, but for now i would prefer to keep it without contracts as i dont have any info about it right now.

i would dive deeper into issuing certificates \ creating report.
- it has unique number based on customerSite \ location, TrainingType.
- this unique number should be saved into table so we can track them.
- as i get the design correctly. from TrainingAattendance form it should some button to generate it. ( i think later i will be able to change this part to maybe auto-save report to root \ certificates \
- guess it would be useful to have checkbox that shows was this cert. (employees' certificate) printed \ saved \ generated. or once it has unique number ... maybe it should be set up via query to compare employees vs certificated generated. but this part is totally blind for me as i totally dont know how i would structure it thus i have no clue how it should work.

damn, u saved me already 100 hours for my last 2 weeks. at minimum for now, and its counting
this app shows that for the last 7 days access was an active window for > 50 hours. not to include txt, chrome etc where i find solutions.
View attachment 119166
thank you once again !

We don't need a separate table for Certificates. I can add a Certificate Text field to the TrainingType table to describe what the certificate is for and add a Certificate Awarded checkmark in the TrainingAttendance table to indicate it was printed and awarded for each attendee.

 

[TipsyWolf]

Certificate Awarded checkmark

could u please add timestamp for it ? isn't too much ? another angle is simplicity though and not to brake it later. what if i click "generate certificates" twice. is it gonna re-write timestamp data ? so think timestamp isn't necessary. well, i think i could play around with it later, so leave this part to me.

 

[TipsyWolf]

btw, while u r helping me, im trying new designs for myself. kinda practicing and another reason is to find out .. could i build this db if not you, who grateful decided to help me out here.

i learn something new day by day. very slow, but still something.

UPD: this design is bad and wrong

 

[LarryE]

could u please add timestamp for it ? isn't too much ? another angle is simplicity though and not to brake it later. what if i click "generate certificates" twice. is it gonna re-write timestamp data ? so think timestamp isn't necessary. well, i think i could play around with it later, so leave this part to me.

Yes I can add that. Taking the day off today.

 

[TipsyWolf]

 

[LarryE]

View attachment 119171

1. Each Customer has multiple Employees and Locations
2. Each Location has multiple Training Sessions
3. Each Training Session has multiple Employee attendees
4. Each Employee attendee has multiple Questions and Responses and multiple SessionFeedback Ratings (and comments)
5. Each TrainingType has multiple Questions
6. Each Rating has multiple Feedback Rating Numbers

So we need to have a way to:

1. Select which Customer Employees attend each training session (Registration)
2. Apply each selected employee to the appropriate session
3. Apply each Question from the appropriate Training Type to each training session employee
4. Apply each Rating Number to each employee attendee

When I pass the test file to you in a day or so after I test it, it will show you how to do those 4 things

 

[TipsyWolf]

cant wait !

select ? we dont have full emp list for every customer. i get attendance list just 1 day or sometimes just 1 min before training, so it would be great if i need to manually type each employee (or paste it) into form after i select cusotmer, customerSite, trainingtype, trainer, date and then i enter employee data with their question and rate responses
so we dont have full emp list of each customer. we enter those ppl who comes to our trainings. just making sure i explained it correctly since my message #1.

 

[LarryE]

so we dont have full emp list of each customer. we enter those ppl who comes to our trainings

You don't need any full list. You will just enter the employee name under each customer that wants to attend the session. That's all. I didn't say anything about a full list of employees.

 

[TipsyWolf]

You don't need any full list. You will just enter the employee name under each customer that wants to attend the session. That's all. I didn't say anything about a full list of employees.

oh, okay, i was right then, just wanted to make sure. nice !