Searching by Keyword

[Comtech]

Hello again,



My wife asked me to create her a database to keep track of all her quilting books and magazines. I've pretty well completed the database, but I'm stuck on an issue with searching. So, there are 2 tables as follows:

tblQuiltMags:
FormID - Autonumber (Primary Key)
QuiltID - Number
Title - Short Text
Publisher - Short Text
Volume - Number
Issue - Number

tblQuiltMagsTopics:
ProjectID - Autonumber (Primary Key)
BookID - Number
Topic - Short Text
PageNo - Number
BlockName - Short Text
ProjectLink - HyperLink
ProjectVideo - HyperLink

I have a form with a subform for search results, called frmSearchResults. There is a 1 to many relationship between tblQuiltMags.FormID and tblQuiltMagsTopics.BookID. I have created 2 queries that are linked to the form. qrySearchMagsContentMain is the RecordSource for the main form (frmSearchResults), and qrySearchMagsContentSub is the RecordSource for its subform. On the subform, I have set the LinkMasterFields to FormID and LinkChildFields to BookID. When I click the search button, another small form opens up, called frmSearch, and the keywords are entered into a text box called txtKeyWords.

The SQL for each query is as follows.

qrySearchMagsContentMain:
SELECT tblQuiltMags.FormID, tblQuiltMags.QuiltID, tblQuiltMags.Title, tblQuiltMags.Publisher, tblQuiltMags.Volume, tblQuiltMags.Issue, tblQuiltMagsTopics.ProjectID, tblQuiltMagsTopics.Topic, tblQuiltMagsTopics.PageNo, tblQuiltMagsTopics.BlockName, tblQuiltMagsTopics.ProjectLink, tblQuiltMagsTopics.ProjectVideo
FROM tblQuiltMags INNER JOIN tblQuiltMagsTopics ON tblQuiltMags.FormID = tblQuiltMagsTopics.BookID
WHERE (((tblQuiltMagsTopics.Topic) Like "*" & [Forms]![frmSearch].[txtKeyWords] & "*"));

qrySearchMagsContentSub :
SELECT tblQuiltMagsTopics.BookID, tblQuiltMagsTopics.ProjectID, tblQuiltMagsTopics.Topic, tblQuiltMagsTopics.PageNo, tblQuiltMagsTopics.BlockName, tblQuiltMagsTopics.ProjectLink, tblQuiltMagsTopics.ProjectVideo
FROM tblQuiltMagsTopics
WHERE (((tblQuiltMagsTopics.BookID)=[Forms]![frmSearchResults]![FormID]) AND ((tblQuiltMagsTopics.Topic) Like "*" & [Forms]![frmSearch].[txtKeyWords] & "*"));



My problem is this. I want to be able to type in a keyword and search tblQuiltMagsTopics.Topic for that keyword, and see the results on the form. It works ok, but every time there's more than one topic in the same magazine, it shows one for each in the main form. For example, if I search for the word "block" and one magazine has 3 topics with the word block, then it will give 3 records. But all 3 will show the exact same thing, because I still see those 3 records in the subform of each main record. Is there any way to set this up so that I will only get one record on the main form, and all results on the subform for that keyword, without having duplicate records on the main. Any help would be appreciated.

Hope this makes sense. Thank you.

[Bob Fitz]

Can you post a copy of the db

[Micron]

Hope you don't mind if I pass on closely examining that right now, but since it seems you have no related foreign key (FK) to your primary (PK) I'm going to suggest the design is wrong, thus the sql doesn't mean much. If you have defined your relationships, post a pic of them - or the db itself as suggested.

[Comtech]

How do I upload the database?

[Comtech]

I tried uploading the database, but it's too big. It's 5.63MB.

[Comtech]

Micron, I think I do have a foreign key. I did Enforce Referential Integrity when I created the Relationship. I included some screenshots like you suggested, but my database was too big to upload. As well, I changed the FormID to BookID in tblQuiltMags to make them both the same. See screenshots below, and thanks again for your help.

[Welshgasman]

Try SELECT DISTINCT for main query ?

[Bob Fitz]

I tried uploading the database, but it's too big. It's 5.63MB.

I think that Welshgasman's will solve your problem but for future reference, if you need to upload a db, do a "Compact and Repair" and then "Zip" it.

[Micron]

I changed the FormID to BookID in tblQuiltMags to make them both the same.

So you had what you should have but it was oddly named. I don't see DISTINCT or DISTINCT ROW helping much. You're searching on the many side of a relationship and including fields from the one side. If there are 3 topics that match, you can't help but get 3 records, and those will include the fields from the 1 side. I think your problem is that a subform is not suitable for this. You only need a basic search form which filters a continuous or data sheet form, or use a search form for the main and sub for records that match. In the latter case, I don't think you need bound fields for the main form at all.

[Comtech]

Micron, you’re correct. SELECT DISTINCTROW did not work. I will try what you suggested. But when I run the query by itself, it gives me the 3 records, so wouldn’t it still do that if I wasn’t using a sub form?

[Micron]

Yes. If there are 3 articles that contain your keyword, why would you not want all 3, assuming they are not all absolutely identical. Would this not result in perhaps 3 different magazines with an article containing the keyword? Then you want all 3 records, no? If this isn't making sense, perhaps copy some of the query records and paste them into a post - then explain what's wrong with what you're seeing.

[Comtech]

Micron,
No. If there are 2 articles in the same magazine, they come up as 2 separate articles on the main form, and each of those articles show both items in the sub form.

[Micron]

I already said to not use a **subform? Perhaps post a zipped copy of your db as suggested, otherwise I don't see what else I can offer beyond whatever I've already said.

EDIT **at least not where the main form is bound. Maybe this will help
http://allenbrowne.com/ser-62.html

[orange]

Comtech,

As has been suggested:
- do a compact and repair to remove any bloat, then
- create a zip file, add your database, then
- attach to post.

Good luck

PS: You may get some ideas from keyword search or modified UniversalSearch

[Comtech]

Thanks for all your help, but I finally got it to work. I had too many fields selected in my main query. I changed it to only the field that I wanted to check, and I did use SELECT DISTINCT but not sure if it makes a difference or not. I just left it there.