基于索引号和其他条件的MS Access更新查询 [英] MS Access Update Query based on Index Number and other Criteria

问题描述

我有一个功能要为我正在工作的数据库执行.我不是最擅长Access的人，因此，如果我不以最佳方式措辞，我深表歉意.

I have a function I am trying to do for a database I am working on for my job. I'm not the most proficient with Access so I apologize if I am not wording this in the best way.

我想做的是创建一个查询/宏，该查询/宏将模仿显示的行为

What I am trying to do is create a query/macro that will mimic the behavior as shown

并得出以下结果:

逻辑如下

1)对于每个记录-在StdName中获取字符串的LEN.取该数量的字符，并将其更新到名称"字段. LEN之后的其余字符将移至"SuffixString"字段

1) for each record - take the LEN of the string in StdName. Take that number of characters and UPDATE that to the Name field. The remaining characters after the LEN is moved to the 'SuffixString' Field

2)对于每条记录-计算索引号上或索引号之前或之后的任何记录在'StdName'字段中字符串出现的次数，并使用其中已存在的内容更新'Name'字段，并用"_n"连接其中n是发生地点​​

2)for each record - count the number of occurrences of the string in the 'StdName' field for any records ON OR BEFORE the index number and UPDATE the 'Name' field with whatever is in there already and concatenate with "_n" where n is the occurence

示例:索引1-在记录1和记录1之间的StdName字段中出现一次"Car1".索引1名称"更改为Car1_1

example: index 1 - has one occurrence of 'Car1' in the StdName Field between record 1 and record 1. index 1 'Name' is changed to Car1_1

示例:索引2-在记录1和记录2之间的StdName字段中出现两次"Car1".索引2名称"更改为Car1_2

example: index 2 - has two occurrences of 'Car1' in the StdName Field between record 1 and record 2. index 2 'Name' is changed to Car1_2

示例:索引6-在记录1和记录6之间的StdName字段中出现一次"Car3".索引6名称"更改为Car3_1

example: index 6 - has one occurrence of 'Car3" in the StdName Field between record 1 and record 6. index 6 'Name' is changed to Car3_1

可以通过访问查询来完成类似的事情吗?我以前从未在Access中进行过开发，而我的老板真的很想看到此功能保留在Access中，而不是移到Excel之外.

Can something like this be done with an access query? I've never developed in Access before and my boss really wants to see this function kept inside access instead of being moved in an out of excel.

(我以这种方式设置了步骤1，以便稍后将StdName与Name不匹配的逻辑放入其中.例如:"Car1_1"代表Name和StdName"Car2".我意识到我可以将StdName与步骤2中的函数连接起来我描述了这个示例，但是我这样做的真实目的是

(I have step 1 setup this way to later put in logic where StdName does not match Name. example: "Car1_1" for Name and StdName "Car2". I realize I could just Concatenate StdName with the function in step 2 in this example i described, but I have a real world purpose of doing it this way)

这将以MDB格式完成

谢谢

推荐答案

您可以使用我的 RowCounter 函数:

SELECT RowCounter(CStr([Index]),False,[StdName])) AS RowID, *
FROM YourTable
WHERE (RowCounter(CStr([Index]),False) <> RowCounter("",True));

或:

SELECT [StdName] & "_" & CStr(RowCounter(CStr([Index]),False,[StdName]))) AS RankedName, *
FROM YourTable
WHERE (RowCounter(CStr([Index]),False) <> RowCounter("",True));

编辑-进行更新:

UPDATE s_before
SET [Name] = [StdName] & "_" & CStr(RowCounter(CStr([Index]),False,[StdName])) 
WHERE (RowCounter(CStr([Index]),False) <> RowCounter("",True));

代码:

Public Function RowCounter( _
  ByVal strKey As String, _
  ByVal booReset As Boolean, _
  Optional ByVal strGroupKey As String) _
  As Long

' Builds consecutive RowIDs in select, append or create query
' with the possibility of automatic reset.
' Optionally a grouping key can be passed to reset the row count
' for every group key.
'
' Usage (typical select query):
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' Usage (with group key):
'   SELECT RowCounter(CStr([ID]),False,CStr[GroupID])) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter(CStr([ID]),False) <> RowCounter("",True));
'
' The Where statement resets the counter when the query is run
' and is needed for browsing a select query.
'
' Usage (typical append query, manual reset):
' 1. Reset counter manually:
'   Call RowCounter(vbNullString, False)
' 2. Run query:
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable;
'
' Usage (typical append query, automatic reset):
'   INSERT INTO tblTemp ( RowID )
'   SELECT RowCounter(CStr([ID]),False) AS RowID, *
'   FROM tblSomeTable
'   WHERE (RowCounter("",True)=0);
'
' 2002-04-13. Cactus Data ApS. CPH
' 2002-09-09. Str() sometimes fails. Replaced with CStr().
' 2005-10-21. Str(col.Count + 1) reduced to col.Count + 1.
' 2008-02-27. Optional group parameter added.
' 2010-08-04. Corrected that group key missed first row in group.

  Static col      As New Collection
  Static strGroup As String

  On Error GoTo Err_RowCounter

  If booReset = True Then
    Set col = Nothing
  ElseIf strGroup <> strGroupKey Then
    Set col = Nothing
    strGroup = strGroupKey
    col.Add 1, strKey
  Else
    col.Add col.Count + 1, strKey
  End If

  RowCounter = col(strKey)

Exit_RowCounter:
  Exit Function

Err_RowCounter:
  Select Case Err
    Case 457
      ' Key is present.
      Resume Next
    Case Else
      ' Some other error.
      Resume Exit_RowCounter
  End Select

End Function

这篇关于基于索引号和其他条件的MS Access更新查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！