Please help, Look Up Wizard Field Value Does Cannot be Selected

[Mikeg]

Hi,



I am trying to create a drop down for the following questions to equal an answer, and answer then equals a value that I use to come up with an overall score. The answers are Needs Work, Good, Great, and N/A. Both Good and N/A are worth 2 Points but it appears that I cannot make them both worth 2, since it won't let me select N/A in the table. I assume it's because they are both worth the same value. So I am trying to find a work around and have come to a dead end and would appreciate any feedback and help please and thank you.




I am not the best at using Access, and I know I could have down the calculations from a query, which I have done aswell but have been stuck.

[CJ_London]

best not to do this in a table, just on the form. You need a separate table

PK...Assessment..Score
1.....Needs Work...1
2.....Good............2
3.....Great............2.5
4.....N/A..............2

Store the PK in your table and calculate in a query

[Mikeg]

Thank you. I will try this and create a form.

[Mikeg]

I created a Table and stored my PK on it. I created a relationship so that I can try and pull in the score. I can't seem to manage how to get just the score to appear on the back end in order to calculate it all. May I please have additional guidance. I really appreciate it.

[Micron]

You really need to read up on database normalization. You took the advice to heart but then showed a spreadsheet design for the observations table. Spreadsheet format (wide and not tall) is not suitable for databases. Plus there is no way that the ID would be in the same table 3x so the relationship is invalid, sorry to say. Maybe read up and make sure you understand normalization, but I strongly suggest you study naming as well. Some fairly bad examples there

Might as well give you the whole enchilada...

Normalization Parts I, II, III, IV, and V
http://rogersaccessblog.blogspot.com...on-part-i.html
and/or
http://holowczak.com/database-normalization/

Entity-Relationship Diagramming: Part I, II, III and IV
http://rogersaccessblog.blogspot.com...ng-part-i.html

How do I Create an Application in Microsoft Access?
http://rogersaccessblog.blogspot.com...cation-in.html

Important for success:
Naming conventions - http://access.mvps.org/access/general/gen0012.htm
https://www.access-programmers.co.uk...d.php?t=225837

What not to use in names - http://allenbrowne.com/AppIssueBadWord.html

About Auto Numbers
- http://access.mvps.org/access/general/gen0025.htm

The evils of lookup fields - http://access.mvps.org/access/lookupfields.htm
Table and PK design tips - http://www.fmsinc.com/free/newtips/primarykey.asp
About calculated table fields - http://allenbrowne.com/casu-14.html
About Multi Value Fields -http://www.mendipdatasystems.co.uk/multivalued-fields/4594468763

[CJ_London]

agree with micron, do read up to gain a better understanding how databases should be designed (they do not use spreadsheet principles). Based on what you have shown, you would have four tables

1. Observation - fields down to 'observed by'

2. activities - the remaining 3 fields but listed in rows

activityPK...ActivityName

3. Assessments - as already suggested

4. scores
ScorePK
ObservationFK
ActivityFK
AssessmentFK


Main form based on Observations
Subform based on scores where activityFK and AssessmentFK would be combos

you can use code to generate new score records for each observation so the activity is prepopulated and the user only has to select an assessment

[Mikeg]

I appreciate the Help Micron and Ajax. I will read up on all this and get this cleaned up. I have a better understanding now.

Thank you!