More dynamic searching and reporting!

[Dominic H via AccessMonster.com]

People - I got a great and correct response to my earlier query: Just to
refresh.. I have tables thus:
I have a table (called KEYWORDS TABLE) which looks like this:
WORD | onum | nnum |
SCHOOL | 55569 | 99999 |
ENGINEER | 77888 | 99887 |
etc
etc
BASKET | 66666 | 44444 |

And I have a another table (called KEYWORDS FILE) (in the same database) that
looks
like
VENDOR NAME | PostCode| and | so
forth|
St JOESPHS SCHOOL | SL1 8UU | OTHER | STUFF |
BREAk ENGINEER school LTD | TN1 8II | | |

I want to create a query that will look for all the rows in the keywords file
that
contain the words in field Word of keywords table.

I can get the values by using this query (from Michel Walsh)
SELECT [KEYWORDS FILE].*
FROM [KEYWORDS FILE]
WHERE (((Exists (select * from [Keywords table]
where [KEYWORDS FILE].[VENDOR NAME] LIKE "*" & [KEYWORDS
TABLE].[KEYWORD] & "*"))<>False));

What I (sorry my boss now wants) want to report is all the key words that
were found. So for example, the datasheet view would look like:
VENDOR NAME |POST CODE | STUFF |
KEYWORDS
ST Joesphs School | SL1 8UU | uhuh
| SCHOOL
BREAK ENGINEERING SCHOOL LTD | BR1 TTT | OOPS | SCHOOL,ENGINEER

Is this possible? If so.. please help!

Best regards

Dom

 

[[MVP] S.Clark]

If 'Report' means an Access Report Definition object, then base the report
upon your query, and show the Keyword field. (This may require adding a link
to the Keywords table, and showing the WORD field.)

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
(e-mail address removed)
www.fmsinc.com/consulting

 

[Dominic H via AccessMonster.com]

OK - I'll clarify it a bit.

I want to view the datasheet - so this will be a straight query.

In english, I want to view all the records from the vendor name, and all the
keywords that have been matched against that vendor name.

Apologies if this is a little basic!

Regards

Dom

If 'Report' means an Access Report Definition object, then base the report
upon your query, and show the Keyword field. (This may require adding a link
to the Keywords table, and showing the WORD field.)

People - I got a great and correct response to my earlier query: Just to
refresh.. I have tables thus:

[quoted text clipped - 42 lines]

Dom

 

[Duane Hookom]

Try create a query like:

SELECT T.*, F.*
FROM [KEYWORDS TABLE] T, [KEYWORDS FILE] F
WHERE F.[VENDOR NAME] Like "*" & T.WORD & "*";

Then use a generic concatenate function to combine your WORD values into a
single expression.
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane
--
Duane Hookom
MS Access MVP

OK - I'll clarify it a bit.

I want to view the datasheet - so this will be a straight query.

In english, I want to view all the records from the vendor name, and all
the
keywords that have been matched against that vendor name.

Apologies if this is a little basic!

Regards

Dom

If 'Report' means an Access Report Definition object, then base the report
upon your query, and show the Keyword field. (This may require adding a
link
to the Keywords table, and showing the WORD field.)

People - I got a great and correct response to my earlier query: Just
to
refresh.. I have tables thus:

[quoted text clipped - 42 lines]

Dom

 

[Dominic H via AccessMonster.com]

Duane

Splendid - Apologies for not looking sooner - I've been busy!

This works and has given me a good basis for future queries - once again -
thanks for your help!

Dom

Try create a query like:

SELECT T.*, F.*
FROM [KEYWORDS TABLE] T, [KEYWORDS FILE] F
WHERE F.[VENDOR NAME] Like "*" & T.WORD & "*";

Then use a generic concatenate function to combine your WORD values into a
single expression.
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

OK - I'll clarify it a bit.

[quoted text clipped - 21 lines]