Big Picture Instruction Request

[malpho]

I'm trying to create a database for our non-profit that can be used to record
demographic information recorded during intake. For the data we report to the
state, there are standardized responses which I am able to list in look-up
column(some are multi-value). The problem I continue to encounter is how I
can record the number of a particular response. For instance one of the
questions is Race, and the respondant can choose from 7 responses, A. White/
Caucasian, B. Black / African American and so on. I would like a report that
gave a breakdown of the number of each response. What am I missing?

 

[John W. Vinson]

I'm trying to create a database for our non-profit that can be used to record
demographic information recorded during intake. For the data we report to the
state, there are standardized responses which I am able to list in look-up
column(some are multi-value). The problem I continue to encounter is how I
can record the number of a particular response. For instance one of the
questions is Race, and the respondant can choose from 7 responses, A. White/
Caucasian, B. Black / African American and so on. I would like a report that
gave a breakdown of the number of each response. What am I missing?

Perhaps the technique of how to create a Totals query. You don't describe your
table structure - just your requirements - so we can't describe how to create
a totals query based on *your* table. Post a description of the fields and
their datatypes if you need more help.

 

[malpho]

I have 20 fields. All of their data types are text with the exception of 1
date/time, 1 hyperlink, and 1 autonumber for the primary key. Nine of these
fields are set up as lookup columns(all "text" datatypes) as I described.
These nine are what I want to generate a report for. I wish to have totals
for each possible response in these lookup columns.

 

[John W. Vinson]

I have 20 fields. All of their data types are text with the exception of 1
date/time, 1 hyperlink, and 1 autonumber for the primary key. Nine of these
fields are set up as lookup columns(all "text" datatypes) as I described.
These nine are what I want to generate a report for. I wish to have totals
for each possible response in these lookup columns.

You're another victim of Microsoft's misleading, misdesigned so-called "Lookup
Wizard". Your table does NOT contain any of the text that you see; it contains
hidden numeric foreign keys to nine lookup tables.

You'll need to explain a bit more about how you want your report to look. You
may need nine queries to independently group and total the nine fields, I'm
not sure! A Report just based on a table starts with an entire record; you
want different subtotals for the different fields, it seems.

 

[malpho]

To give you more of an idea of where I'm at, I'm going to give you some more
background on my database. I started out w/ the 20 fields in one table as
I've described. I ran the table analyzer wizard and chose my own tables with
consideration to repeating values. This process generated an additional 14
tables, with a seperate table for each of the nine fields I want to report
on. I let the wizard create the query, so each of the tables has a query with
it.
Basically, I'd like the report to show each of the nine fields. With each
field, I'd like a breakdown showing the number of times each possible value
was chosen. For example, one of my nine fields is Gender and in the lookup
column I've created, you can select from the values "Male" and "Female". All
I want for this and the other eight similar fields is a total for how many
times each possible value was selected. Looking at my report, I would be able
to say we had x number of male clients and x number of female clients, etc.
Thanks for your help, John.

 

[Larry Daugherty]

I may be jumping in without a complete picture. My fault, not yours.
It seems that John is trying to nudge you gently along the path to a
complete solution. Suggest that you accept a partial solution and
then hope that John will pick it up again or post a new thread with
the new / remaining issue.

Those cotton-bickin' traps from Microsoft have to go. The values for
the races are in a table. That's exactly where they should be and
that table is quite appropriately referred to as a "Lookup Table"
however, lookup tables have absolutely nothing to do with "Lookup
Fields" which are an abomination. Lookup tables are not a separate
kind of object from a regular table. They are simply regular tables
used to store more or less stable lists of things for purposes of
"lookup" via a combobox.

Assuming that your primary key of tblRace is an Autonumber datatype,
change the field type of field "race" in your primary table to Long
Integer. If it doesn't already have an Autonumber Primary Key, please
add one so that this example will work. On the form with which you
manage this information, make the control for "Race" a combobox based
on a query on tblRace. When you design the query for the combobox,
tell the Combobox wizard that you want to lookup a value and then let
it hide the ID field. With your combobox design complete, return the
form to run mode. Then, when you choose a race from the list it will
be placed in the combobox. It's ID will be placed in the control ant
it's value will be written to the field in the underlying table. You
will explicitly be doing what the Microsoft trap did for you under the
hood. Now you will expect a query on that table to return a number
rather than a race. You can write queries that can apply that ID
value to return the text value of the race in question. No problem.

Once you have done that you can press on with creating Totals queries
to get the numbers of genders, races and whatever else you need to
count.

If I've misunderstood then my apologies to all concerned.

HTH