一个联结表字段上的多个搜索条件 [英] Multiple search criteria on one junction table field
问题描述
我正在MS Access 2013中构建。
我有一个零件表和一个具有多对多关系的标签表。我创建了一个联结表来消除多对多问题。所以我最终得到：
tbl_Parts
PartID
PartType
部分描述
。
。
tbl_Tags
TagID
TagName
TagCategory
join_PartsToTags
ParttoTagID
PartID
TagID
我已经构建了一个表单，其中标签出现在列表框中（通过TagCategory）。用户可以在每个列表框中选择多个值。这一切都正常。
我希望用户能够通过级联这些TagCategory值来过滤他们看到的记录。因此，他们可以在第一个TagCategory中选择多个TagID，然后在第二个TagCategory中选择TagID。结果应该只是符合所有选定标记条件的部分。
例如，他们从TagCategory1中选择TagID：IVV和LVV，从TagCategory2中选择TagID：2015和2017。我希望结果只是与所有选定标签相关联的部分。
我知道如何在不同的字段上创建具有多个条件的查询，但不知道如何进行当所有标准适用于一个字段时。
感谢您的帮助。
您的问题并不是那么明确对我来说，但我怀疑你的数据库没有规范化。
相同的TagCategory在你的TblTags中出现的次数是否超过三次？如果是这样，你需要一个TagCategories表。
同样在你的TblJoinPartsTags中，不需要ParttoTagID。 PartID& TagID应该是一个联合主键（根据定义是唯一的）
也许你可以提供更多信息，例如你的关系窗口的视图（请确保它是可读的）和一些你希望你的搜索表格看起来像什么，你可能会得到更多的帮助。
Phil
INJacqui，
由于您没有提供大量信息，很难为您提供详细的帮助 - 也没有显示任何尝试自行解决此问题的方法。
但是，hte solutoin的核心是通过循环选择列表框中的选项来构建过滤字符串。这可以通过使用[ListBoxName] .ItemsSelected属性来完成。您可以在MS开发者网络中了解有关此属性的更多信息： ListBox.ItemsSelected属性。这应该提供您需要开始的所有内容。
我很乐意帮助您解决您的工作问题，但我们在这个为您工作的论坛上的习惯并不多。我们坚信，学习如何做某事的最佳方式是自己解决问题 - 我们很高兴为您指导整个过程。
希望这可以。 / blockquote>
基本思路是在第一类中查询具有这些标记的所有部分的联结表。按部分计算它们，只返回计数等于搜索条件数的那些。
对第二类做同样的事情。加入2个结果，你只剩下所有搜索过的所有类别的标签。
I am building in MS Access 2013.
I have a table for Parts and a table for Tags which have a many-to-many relationship. I have created a junction table to eliminate the many-to-many issue. So I end up with:
tbl_Parts
PartID
PartType
Part Description
.
.
tbl_Tags
TagID
TagName
TagCategory
join_PartsToTags
ParttoTagID
PartID
TagID
I have built a form where the Tags appear in list boxes (by TagCategory). The user can select multiple values in each list box. This is all working.
I want the user to be able to filter the records they see by cascading through these TagCategory values. So, they can select multiple TagIDs in the first TagCategory then TagIDs in the second TagCategory. The results should only be Parts that meet ALL the selected tag criteria.
For example, they select TagIDs: IVV and LVV from TagCategory1 and TagIDs: 2015 and 2017 from TagCategory2. I want the results to be only parts that are associated with ALL the selected tags.
I know how to create a query with multiple criteria on different fields, but no idea how to do it when all the criteria applies to one field.
Thanks for any assistance.解决方案Your Question is not all that clear to me, but I suspect your database is not normalised.
Does the same TagCategory appear more than say three times in your TblTags? If so you need a table of TagCategories.
Also in your TblJoinPartsTags, the ParttoTagID is not required. The PartID & TagID should be a joint Primary Key (Unique by definition)
Perhaps if you could give a bit more information such a a view of your relationship window (Please make sure it is readable) and some sort of indication what you would like your search form to look like, you may get some more help.
Phil
INJacqui,
It''s difficult to help you in great detail as you have not provided a lot of information--nor shown any attempts at solving this yourself.
However, the heart of hte solutoin will be for you to build your filter string by cycling thorugh the selected items in the Listbox. This would be done by using the[ListBoxName].ItemsSelected property. You can learn more about this Property from the MS Developers Network here: ListBox.ItemsSelected Property. That should provide all you need to begin.
I am glad to help you troubleshoot your work, but we are not much in the habit on this forum of doing the work for you. We firmly believe that the best way to learn how to do something is to work through the problems yourself--and we are glad to guide you through the process.
Hope this hepps.
The basic idea would be to query the junction table for all parts with those tags in the first category. Count them by part and only return those with a count equal to the number of search terms.
Do the same for the second category. Join the 2 results and you''ll be left with only the parts that have all tags for all categories searched.
这篇关于一个联结表字段上的多个搜索条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
