please help me with db, stuck with option group

TipsyWolf

Member
Local time
Today, 23:07
Joined
Mar 20, 2024
Messages
278
hello guys !

im stuck with another issue that i just can't solve. i've tried to google it, youtube it, AI it , but no luck for me. spent like 2 days in a row per 17 hours and 0 success.

i have a simple training database where i have training, employee (attendees) trainer and some questions and answers they give as a feedback to us.
so in a entry form where control source is training i inserted an employee subform so me \ user could entry bulk of employee per 1 training so it has continuously form and also i would like to have option group so user could entry employees answer (responses), but i try everyting and it didn't work for me with new records.
the thing is .. option group works smoothly only if i input employeeID manually into Response table as well as QuestionID - option group indicates it and can even change the values , but it can't add Foreign Key IDs into Response table

i even try noob version like put all question \ responses in one table like q1, q2, q3, q4so i could get rid of questionID and even this didn't work for me
could anyone help me please ? im attaching db it self. please have a loot at Form FnewTraining and try to play with option group.

Pease help me :(


this is my copy past from deepseek if u need more details about how it should run.:
need to have customer table with all our customers. it should be not only names, but also location as well for example Customer1, location1-we need employee table with all people who visited training. 1 employee takes only 1 training, so consider it as one to many relationship. -need to have trainers table who facilitates training. 1 trainers can lead many trainings at every customer's locations- need to have trainings table where we need to register every trainingwe have 2 types of training for workers and engineers. we have a questionnaire after training where ppl need to give feedback like rating to this training from 1 to 5. scale and 11-13 questions that employees need to answer (11 for workers and 13 to engineers). number of questions are fixed and its very unlikely that they are gonna be changed i would like to have a form where record source is training with all employee took the training (as a subform i guess) and with all feedback and responses they gave. prob. need to compare if their response is correct. if correct , maybe indicate it as logo ✅ and ❌i see it as TrainingFormTraining Info, Customer info,subformEmployeeID FullName Q1 Q2 Q3 Q4 % 1 Bob ✅ ❌ ❌ ✅ 50 2 Lily ✅ ❌ ✅ ✅ 75 3 Nick ✅ ❌ ✅ ✅ 75 4 Micky ✅ ✅ ✅ ✅ 100 also i need to have some kinda form for registering employees who participated in training as well as set their responses from those 1-13 questions where 4 options and only 1 is correct. this form should allow to add more than 1 employee, prob 5-10 or even 20 if its possible
 

Attachments

Last edited:
Hi
The attached is one of the best examples of a questionnaire, which is what you are trying to achieve.
 

Attachments

Hi
The attached is one of the best examples of a questionnaire, which is what you are trying to achieve.
too complex for me as i can't rebuild it for my case. no forms (only tables, other objects must be hidden) and it gives me an error even i click on one of those buttons
1742929892512.png

but thanks anyway!
 
I take it you did not bother to look at the Manual on how to set up a questionnaire?
This is one of the easiest ways to achieve what you need.
 
You say an employee will only ever have one training.

Linking Questions to TrainingTypeID instead of TrainingID is not conventional and source of difficulty for data entry. Employees link to Trainings but their Responses are linked to TrainingType. You want to enter data for each Training but the relationships don't facilitate that in a form/subform arrangement.

Also, the Q1 and Q2 structure makes no sense. Number of questions for each type varies.

Customarily, a form allows data entry to one table - other tables data can be displayed via JOIN in query but controls should be set as Locked to prevent edit.

CROSSTAB query is not editable so why use it as source for a form? This belongs in a report.
 
You say an employee will only ever have one training.

Linking Questions to TrainingTypeID instead of TrainingID is not conventional and source of difficulty for data entry. Employees link to Trainings but their Responses are linked to TrainingType. You want to enter data for each Training but the relationships don't facilitate that in a form/subform arrangement.

Also, the Q1 and Q2 structure makes no sense. Number of questions for each type varies.

Customarily, a form allows data entry to one table - other tables data can be displayed via JOIN in query but controls should be set as Locked to prevent edit.

CROSSTAB query is not editable so why use it as source for a form? This belongs in a report.
thank you for your reply, sir !
I'll try to fix it if I can.
 
i can't rebuild it for my case. no forms (only tables, other objects must be hidden)

There is a major disconnect here. "Option Group" is not a data type, it is a control type - which only applies on a form. If you have only tables (no forms), you CAN use a multi-select data type - at your peril, since they are absolute beasts to work with - but you can't use Option Group as a data type in a table. In your post from this morning at 7:34 (see "Similar threads") you stated that you DO have a form. Therefore, can you please clarify what you really have? Did that statement in this thread not apply to the problem described in the "similar thread" item?
 
In your post from this morning at 7:34 (see "Similar threads") you stated that you DO have a form. Therefore, can you please clarify what you really have?
yes, sure i do have form to build my db.
i said there are no form to mike60smart who shared his survey db.

previous post was about option group, but it was a bit different issue.

could u please explain to me whats multi-select data type ? isn't the same as multivalued fields ?
 
hello guys !

im stuck with another issue that i just can't solve. i've tried to google it, youtube it, AI it , but no luck for me. spent like 2 days in a row per 17 hours and 0 success.

i have a simple training database where i have training, employee (attendees) trainer and some questions and answers they give as a feedback to us.
so in a entry form where control source is training i inserted an employee subform so me \ user could entry bulk of employee per 1 training so it has continuously form and also i would like to have option group so user could entry employees answer (responses), but i try everyting and it didn't work for me with new records.
the thing is .. option group works smoothly only if i input employeeID manually into Response table as well as QuestionID - option group indicates it and can even change the values , but it can't add Foreign Key IDs into Response table

i even try noob version like put all question \ responses in one table like q1, q2, q3, q4so i could get rid of questionID and even this didn't work for me
could anyone help me please ? im attaching db it self. please have a loot at Form FnewTraining and try to play with option group.

Pease help me :(


this is my copy past from deepseek if u need more details about how it should run.:
need to have customer table with all our customers. it should be not only names, but also location as well for example Customer1, location1-we need employee table with all people who visited training. 1 employee takes only 1 training, so consider it as one to many relationship. -need to have trainers table who facilitates training. 1 trainers can lead many trainings at every customer's locations- need to have trainings table where we need to register every trainingwe have 2 types of training for workers and engineers. we have a questionnaire after training where ppl need to give feedback like rating to this training from 1 to 5. scale and 11-13 questions that employees need to answer (11 for workers and 13 to engineers). number of questions are fixed and its very unlikely that they are gonna be changed i would like to have a form where record source is training with all employee took the training (as a subform i guess) and with all feedback and responses they gave. prob. need to compare if their response is correct. if correct , maybe indicate it as logo ✅ and ❌i see it as TrainingFormTraining Info, Customer info,subformEmployeeID FullName Q1 Q2 Q3 Q4 % 1 Bob ✅ ❌ ❌ ✅ 50 2 Lily ✅ ❌ ✅ ✅ 75 3 Nick ✅ ❌ ✅ ✅ 75 4 Micky ✅ ✅ ✅ ✅ 100 also i need to have some kinda form for registering employees who participated in training as well as set their responses from those 1-13 questions where 4 options and only 1 is correct. this form should allow to add more than 1 employee, prob 5-10 or even 20 if its possible
It sounds like maybe a design like this may be closer to what you might need, but just a start:
1742942083738.png

I attach the file here without the queries and forms, just the tables and fields I used. A lot going on here and I assumed Option 1,2,3 and 4 are options for each question, such as answer options. You can study it and see if it works.
 

Attachments

It sounds like maybe a design like this may be closer to what you might need, but just a start:
View attachment 119109
I attach the file here without the queries and forms, just the tables and fields I used. A lot going on here and I assumed Option 1,2,3 and 4 are options for each question, such as answer options. You can study it and see if it works.
thank you very much for your help and effort. !! much appreciated !
 
could u please explain to me whats multi-select data type ? isn't the same as multivalued fields ?

Yes, all the same. Different names based on seeing things through different-colored glasses, so to speak. But all the same. And all bad choices. A multi-select data type is a field type that lets you define fields with a limited and preset number of values.
 
It sounds like maybe a design like this may be closer to what you might need, but just a start:
View attachment 119109
I attach the file here without the queries and forms, just the tables and fields I used. A lot going on here and I assumed Option 1,2,3 and 4 are options for each question, such as answer options. You can study it and see if it works.

thank you very much again for your help and time.
i got confused of attendance table and couldn't build forms around it and had to step back to my own design. i know my design is fairly simply thus has limitations of what it can do later in forms \ queries etc. as we discussed in DMs

Structure:
- customer1's emp can't be in the same training with customer2's emp
- training date has 1 day long for TrainingTypeID=1 and 2 days for TrainingTypeID=2 (days are always in a row, like 26th and 27th march)

Forms:
- i see it as training masterlist (contentiously forms) where i can dbl click (OpenForm ="[TrainingID]= " & [TrainingID]) on it and enter
- TrainingPage where i can see every attendees like i already built with scoring via query and subform
1743012832631.png

- i would like to have a option to dbl click on attendee as well but with subform its not possible i guess. so i could see his personal page to see their answers on every question and feedback (rating) basically from 1 to 5)
-also why i need attendees page is to open it and put his responses from his list he gave me and mark all answers like i already did
1743013148094.png

but it has limitations - as its subform - i can't make it in continuously form have many emp to enter and click on 11 option groups, so can only have 1 (attendees \ responsePage per 1 employee , so thats why im stuck with not having the option to enter data for many employee at one screen - it would make entering data much easier.

to summarize about Forms. it should be:
- master list of trainings
- form of training page
- form attendee page
- statistic form page
- entry data form

later i would try to find some solutions to save and store PDF files outside of the db. i have my db that perfectly does it , but it supports jpeg only. this PDF file is just a scan of attendance list as well as all questions \ answers \ feedback (its 3 page doc per attendees , but we usually store it as a single 30-50 page file) so attendeesID should also has 1 pdf file attached attendees.link ( to it so i could dbl click on it in TrainingPage \ Attendees page.

also i would like to build some kinda statistics pages where can i filter customers, trainers and see who has which scores on questions \ answers and feedback (1-5 rating).

and last ... i need it generates certificates after i enter employees into db. for example i had a group 10 ppl were there. i enter 10 ppl' data put all responses and feedback (hopefully within 1 page \screen) and push 1 button so it creates report. i can create report myself, but i think i can handle it. i would do it using query, find those ppl by today().. later i will put mark checkbox for already generated certificates. i guess thats it .. sorry for long txt
 
Last edited:
thank you very much again for your help and time.
i got confused of attendance table and couldn't build forms around it and had to step back to my own design. i know my design is fairly simply thus has limitations of what it can do later in forms \ queries etc. as we discussed in DMs

Structure:
- customer1's emp can't be in the same training with customer2's emp
- training date has 1 day long for TrainingTypeID=1 and 2 days for TrainingTypeID=2 (days are always in a row, like 26th and 27th march)

Forms:
- i see it as training masterlist (contentiously forms) where i can dbl click (OpenForm ="[TrainingID]= " & [TrainingID]) on it and enter
- TrainingPage where i can see every attendees like i already built with scoring via query and subform
View attachment 119121
- i would like to have a option to dbl click on attendee as well but with subform its not possible i guess. so i could see his personal page to see their answers on every question and feedback (rating) basically from 1 to 5)
-also why i need attendees page is to open it and put his responses from his list he gave me and mark all answers like i already did
View attachment 119122
but it has limitations - as its subform - i can't make it in continuously form have many emp to enter and click on 11 option groups, so can only have 1 (attendees \ responsePage per 1 employee , so thats why im stuck with not having the option to enter data for many employee at one screen - it would make entering data much easier.

to summarize about Forms. it should be:
- master list of trainings
- form of training page
- form attendee page
- entry data form

later i would try to find some solutions to save and store PDF files outside of the db. i have my db that perfectly does it , but it supports jpeg only. this PDF file is just a scan of attendance list as well as all questions \ answers \ feedback (its 3 page doc per attendees , but we usually store it as a single 30-50 page file) so attendeesID should also has 1 pdf file attached attendees.link ( to it so i could dbl click on it in TrainingPage \ Attendees page.

also i would like to build some kinda statistics pages where can i filter customers, trainers and see who has which scores on questions \ answers and feedback (1-5 rating).

and last ... i need it generates certificates after i enter employees into db. for example i had a group 10 ppl were there. i enter 10 ppl' data put all responses and feedback (hopefully within 1 page \screen) and push 1 button so it creates report. i can create report myself, but i think i can handle it. i would do it using query, find those ppl by today().. later i will put mark checkbox for already generated certificates. i guess thats it .. sorry for long txt
Do you want to print the test questions out and distribute them to the attendees and then enter their answers into the database?
 
Do you want to print the test questions out and distribute them to the attendees and then enter their answers into the database?
no, usually i have hard copy papers already printed to give them after training. when training is coming to its end i hand them over these papers for them to fill feedback \ rate and answer to questions . my job as a trainer just enter data i have on my hand in real a4 papers.
 
Do you want to print the test questions out and distribute them to the attendees and then enter their answers into the database?
oh, i just re-read your question and sorry for misunderstanding , but "yes, i do" to your question
 
oh, i just re-read your question and sorry for misunderstanding , but "yes, i do" to your question
I am going to work on your project and develop a system for you to test. It will take some time but I expect a preliminary test application within a week or so. I will attach the file for you to test at that time. I am going to use this design:

1743080129306.png

at least for now.
  1. Each customer has multiple locations and employees
  2. Each training session has multiple dates and customer employee attendees and a trainer
  3. Each training type has multiple test questions
  4. Each question has 4 options as answers and each attendance employee completes a test
  5. Each question has a correct answer and the answer is recorded
  6. Each attendee may provide feedback comments to the trainer and their comments are input by someone
Other advisors in the forum may have additional ideas as well.
 
@TipsyWolf :
Status Update:
  1. This design works very well
  2. You can register selected (or all) customer employees to any session and select the individual question set to apply to the session
  3. You are also be able to select all employees from all customers and register them. This might be helpful if you hold training sessions where anyone can register and be trained. Seminar-style training sessions for example
  4. You can select employees by job title and register only those employees (engineers only for example)
  5. You can record question answers and match against the correct answer
  6. I am working on a training test statistics query to get % correct etc.
  7. The options for each question is not a problem. You can use 4 different ones (multiple choice) or you can designate option 1 as True and Option 2 as False for True/False questions. It makes no difference.
This has been interesting for me because I have designed questionnaires before, but never actual test questions with multiple choice and graded results.

This is just an update.
 
@TipsyWolf :
Status Update:
  1. This design works very well
  2. You can register selected (or all) customer employees to any session and select the individual question set to apply to the session
  3. You are also be able to select all employees from all customers and register them. This might be helpful if you hold training sessions where anyone can register and be trained. Seminar-style training sessions for example
  4. You can select employees by job title and register only those employees (engineers only for example)
  5. You can record question answers and match against the correct answer
  6. I am working on a training test statistics query to get % correct etc.
  7. The options for each question is not a problem. You can use 4 different ones (multiple choice) or you can designate option 1 as True and Option 2 as False for True/False questions. It makes no difference.
This has been interesting for me because I have designed questionnaires before, but never actual test questions with multiple choice and graded results.

This is just an update.
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

1743191304357.png


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

1743191422250.png


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

Users who are viewing this thread

Back
Top Bottom