帮助查询和子表单 [英] Help with Query and Subform

问题描述

大家好，


我需要创建一个基于关键字和主要类别进行搜索的查询。我的表格设置如下：

tblLiteratureArticles：

缩写（FK）

ArticleID（PK）

作者

标题

等...

tblArticleKeyword（加入表btn tblLiteratureArticles& tblKeywords）

ArticleID

关键字

（这两个创建复合键）

tblKeywords

关键字（PK）

tblLitCategories（与tblLiteratureArticles的一对多关系）

MajorCategory

缩写（PK）

选中（是/否字段）

我希望我能清楚地建立关系。好的，所以我有一个带有文本框的表单（Form1），供用户输入用逗号分隔的关键字。我还有一个基于更新查询的子表单（subfrmLitCategories），它具有[MajorCategory]字段和[Selected] Yes / No字段，允许用户选择主要类别的任意组合。


我需要有一个基于查询的子表单，该查询将根据文本框中键入的关键字和子表单中选择的主要类别过滤记录。这可能吗？我的表关系是否正确以创建查询来完成这项工作？如果是这样，我该怎么做？


感谢您的帮助/建议！

解决方案

大家好，


我需要创建一个基于关键字和主要类别进行搜索的查询。我的表格设置如下：

tblLiteratureArticles：

缩写（FK）

ArticleID（PK）

作者

标题

等...

tblArticleKeyword（加入表btn tblLiteratureArticles& tblKeywords）

ArticleID

关键字

（这两个创建复合键）

tblKeywords

关键字（PK）

tblLitCategories（与tblLiteratureArticles的一对多关系）

MajorCategory

缩写（PK）

选中（是/否字段）

我希望我能清楚地建立关系。好的，所以我有一个带有文本框的表单（Form1），供用户输入用逗号分隔的关键字。我还有一个基于更新查询的子表单（subfrmLitCategories），它具有[MajorCategory]字段和[Selected] Yes / No字段，允许用户选择主要类别的任意组合。


我需要有一个基于查询的子表单，该查询将根据文本框中键入的关键字和子表单中选择的主要类别过滤记录。这可能吗？我的表关系是否正确以创建查询来完成这项工作？如果是这样，我该怎么做？


感谢您的帮助/建议！

我能够创建一个查询来根据主要类别和ONE关键字过滤记录。但是，我需要允许用户输入多个以逗号分隔的关键字。有谁知道我需要对SQL做什么改变？


SELECT

tblLiteratureArticles.Abbreviation，

tblLiteratureArticles.ArticleID，

tblLiteratureArticles.PrincipalAuthor，

tblLiteratureArticles.TITLE，

tblLiteratureArticles.Journal，

tblLiteratureArticles.Volume，

tblLiteratureArticles.Number，

tblLiteratureArticles.PAGES，

tblLiteratureArticles.DATE，

tblLiteratureArticles.OtherAuthors，

tblLiteratureArticles.Link，

tblArticleKeyword.Keyword

FROM

（tblLitCategories INNER JOIN tblLiteratureArticles ON tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation） INNER JOIN tblArticleKeyword ON tblLiteratureArticles.ArticleID = tblArticleKeyword。[文章ID]

WHERE

（（（tblArticleKeyword.Keyword）= [Forms]！[Form1]！[txtKeywords] ）AND（（tblLitCategories.Selected）= True））;

我能够创建一个查询来根据主要类别和ONE关键字过滤记录。但是，我需要允许用户输入多个以逗号分隔的关键字。有谁知道我需要对SQL做什么改变？


SELECT

tblLiteratureArticles.Abbreviation，

tblLiteratureArticles.ArticleID，

tblLiteratureArticles.PrincipalAuthor，

tblLiteratureArticles.TITLE，

tblLiteratureArticles.Journal，

tblLiteratureArticles.Volume，

tblLiteratureArticles.Number，

tblLiteratureArticles.PAGES，

tblLiteratureArticles.DATE，

tblLiteratureArticles.OtherAuthors，

tblLiteratureArticles.Link，

tblArticleKeyword.Keyword

FROM

（tblLitCategories INNER JOIN tblLiteratureArticles ON tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation） INNER JOIN tblArticleKeyword ON tblLiteratureArticles.ArticleID = tblArticleKeyword。[文章ID]

WHERE

（（（tblArticleKeyword.Keyword）= [Forms]！[Form1]！[txtKeywords] ）AND（（tblLitCategories.Selected）= True））;

嗨。


我想带你回到岗位 -
http://www.thescripts.com/forum/thread671666.html msg＃4


将此功能添加到公共模块。 SQL将如下所示

展开 | 选择 | Wrap | 行号

嗨。


我想带你回到岗位 -

http://www.thescripts.com/forum/thread671666.html msg＃4


