Query trouble

[Guest]

Hi All,

I'm building a db at work (work in a hospital) of interesting clinical
caseloads. To give the user an interface to search the records I have
designed a form with various combo boxes that give the user search options. I
also want to put a combobox on the form that lists different KEYWORDS to
search on. In the underlying table there are 3 KEYWORD fields as I thought it
would be easier to search for single KEYWORDS in separate fields rather than
try to split out upto three words in just one text box. So each patient has
upto 3 KEYWORDS in their record. On the form I want the combobox to give me a
list of ALL the KEYWORDS entered into the database. I have tried to construct
a SQL string to use as the RecordSource for the forms combobox but all I can
get are the entries from the first KEYWORD field. It's like I'm trying to get
the SQL statement to join the entries in all 3 KEYWORD fields for all records
in the db. Is this the correct way to do this and if so can you help with the
SQL construct. If not can you suggest a better method. It would also be
excellent if this list of ALL KEYWORDS used in the database does not contain
duplicates as many patients may have the same KEYWORD. If this is not do-able
I guess I'll just have the user enter their own KEYWORD and use "Like
KEYWORD" to search the KEYWORD fields.

Here's hoping someone has a solution. Many thanks in advance.
Ian.

 

[Guest]

I think it best to have all keywords in a single field separated by spaces.
Then query for keyword with this as criteria --
Like "*" & [Forms]![YourFormName]![YourControl] & "*"
With this criteria it will search for the keyword anywhere in the field.

 

[Guest]

Hi Karl,

Thanks for the prompt reply.

I can go with that as a concept except that now on my form I can't have a
combobox that gives the user a list of Keywords that are already in the db.
If I were to use that field as the lookup for the form's KEYWORD field each
row would have more than one word on its line unless it's possible to split
each word entered at the space character and then construct a list from the
ensuing single words??

Any thoughts,
Ian

I think it best to have all keywords in a single field separated by spaces.
Then query for keyword with this as criteria --
Like "*" & [Forms]![YourFormName]![YourControl] & "*"
With this criteria it will search for the keyword anywhere in the field.

Hi All,

I'm building a db at work (work in a hospital) of interesting clinical
caseloads. To give the user an interface to search the records I have
designed a form with various combo boxes that give the user search options. I
also want to put a combobox on the form that lists different KEYWORDS to
search on. In the underlying table there are 3 KEYWORD fields as I thought it
would be easier to search for single KEYWORDS in separate fields rather than
try to split out upto three words in just one text box. So each patient has
upto 3 KEYWORDS in their record. On the form I want the combobox to give me a
list of ALL the KEYWORDS entered into the database. I have tried to construct
a SQL string to use as the RecordSource for the forms combobox but all I can
get are the entries from the first KEYWORD field. It's like I'm trying to get
the SQL statement to join the entries in all 3 KEYWORD fields for all records
in the db. Is this the correct way to do this and if so can you help with the
SQL construct. If not can you suggest a better method. It would also be
excellent if this list of ALL KEYWORDS used in the database does not contain
duplicates as many patients may have the same KEYWORD. If this is not do-able
I guess I'll just have the user enter their own KEYWORD and use "Like
KEYWORD" to search the KEYWORD fields.

Here's hoping someone has a solution. Many thanks in advance.
Ian.

 

[Guest]

You said you currently have three fields for keywords. Build a table for
keywords, set primary key on the keyword field, append from field1, append
from field2, and append from field3. Use the keyword table as record source
for the control.

Hi Karl,

Thanks for the prompt reply.

I can go with that as a concept except that now on my form I can't have a
combobox that gives the user a list of Keywords that are already in the db.
If I were to use that field as the lookup for the form's KEYWORD field each
row would have more than one word on its line unless it's possible to split
each word entered at the space character and then construct a list from the
ensuing single words??

Any thoughts,
Ian

