counting totals, but not using "like"

  • Thread starter Thread starter Scuda
  • Start date Start date
S

Scuda

Morning all,

i have a pretty simple query totaling up the Incidents for a year. The
field I am looking up is a text field, that has a lookup to another table (I
know, most people don't like that, but I have a lot of options there)

When I run the query:
SELECT Count(tblSENEIncidentLogCY.[NATURE OF DISTRESS]) AS [CountOfNATURE OF
DISTRESS]
FROM tblDISTRESS INNER JOIN tblSENEIncidentLogCY ON tblDISTRESS.DISTRESS =
tblSENEIncidentLogCY.[NATURE OF DISTRESS];

no problem, gives me a Total number of all entries in that field.

What I am trying to do is have a number of only the entries in NATURE OF
DISTRESS that has the word SAR in it. I have tried the Like "sar*" in
criteria but it comes back as a mismatch.

Any ideas what I am doing wrong? grrrrr....

Stephanie
 
Any ideas what I am doing wrong? grrrrr....

Probably. You are using a lookup! ;-)

The lookup link is probably just a number and nothing like "SAR". Lookups
hide what you are actually need to see and presents something else.

You'll need to include the field in the lookup table that actually has the
"SARs" in it and use your criteria there.

This in a major reason that most people suggest avoiding lookups.
 
If you look at the design view of tblSENEIncidentLogCY, is the [Nature Of
Distress] field actually text?

What is the Row Source of the [Nature of Distress] field?

I think you are adding to the justification for many of us to detest lookup
fields in table designs.
 
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!!

Duane Hookom said:
If you look at the design view of tblSENEIncidentLogCY, is the [Nature Of
Distress] field actually text?

What is the Row Source of the [Nature of Distress] field?

I think you are adding to the justification for many of us to detest lookup
fields in table designs.
--
Duane Hookom
Microsoft Access MVP


Scuda said:
Morning all,

i have a pretty simple query totaling up the Incidents for a year. The
field I am looking up is a text field, that has a lookup to another table (I
know, most people don't like that, but I have a lot of options there)

When I run the query:
SELECT Count(tblSENEIncidentLogCY.[NATURE OF DISTRESS]) AS [CountOfNATURE OF
DISTRESS]
FROM tblDISTRESS INNER JOIN tblSENEIncidentLogCY ON tblDISTRESS.DISTRESS =
tblSENEIncidentLogCY.[NATURE OF DISTRESS];

no problem, gives me a Total number of all entries in that field.

What I am trying to do is have a number of only the entries in NATURE OF
DISTRESS that has the word SAR in it. I have tried the Like "sar*" in
criteria but it comes back as a mismatch.

Any ideas what I am doing wrong? grrrrr....

Stephanie
Click to expand...
Click to expand...
 
Awesome Ken, thanks. i will try that.

So..i think i get it, it's ok to put the combo box on my form, use the row
source and tables THERE, but NOT in my table?

Thanks again to all.



KenSheridan via AccessMonster.com said:
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?
Click to expand...
[quoted text clipped - 25 lines]
Stephanie
Click to expand...
Click to expand...
Click to expand...
 
And it worked brilliantly, thanks again Ken. I got an answer to my problem,
and I learned allot along the way, thats why I love this forum!

KenSheridan via AccessMonster.com said:
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?
Click to expand...
[quoted text clipped - 25 lines]
Stephanie
Click to expand...
Click to expand...
Click to expand...
 
Back
Top