获取值而不是 ID
我有一个表 tblInvestigators，其中包含一个查找字段来显示姓名列表
一笔资助可能有超过 1 名调查员。
我的项目的一个要求是在拨款详细信息旁边的一个单元格中列出所有研究人员，所以我会：
拨款 A |姓名 A、姓名 B、姓名 C 以及
我有一个 VBA 模块，它将调查员连接到 1 个单元格中，如下所示：
'Concat Returns lists of items which are within a grouped field
Public Function c(strID As String, strAddMe As String) As String
Static prevID As String
Static strFinal As String
Static strLastAdded As String
If (strID = prevID And strAddMe <> strLastAdded) Then
strFinal = strFinal & ", " & strAddMe
Else
prevID = strID
strLastAdded = strAddMe
strFinal = strAddMe
End If
c = strFinal
End Function
一个调用它的访问查询 (SQL)：
SELECT g.grant_id, Max(c(g.grant_id,tblInvestigators.investigator)) AS Expr1
FROM tblGrants AS g LEFT JOIN tblInvestigators ON g.grant_id = tblInvestigators.grant_id
WHERE (((g.grant_id)=6))
GROUP BY g.grant_id;
当我运行此模块时，它返回一个以逗号分隔的列表，但它是来自以下位置的 ID 号的列表：查找列（tblInvestigators.investigator）而不是名称。我怎样才能得到名字？
克里斯
I have a table tblInvestigators which contains a lookup field to display a list of names
A grant may have more than 1 investigator.
A requirement of my project is to list all investigators in a single cell next to the grant details, so i'd have:
Grant A | Name A, Name B, Name C
etc.
I have a VBA module that concatenates the investigators into 1 cell as follows:
'Concat Returns lists of items which are within a grouped field
Public Function c(strID As String, strAddMe As String) As String
Static prevID As String
Static strFinal As String
Static strLastAdded As String
If (strID = prevID And strAddMe <> strLastAdded) Then
strFinal = strFinal & ", " & strAddMe
Else
prevID = strID
strLastAdded = strAddMe
strFinal = strAddMe
End If
c = strFinal
End Function
And an access query that calls it (SQL):
SELECT g.grant_id, Max(c(g.grant_id,tblInvestigators.investigator)) AS Expr1
FROM tblGrants AS g LEFT JOIN tblInvestigators ON g.grant_id = tblInvestigators.grant_id
WHERE (((g.grant_id)=6))
GROUP BY g.grant_id;
When I run this, it returns a comma separated list, but it is a list of the ID numbers from the look up column (tblInvestigators.investigator)rather than the names. How can I get the names?
Chris
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
使用查找字段从来都不是一个好主意： http://www.mvps.org /access/lookupfields.htm
它伪装了获取所需结果的标准方法，即使用查询将 ID 连接到查找表并返回描述。
看看这个MS access(2003) 是否有与存储过程类似的功能。我想在 MS Access 中运行复杂查询
It is never a good idea to use look-up fields: http://www.mvps.org/access/lookupfields.htm
It is disguising the standard way of getting the results you want, which is to use a query to join the ID to the look-up table and return the description.
Have a look at this Does MS access(2003) have anything comparable to Stored procedure. I want to run a complex query in MS acceess