please help me with db, stuck with option group

[LarryE]

Occasionally, when building a database for myself, I violate rules like this one and I am ALWAYS sorry. It is no more difficult to separate the name parts than it is to mush them together and once you mush them together, separating the components is a bitch and will always result in data anomalies so forget your Dear Mr. Jones letters or sorting by last name.

That is true. The OP had them together, but I am going to separate them into LastName and FirstName.

 

[TipsyWolf]

That is true. The OP had them together, but I am going to separate them into LastName and FirstName.

then i have to add - we have a middle name as well. its father's name and its very much important for us as name itself

 

[Pat Hartman]

then i have to add - we have a middle name as well. its father's name and its very much important for us as name itself

Make as many discrete fields as you need. Typically, I use:
Prefix (Mr, Dr, Mrs, etc)
FName
MName
LName
Suffix (Jr, Sr, Exq, etc)

This gives you all the flexibility you need to make nicely addressed snail mail and letters as well as providing sorting ability.

 

[LarryE]

then i have to add - we have a middle name as well. its father's name and its very much important for us as name itself

OK

 

[LarryE]

1. How many participants do you normally have for each training session over 2 days?
2. How many rating questions do you have for each participant?
3. How many test questions do you have for each participant to complete for each training session?

The reason I am asking is because this database could become enormous over time because for each training session, each participant will complete numerous test questions and numerous rating questions. I just wanted to warn you. That is also why it may take a long time to actually get a working model for you to test. If you multiply the number of training sessions you conduct over time X the number of participants you normally have X the number of test questions + the number of rating questions, you can see the number of records in the file could get enormous.

 

[TipsyWolf]

How many participants do you normally have for each training session over 2 days?

its 5-20 usually. per trainer. each trainer might have 1-4 training sessions per month. there are 10-15 trainers.

How many rating questions do you have for each participant?

9

How many test questions do you have for each participant to complete for each training session?

11-13. depends on TrainingTypeID=1 or 2.

 

[TipsyWolf]

this database could become enormous

i will try to change field type where its possible to optimize it. when i know its responses like 1 to 9, i set it as "byte" so it doesnt take too much space and run queries faster i guess.

 

[TipsyWolf]

i tired almost dozens of design structure and this works for me like it should with no mass records in query because i did bad job with relationship set up.

 

[The_Doc_Man]

With regard to the FullName question, my genealogy database tables do not contain such a field. They have PFX (Prefix title), First Name, Middle Name, Last Name, and SFX (Suffix title). However, when I am looking things up on a form, I have a QUERY that calls a text-string function that merges the name parts and removes extraneous spaces, tabs, zero-length-strings, and occasionally, nulls. (The nulls come in because of the unique structure of a GEDCOM file for genealogy research.) That function is never called for bulk processing, but forms generally are limited-processing cases, rarely more than 20 names at a time. It also gets used in reports and when I'm building the tree diagrams using Excel or the "history book" using Word. In each case, the full name is used only rarely.

 

[LarryE]

View attachment 119252

i tired almost dozens of design structure and this works for me like it should with no mass records in query because i did bad job with relationship set up.

This is the design I am using. I did remove the Trainer table relationship with the TrainingSessions table because it served no purpose. I still assign a Trainer to each session but it is no longer a required Foreign Key relationship.