I think that you need to change the link to the Primary Key field in
tblSITController_List. That is the value that is being stored in
tblSITLog.SIT.
Also, you can only link one field to one field in this case.
First: Open tblSitLog and look at the field SIT - what type of field is
it. If it is a number field, that is the first clue to the cause of the
problem.
You may need to write the query as follows
SELECT tblSITLog.MONTH
, tblSITLog.DAY
, tblSITLog.lkupFiscalYear
, tblSITLog.MISLE_Link
, tblSITLog.[Case Description]
, tblSITLog.[NATURE OF iNCIDENT]
, tblSITLog.[Case Title or Vsl Description]
, tblSITLog.SIT
FROM tblSITLog
WHERE SIT
(SELECT tblSITCONTROLLER_LIST.ControllerID
FROM tblSITCONTROLLER_LIST
WHERE tblSITController_List.[Name] LIKE
"*" & [Enter in Controlller Name (partial works)] & "*")
OR SIT2 IN
(SELECT tblSITCONTROLLER_LIST.ControllerID
FROM tblSITCONTROLLER_LIST
WHERE tblSITController_List.[Name] LIKE
"*" & [Enter in Controlller Name (partial works)] & "*")
OR SIT3 IN
(SELECT tblSITCONTROLLER_LIST.ControllerID
FROM tblSITCONTROLLER_LIST
WHERE tblSITController_List.[Name] LIKE
"*" & [Enter in Controller Name (partial works)] & "*")
What you really need is a redesign of your table structure where you
have a table tblSitController that has two fields
SitLogID (a copy of the primary key from tblSitLog)
SitControllerID (a copy of the primary key from tblSITController_List)
With that junction table you could have 0 to any number of controllers
for any situation.
'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
A little more info. I went into relationships and I linked the field "NAME"
from tblSITCONTROLLER_LIST to "SIT", "SIT2" AND "SIT3" of my tblSITLog. I
have 3 because there could be more than one person working on a certain case.
I still had no success, here was the query I ended up with:
SELECT tblSITLog.MONTH
, tblSITLog.DAY
, tblSITLog.lkupFiscalYear
, tblSITLog.MISLE_Link
, tblSITLog.[Case Description]
, tblSITLog.[NATURE OF iNCIDENT]
, tblSITLog.[Case Title or Vsl Description]
, tblSITLog.SIT
FROM tblSITCONTROLLER_LIST INNER JOIN tblSITLog ON
(tblSITCONTROLLER_LIST.NAME = tblSITLog.SIT) AND (tblSITCONTROLLER_LIST.NAME
= tblSITLog.SIT2) AND (tblSITCONTROLLER_LIST.NAME = tblSITLog.SIT3)
WHERE (((tblSITLog.SIT) Like "*" & [Enter in Controlller Name (partial
works)] & "*"));
Thanks again for any help you can provide me.
Steph
Scuda said:
Thanks John, I am sorry I was not more specific, it as early in the morning,
my bad.
Problem is that when I enter a name I get NO records returned. When I remove
the clause it returns EVERYTHING, which is as expected. The where clause
seems to work fine with my other fields, just not this one.
My SIT field is a text type, and yes I used a Field Lookup, to
tblSITCONTROLLER_LIST. I had tried doing as you mentioned, adding the table
to the query and joining them, I am still not getting any results.
Thanks again.
:
You don't say what the problem is.
Do you get an error message?
Do you get unexpected results?
If you remove the where clause, do you get all records returned?
What type of field is SIT (is it a number field or a text field)?
Did you use the Field Lookup when you were designing the table? If so, that
hides the real data from you and shows related values. You may need to add
the table that holds the controller name to your query and join that table
to tblSITlog using the SIT fields.
SELECT tblSITLog.MONTH
, tblSITLog.DAY
, tblSITLog.lkupFiscalYear
, tblSITLog.MISLE_Link
, tblSITLog.[Case Description]
, tblSITLog.[NATURE OF INCIDENT]
, tblSITLog.[Case Title or Vsl Description]
, tblSITLog.SIT
FROM tblSITLog
WHERE tblSITLog.SIT Like "*" &
[Enter in Controlller Name (partial works)] & "*"
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
Hello all, I have run this query with other tables (basically the same)
and
have not had a problem. Can anyone tell me if something looks wrong with
this
query? SIT is the watchstander and I want to query all incidents when they
were on watch.
Thanks in advance,
STEPH
SELECT tblSITLog.MONTH, tblSITLog.DAY, tblSITLog.lkupFiscalYear,
tblSITLog.MISLE_Link, tblSITLog.[Case Description], tblSITLog.[NATURE OF
iNCIDENT], tblSITLog.[Case Title or Vsl Description], tblSITLog.SIT
FROM tblSITLog
WHERE (((tblSITLog.SIT) Like "*" & [Enter in Controlller Name (partial
works)] & "*"));
