Follow-up to Can Access do this? Attn. John Vinson

[Stacey]

I wasn't sure if I should make a new post for this or just
reply to my original post. I didn't know if you would go
back and check under there. Thanks for your wonderful
help and patiences. I have added some further questions
below. I hope you can set me straight. Thanks.

Hi there,

I am new to Access and have been learning a lot, but I
am stuck on one aspect of the program. I am creating a
database to store student data for an elementary school.
For a lot of the fields I have combo boxes.

If you're using Combo Boxes in your Tables - the infamous
Lookup
Wizard - read the critique at
http://www.mvps.org/access/lookupfields.htm.
Most serious developers avoid these like the plague that
they are; and
most developers also avoid ever exposing table datasheets
to users.
Forms are MUCH more powerful and flexible, and it's very
easy to
create a combo box on a Form, even without using a lookup
field in
your table.

I read the critique. Thanks. Where do you store the
choices that the user can select in a combo box without
using the lookup?

An example of
this is one field records the student's begnning of the
year reading stage. The reading stages are exploring,
developing, experiencing. On the form the teacher selects
one of these for each student. I want to be able to total
up how many students are at the experiencing stage, how
many are at the developing stage, and how many are at the
exploring stage. This data is contained in one field
(through a combo box) So basically I want Access to count
how many of each choice (from the combo box)are in a
field. I feel like this should be simple, but I am having
great difficulty finding any information about it. Thanks
for any help you can provide.

I guess the question is - what is actually stored in your
Table? Is it
a Text field containing the text
strings "Experiencing", "Developing"
etc.? or is it a numeric value concealed behind the text
string in the
combo box?

A text field is what is actually stored in my table that
contains text strings "Experiencing", "Developing" etc.

What I would suggest is creating a Crosstab query with the
classID as
the row header and the reading level as the column header.
This will
give you a query datasheet resembling

Experiencing Developing Exploring
Mr. Jones 4 8 12
Ms. Ramirez 5 6 13
Ms. Phelps 3 7 11

This is exactly what I am after! When I go to create a
crosstab query as you explained, it asks for a value
field. What field do I use for that? Even following your
directions to the letter it doesn't count anything. I am
sure because I am new I am missing some important step,
but I can't seem to figure out what it is. Thanks for any
further help you can provide.

 

[John Vinson]

I wasn't sure if I should make a new post for this or just
reply to my original post. I didn't know if you would go
back and check under there.

Either way will work, but it's usually easier on everyone if you keep
the same discussion all in the same thread. I use a text-based
newsreader (Agent), not the web interface, so it's easy to see new
messages even in old threads - so do most of the other volunteers.

Thanks for your wonderful help and patiences.

I have added some further questions
below. I hope you can set me straight. Thanks.

I read the critique. Thanks. Where do you store the
choices that the user can select in a combo box without
using the lookup?

In a Table. That's really all the lookup wizard *does* - it creates a
new table and links to it. You can do it yourself without involving
the wizard.

The Combo Box would usually be based on a sorted Query selecting the
appropriate fields from the combo.

A text field is what is actually stored in my table that
contains text strings "Experiencing", "Developing" etc.

Ok - and this is NOT a lookup field, right?

What I would suggest is creating a Crosstab query with the
classID as
the row header and the reading level as the column header.
This will
give you a query datasheet resembling

Experiencing Developing Exploring
Mr. Jones 4 8 12
Ms. Ramirez 5 6 13
Ms. Phelps 3 7 11

This is exactly what I am after! When I go to create a
crosstab query as you explained, it asks for a value
field. What field do I use for that?

Use Count(*).

Even following your
directions to the letter it doesn't count anything. I am
sure because I am new I am missing some important step,
but I can't seem to figure out what it is. Thanks for any
further help you can provide.

Please open the query in its current guise, select View... SQL, and
copy and paste the SQL text to a message here. It may look like
Sanskrit (or, as I was recently and justly accused, Klingon) but it
does make sense, and should help me or someone fix the query!