另一个部分匹配问题...... [英] yet another partial match question...
问题描述
表：
[ID]; [str_status]; [str_nextallowedstatus]
1;收到; 3,4,11
2;回 ; 3,4,5,21
3;储存; 4,5,11,23
4;发货; 2
5;停止服务 ; 12,13,21
...
10; moredatat; 3,5,6
11; boohoo; 33,93,53
...等......
好的，这是'问题：
我需要完全匹配部分字符串=" 3"在[str_nextallowedsatus]字段中;因此，仅返回带有3的记录。
ie：[ID] = 1，[ID] = 2且[ID] = 10但NOT [ID] = 3，[ID] = 5，[ID] = 11等。 。
来自我迄今为止发现的google和搜索是使用LIKE和*的标准。最终返回所有带有3的记录。在它们中，无论它是3，33，还是13，它们都是3。 ;但是，我可能需要搜索类似13的值。或33或33。在某些时候。
我用它来过滤一个组合框，真的不想建立另一个允许下一个状态设置的表....记录每个允许的子字符串...想象一下，对于[ID] = 1，会有三个记录，[ID] = 2会有四个记录等...
我正在使用VBA根据当前选择的记录设置组合框的记录源。
您应该规范化数据而不是以这种方式存储它。这将使查询变得更加容易。
@Rabbit
兔子，
谢谢你那个建议;但是，上面的数据是标准化的。
ie：
这很像一个库或其他库存管理数据库，事实上，这就是我开始的框架与 - 不要重新发明轮子：
- 具有独特制造细节的表格
- 表格中包含与[manufacture_id]链接的独特产品详细信息。这样一个制造商有多个产品然后我可以简单地通过manufacture_id查询并返回他们所有的产品
- 具有唯一资产详细信息（即序列号）的表，链接回[product_id]。 br />
- 仅包含允许位置的表（例如building-1，building-2）。这是为了标准化位置命名。
- 仅包含允许的用户详细信息的表
- 具有允许状态的表（例如问题中给出的）。这是为了标准化状态命名。
- 包含事件历史记录的表，链接回[asset_id]，[user_id]，[status_id]（此问题中又称：[ID]），[location_id ]，[History_Date]条目和[评论]。我甚至使用此表来确定资产的最后状态，方法是查询资产作为记录集并查看最后一条记录中的数据（eof）。
注意所有[* _ID]是表格之间的foreign_keys。
有问题的表格是针对状态的。库存项目的每次使用相同的措辞（然而，[status_id]实际上是仅在某人决定将OutOfService改变为Out of Order的情况下使用的主要密钥。存档可能会使信息变平，但这又是另一次。
现在，当用户打开表单为资产创建新的历史事件时 - 窘境就是当它从未处于已发货（[ID] = 4）状态或已修复状态时，允许返回（[ID] = 2）的项目的记录条目是没有意义的。 ;如果从不OutOfService。那么，最好不要在组合框的可用列表中具有不合逻辑的状态。
对于以下所有解决方案：资产已被找到，历史表已被查询，记录集仅处理与感兴趣的资产相关的记录。
我的解决方案1：
厕所在库存项目的当前状态下，然后使用巨大的VBA SELECT CASE（条件）/ Case / CaseElse尝试处理当前库存状态和下一个逻辑上允许的状态的每种可能组合。每次添加新条件时我都必须进入代码...这不仅耗费时间，而且需要大量的测试和重新测试，当我离开时，对于下一个程序员来说是一场噩梦！我每天的时间都非常有限......
我的解决方案2：
构建一个表，我有一个记录链接状态[ID]和下一个允许状态[ID];因此，拥有每个允许状态的记录（即在此示例中，对于[ID] = 1，将有三个记录条目{1,3}; {1,4}; {1,11}。然后我可以构建一个加入两个表之间的查询，是的。现在这可能是更规范化的方法;但是，它看起来像解决方案一样笨拙1.为一条记录创建3条数据记录，或者甚至每条记录创建5或6条记录记录。我可以自动化表格条目;但是，这似乎不是一个优雅的解决方案。
我的解决方案3：
给定在问题中。每个允许状态的1条记录，其中一个字段具有下一个逻辑上允许的状态。
在这种情况下，我不担心能够构建查询基于[str_nextallowedstatus]字段。它只是一个字段，用于限制下一个资产历史记录的状态表中组合框中提供的选项。
SO，现在回到原始问题。
Z
Table: 这篇关于另一个部分匹配问题......的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
解决方案二是正确的方法，因为它规范化数据并产生最优化的最简单的查询。你的表征更复杂是不正确的。它实际上更简单，它只是更多的记录。但它使其他一切变得更容易。
你说它用三条记录代表一条记录。但这是不正确的。你现在拥有的实际上是三个记录在一条记录中的信息。这就是使查询更复杂的原因。
[ID] ; [str_status] ; [str_nextallowedstatus]
1 ; Received ; 3,4,11
2 ; Returned ; 3,4,5,21
3 ; Storage ; 4,5,11,23
4 ; Shipped ; 2
5 ; OutOfService ; 12,13,21
...
10 ; moredatat ; 3,5,6
11 ; boohoo ; 33,93,53
... etc...
Ok, here''s the question:
I need to match exactly the partial string = "3" in the [str_nextallowedsatus] field; thus, returning only the records with "3"
i.e: [ID]=1, [ID]=2 and [ID]=10 but NOT [ID]=3, [ID]=5, nor [ID]=11 etc...
from what I''ve found so far with google and the search are criteria using LIKE and "*" which endup returning all the records with a "3" in them no matter if it is "3", "33", or "13" ; HOWEVER, I may need to search for a value like "13" or "33" at some point.
I am using this to filter a combo-box and really don''t want to build yet another table where the allowed next status is setup.... one record for each allowed sub-string... imagine, for [ID]=1, there would be three records, [ID]=2 would have four records, etc...
I am using VBA to set the record source for the combo-box based upon the currently selected record.
You should normalize you data instead of storing it this way. It will make querying much easier.
@Rabbit
Rabbit,
Thank you for that suggestion; however, the overlying data is normalized.
ie:
This is much like a library or other inventory management database, as a matter of fact, that is the framework I started with - don''t re-invent the wheel:
- Table with the unique manufacture details
- Table with the unique product details linked back to the [manufacture_id]. That way one manufacture has multiple products then I can simply query by manufacture_id and return all of their prod
- Table with the unique asset details (ie serial number), linked back to the [product_id].
- Table with just allowed locations (such as building-1, building-2). This is to standardize the location naming.
- Table with just the allowed user details
- Table with the allowed states (such as given in the question). This is to standardize the status naming.
- Table with event history that links back to the [asset_id], [user_id], [status_id] (aka:[ID] in this question), [location_id], [History_Date] of entry and a [comment]. I even use this table to determine the last status of the asset by querying for the asset as a record set and looking a the data in the last record (eof).
Note all of the [*_ID] are foreign_keys between tables.
The table in question is for the "status" of the inventory item so that the same wording is used every time (however, the [status_id] is actually the primary key that is used just-in-case someone decides to change "OutOfService" to "Out of Order." An archive might flatten the information but that''s for another time.
Now, when the user has the form open to create a new history event for the asset - the quandary is that it doesn''t make sense to allow a record entry for an item that is "returned" ([ID]=2) when it was never in the "shipped" ([ID]=4) state or "repaired" if never "OutOfService." So, it would be better to not have the illogical status in the available list of the combo-box.
For all of the following solutions: The asset has already been found, the history table has been queried, and the record set deals only with those records related to the asset of interest.
My solution 1:
Looking at the current status of the inventory item in question, then using a huge VBA SELECT CASE (condition)/Case/CaseElse in an attempt to handle every possible combination of current inventory status and the next logically allowed status. I would have to go into the code every time a new condition is added... this is not only time consuming, but requires a lot of test and retest and when I''m gone, a nightmare for the next programmer! And My time is very limited from day to day...
My solution 2:
Build a table where I have a record linking the status [ID] and the next allowed status [ID]; thus, having a record for each allowed status (i.e. in this example, for [ID]=1 there would be three record entries {1,3};{1,4};{1,11}. Then I can build a join query between the two tables, yea. Now this may be the more "Normalized" approach; however, it seems about as clumsy as solution 1. Creating 3 data-records for one record, or maybe even 5 or 6 records per one record. I can automate the table entries; however, this just doesn''t seem to be an elegant solution.
My solution 3:
As given in the question. 1 record for each of the allowed status with a field that has the next logically allowed states.
In this case, I am not concerned about being able to build a query based on the [str_nextallowedstatus] field. It is simply a field used to limit the choices provided in a combo-box from the status table for the next asset history record.
SO, now back to the orginal quesition.
ZIt''s not normalized because the next allowed status field contains more than one value.
Solution two is the correct approach because it normalizes the data and results in the most optimized and simplest query. Your characterization that it is more complex is incorrect. It is actually simpler, it''s just more records. But it makes everything else easier.
And you said it uses three records to represent one record. But that''s incorrect. What you have currently is actually three records worth of information that is being crammed into one record. And that is what makes the query more complicated.
