Where To Set Up Fields

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.

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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ridgerunner" wrote in message
...
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.

On Fri, 21 Mar 2008 09:37:02 -0700, ridgerunner
wrote:

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.
--

John W. Vinson [MVP]

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?

"Allen Browne" wrote:

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

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

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"ridgerunner" wrote in message
...
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.

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" wrote:

On Fri, 21 Mar 2008 09:37:02 -0700, ridgerunner
wrote:

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.
--

John W. Vinson [MVP]

On Fri, 21 Mar 2008 16:10:01 -0700, ridgerunner
wrote:

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.

"John W. Vinson" wrote:

On Fri, 21 Mar 2008 09:37:02 -0700, ridgerunner
wrote:

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.
--

John W. Vinson [MVP]

--

John W. Vinson [MVP]

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.

"John W. Vinson" wrote:

On Fri, 21 Mar 2008 16:10:01 -0700, ridgerunner
wrote:

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.

"John W. Vinson" wrote:

On Fri, 21 Mar 2008 09:37:02 -0700, ridgerunner
wrote:

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.
--

John W. Vinson [MVP]

--

John W. Vinson [MVP]

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?

"ridgerunner" wrote:

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.

"John W. Vinson" wrote:

On Fri, 21 Mar 2008 16:10:01 -0700, ridgerunner
wrote:

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.

"John W. Vinson" wrote:

On Fri, 21 Mar 2008 09:37:02 -0700, ridgerunner
wrote:

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.
--

John W. Vinson [MVP]

--

John W. Vinson [MVP]