Stephanie:
Its not the fact that you are looking up values form the tblDISTRESS table
per se which is the problem but that by using the 'lookup wizard' when
designing the table the values being looked up are shown in the NATURE OF
DISTRESS field rather than its true underlying value, which is almost
certainly a long integer number. Its this which causes the confusion.
The tblDISTRESS table will probably have two fields, one of which, DISTRESS,
is its numeric primary key. It will probably also have another field, which
is a text data type and contains the text values for each type of distress.
Its this field whose value is being looked up. Lets assume for the moment
that its called DISTRESS CATEGORY.
So to query by a value in the DISTRESS CATEGORY field you need to include
this text field in the query's WHERE CLAUSE, in which case the query's SQL
would look like this:
SELECT COUNT(tblSENEIncidentLogCY.[NATURE OF DISTRESS])
AS [CountOfNATURE OF DISTRESS]
FROM tblDISTRESS INNER JOIN tblSENEIncidentLogCY
ON tblDISTRESS.DISTRESS =
tblSENEIncidentLogCY.[NATURE OF DISTRESS]
WHERE tblDISTRESS.[DISTRESS CATEGORY] LIKE "sar*";
If you do this in SQL view and then switch to design view you'll see how its
built in design view.
BTW when simply counting rows you can just use COUNT(*) rather than COUNT
(tblSENEIncidentLogCY.[NATURE OF DISTRESS]). Its only necessary to include
the field name if it can contain Nulls and you want to exclude them from the
count.
For future reference instead of using the lookup wizard in table design just
use a long integer number data type if the primary key of the 'referenced'
table is also a long integer number, e.g. an autonumber. For data entry you
can still 'look up' from a list of text values, but do this in a form based
on the table by using a combo box. Data should always be entered via forms,
never in a table's raw datasheet view. In this case the combo box would be
set up like this:
ControlSource: [NATURE OF DISTRESS]
RowSource: SELECT DISTRESS , [DISTRESS CATEGORY] FROM tblDISTRESS ORDER
BY [DISTRESS CATEGORY];
BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm
If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box. You can do this in a form while keeping your current
'lookup field'; in fact you'll probably find that if you use the form wizard
it will create the combo box for you. Just be aware that if you look at the
table you are not seeing the field's real values.
In a report join the tables in a query and return the [DISTRESS CATEGORY]
field. You can then include a control bound to this field in the report.
Ken Sheridan
Stafford, England
Thanks guys, yes I am truly seeing why.
The Row Source is tblDISTRESS, Type Table/Query, and a Combo Box actually.
So, 2 questions if I may:
1) am I screwed?
2) What is my alternative to a lookup? I mean, I have probably 60 different
options, should I type them all in?
Thanks again!!
If you look at the design view of tblSENEIncidentLogCY, is the [Nature Of
Distress] field actually text?
[quoted text clipped - 25 lines]
