Where To Set Up Fields

[ridgerunner]

If I need to design a form that will allow the simultaneous display of all
the items a person will assign a rating to, do I need to set up fields for
all of the items in the main table?

The main table would be something like:
Location Number
Date
Category
Item
Score

Where the item is something like "Are all shelves fully stocked". Even
though this sounds like a question, the item is given a numeric score. There
are approximately 50 items that fall under 5 or 6 categories and all items
must be rated every time. I do not think there is a great possibility that
the items will change, but one never knows. The items need to be grouped by
category on the form.

 

[Allen Browne]

See Duane Hookom's sample survey database:
http://www.rogersaccesslibrary.com/Otherdownload.asp?SampleName='At Your Survey 2000'

It's a great example of how to set up a question and answer database.

 

[John W. Vinson]

If I need to design a form that will allow the simultaneous display of all
the items a person will assign a rating to, do I need to set up fields for
all of the items in the main table?

The main table would be something like:
Location Number
Date
Category
Item
Score

Where the item is something like "Are all shelves fully stocked". Even
though this sounds like a question, the item is given a numeric score. There
are approximately 50 items that fall under 5 or 6 categories and all items
must be rated every time. I do not think there is a great possibility that
the items will change, but one never knows. The items need to be grouped by
category on the form.

You should consider three tables, not just one:

Ratings
RatingID <autonumber primary key>
LocationNumber
RatingDate (don't use Date as a fieldname, it gets confused with the Date()
function)
PersonID < who's being rated>
Comments
<maybe other fields about this day's rating>

RatingItems
ItemID <autonumber primary key>
Category <text>
Item <text>

Results
RatingID <long integer link to Ratings>
ItemID <long integer link to RatingItems>
Rating < how well was this item done on this particular evaluation>

For display you would use a Form based on ratings, to indicate the
circumstances of the rating as a whole; with a Subform based on Results, using
a combo box or two to select the rating items.

 

[ridgerunner]

Thank you Allen. I have looked at the sample survey database, but I guess I
am not advanced enough to follow everything. It seems way more complicated
than what I need. Any other thoughts?

 

[ridgerunner]

Thank you John. The main form needs to based on the location. Each location
is rated on the same items everytime and that is how the rater is used to
completing the current form in Excel. We are trying to convert this
application to Access. What would change in your suggestions below for the
form to be based on location?

 

[John W. Vinson]

Thank you John. The main form needs to based on the location. Each location
is rated on the same items everytime and that is how the rater is used to
completing the current form in Excel. We are trying to convert this
application to Access. What would change in your suggestions below for the
form to be based on location?

Just take out the PersonID field, since you're rating a location rather than a
person.

If you want to see all the ratings onscreen before any of them are filled in
you can base the subform on a query joining RatingItems to Results by ItemID;
use a Left Join in the query (click the join line and choose the option "Show
all records in RatingItems and matching records in Results"). Be sure you
select both ItemID fields and use RatingID as the master/child link field.

 

[ridgerunner]

I hope this question makes sense. I do need to see all the ratings on screen
before filling them in. How can I capture the item description on the form
from the RatingItems table into the main table when the item is scored? I am
trying to avoid having the user look up the item in a drop down box.
Thanks for your patience.

 

[ridgerunner]

I have the form working with the ratings showing on screen before they are
scored, however, the query and the form are not updatable. I have checked
primary and index keys and I think they are all fine. Should I make the form
showing the ratings a subform and set up another subform, beside the first
one, to contain the data that needs to be added to the main table?

I hope this question makes sense. I do need to see all the ratings on screen
before filling them in. How can I capture the item description on the form
from the RatingItems table into the main table when the item is scored? I am
trying to avoid having the user look up the item in a drop down box.
Thanks for your patience.

Thank you John. The main form needs to based on the location. Each location
is rated on the same items everytime and that is how the rater is used to
completing the current form in Excel. We are trying to convert this
application to Access. What would change in your suggestions below for the
form to be based on location?

Just take out the PersonID field, since you're rating a location rather than a
person.

If you want to see all the ratings onscreen before any of them are filled in
you can base the subform on a query joining RatingItems to Results by ItemID;
use a Left Join in the query (click the join line and choose the option "Show
all records in RatingItems and matching records in Results"). Be sure you
select both ItemID fields and use RatingID as the master/child link field.