将此函数添加到公共模块。 SQL将如下所示

展开 | 选择 | Wrap | 行号

Hi everybody,

I need to create a query that will search based on keywords and major categories. My tables are set-up as follows:
tblLiteratureArticles:

Abbreviation(FK)
ArticleID(PK)
Author
Title
etc...

tblArticleKeyword (Join Table btn tblLiteratureArticles & tblKeywords)

ArticleID
Keyword
(these two create a composite key)

tblKeywords

Keyword(PK)

tblLitCategories (one-to-many relationship with tblLiteratureArticles)

MajorCategory
Abbreviation(PK)
Selected(Yes/No field)

I hope I made the relationships clear. Okay, so I have a form (Form1) with a text box for users to type in keyword(s) separated by commas. I also have a subform based on an update query (subfrmLitCategories), which has the [MajorCategory] field and the [Selected] Yes/No field to allow users to choose any combination of major categories.

I need to have a subform based on a query that will filter records based on the keywords typed in the text box and the major categories chosen in the subform. Is this possible? Are my table relationships correct to create a query to do this job? If so, how do I do it?

Thanks for your help/suggestions!

解决方案

Hi everybody,

I need to create a query that will search based on keywords and major categories. My tables are set-up as follows:
tblLiteratureArticles:

Abbreviation(FK)
ArticleID(PK)
Author
Title
etc...

tblArticleKeyword (Join Table btn tblLiteratureArticles & tblKeywords)

ArticleID
Keyword
(these two create a composite key)

tblKeywords

Keyword(PK)

tblLitCategories (one-to-many relationship with tblLiteratureArticles)

MajorCategory
Abbreviation(PK)
Selected(Yes/No field)

I hope I made the relationships clear. Okay, so I have a form (Form1) with a text box for users to type in keyword(s) separated by commas. I also have a subform based on an update query (subfrmLitCategories), which has the [MajorCategory] field and the [Selected] Yes/No field to allow users to choose any combination of major categories.

I need to have a subform based on a query that will filter records based on the keywords typed in the text box and the major categories chosen in the subform. Is this possible? Are my table relationships correct to create a query to do this job? If so, how do I do it?

Thanks for your help/suggestions!

I was able to create a query to filter records based on major categories and ONE keyword. However, I need to allow users to type in more than one keyword, separated by commas. Does anyone know what changes I need to make to the SQL?

SELECT
tblLiteratureArticles.Abbreviation,
tblLiteratureArticles.ArticleID,
tblLiteratureArticles.PrincipalAuthor,
tblLiteratureArticles.TITLE,
tblLiteratureArticles.Journal,
tblLiteratureArticles.Volume,
tblLiteratureArticles.Number,
tblLiteratureArticles.PAGES,
tblLiteratureArticles.DATE,
tblLiteratureArticles.OtherAuthors,
tblLiteratureArticles.Link,
tblArticleKeyword.Keyword
FROM
(tblLitCategories INNER JOIN tblLiteratureArticles ON tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation) INNER JOIN tblArticleKeyword ON tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
WHERE
(((tblArticleKeyword.Keyword)=[Forms]![Form1]![txtKeywords]) AND ((tblLitCategories.Selected)=True));

I was able to create a query to filter records based on major categories and ONE keyword. However, I need to allow users to type in more than one keyword, separated by commas. Does anyone know what changes I need to make to the SQL?

SELECT
tblLiteratureArticles.Abbreviation,
tblLiteratureArticles.ArticleID,
tblLiteratureArticles.PrincipalAuthor,
tblLiteratureArticles.TITLE,
tblLiteratureArticles.Journal,
tblLiteratureArticles.Volume,
tblLiteratureArticles.Number,
tblLiteratureArticles.PAGES,
tblLiteratureArticles.DATE,
tblLiteratureArticles.OtherAuthors,
tblLiteratureArticles.Link,
tblArticleKeyword.Keyword
FROM
(tblLitCategories INNER JOIN tblLiteratureArticles ON tblLitCategories.Abbreviation = tblLiteratureArticles.Abbreviation) INNER JOIN tblArticleKeyword ON tblLiteratureArticles.ArticleID = tblArticleKeyword.[Article ID]
WHERE
(((tblArticleKeyword.Keyword)=[Forms]![Form1]![txtKeywords]) AND ((tblLitCategories.Selected)=True));

Hi.

I''d like to bring your back to the post -

http://www.thescripts.com/forum/thread671666.html msg#4

Add this function to a public module. SQL will look like this

Expand|Select|Wrap|Line Numbers

Hi.

I''d like to bring your back to the post -

http://www.thescripts.com/forum/thread671666.html msg#4

Add this function to a public module. SQL will look like this

Expand|Select|Wrap|Line Numbers

这篇关于帮助查询和子表单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！