More dynamic searching and reporting!

  • Thread starter Thread starter Dominic H via AccessMonster.com
  • Start date Start date
D

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
 
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
 
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

[MVP] S.Clark said:
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:
Click to expand...
[quoted text clipped - 42 lines]
Dom
Click to expand...
Click to expand...
 
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


Dominic H via AccessMonster.com said:
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

[MVP] S.Clark said:
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:
Click to expand...
[quoted text clipped - 42 lines]
Dom
Click to expand...
Click to expand...
Click to expand...
 
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

Duane said:
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.
Click to expand...
[quoted text clipped - 21 lines]
Click to expand...
 
Back
Top