I think it best to have all keywords in a single field separated by spaces.
Then query for keyword with this as criteria --
Like "*" & [Forms]![YourFormName]![YourControl] & "*"
With this criteria it will search for the keyword anywhere in the field.

Hi All,

I'm building a db at work (work in a hospital) of interesting clinical
caseloads. To give the user an interface to search the records I have
designed a form with various combo boxes that give the user search options. I
also want to put a combobox on the form that lists different KEYWORDS to
search on. In the underlying table there are 3 KEYWORD fields as I thought it
would be easier to search for single KEYWORDS in separate fields rather than
try to split out upto three words in just one text box. So each patient has
upto 3 KEYWORDS in their record. On the form I want the combobox to give me a
list of ALL the KEYWORDS entered into the database. I have tried to construct
a SQL string to use as the RecordSource for the forms combobox but all I can
get are the entries from the first KEYWORD field. It's like I'm trying to get
the SQL statement to join the entries in all 3 KEYWORD fields for all records
in the db. Is this the correct way to do this and if so can you help with the
SQL construct. If not can you suggest a better method. It would also be
excellent if this list of ALL KEYWORDS used in the database does not contain
duplicates as many patients may have the same KEYWORD. If this is not do-able
I guess I'll just have the user enter their own KEYWORD and use "Like
KEYWORD" to search the KEYWORD fields.

Here's hoping someone has a solution. Many thanks in advance.
Ian.

 

[Guest]

Hi Karl,

Thanks for that, it seems a tidy way to solve the problem. Just for
completeness I played around SQL statements and came up with the following
which does the job but I'm not sure how "pure" it is in the eyes of the
professionals:

SELECT [Keyword]
FROM [tblPatients]
UNION
SELECT [Keyword2]
FROM [tblPatients]
UNION
SELECT [Keyword3]
FROM [tblPatients]
ORDER BY [Keyword]

Thanks again for your help, I'll certainly give your suggestion a go.

Cheers,
Ian.

You said you currently have three fields for keywords. Build a table for
keywords, set primary key on the keyword field, append from field1, append
from field2, and append from field3. Use the keyword table as record source
for the control.

Hi Karl,

Thanks for the prompt reply.

I can go with that as a concept except that now on my form I can't have a
combobox that gives the user a list of Keywords that are already in the db.
If I were to use that field as the lookup for the form's KEYWORD field each
row would have more than one word on its line unless it's possible to split
each word entered at the space character and then construct a list from the
ensuing single words??

Any thoughts,
Ian

I think it best to have all keywords in a single field separated by spaces.
Then query for keyword with this as criteria --
Like "*" & [Forms]![YourFormName]![YourControl] & "*"
With this criteria it will search for the keyword anywhere in the field.

:

Hi All,

I'm building a db at work (work in a hospital) of interesting clinical
caseloads. To give the user an interface to search the records I have
designed a form with various combo boxes that give the user search options. I
also want to put a combobox on the form that lists different KEYWORDS to
search on. In the underlying table there are 3 KEYWORD fields as I thought it
would be easier to search for single KEYWORDS in separate fields rather than
try to split out upto three words in just one text box. So each patient has
upto 3 KEYWORDS in their record. On the form I want the combobox to give me a
list of ALL the KEYWORDS entered into the database. I have tried to construct
a SQL string to use as the RecordSource for the forms combobox but all I can
get are the entries from the first KEYWORD field. It's like I'm trying to get
the SQL statement to join the entries in all 3 KEYWORD fields for all records
in the db. Is this the correct way to do this and if so can you help with the
SQL construct. If not can you suggest a better method. It would also be
excellent if this list of ALL KEYWORDS used in the database does not contain
duplicates as many patients may have the same KEYWORD. If this is not do-able
I guess I'll just have the user enter their own KEYWORD and use "Like
KEYWORD" to search the KEYWORD fields.

Here's hoping someone has a solution. Many thanks in advance.
Ian.