基于索引号和其他条件的 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) 对于每条记录 - 计算索引号 ON 或之前的任何记录的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 与名称不匹配的情况下输入逻辑.例如:名称为Car1_1"，标准名称为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 格式上完成

This will be done on an MDB format

谢谢

推荐答案

你可以使用我的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屋！