由于“不同"而导致访问查询备注字段被截断. [英] Access Query Memo field truncation due to "distinct"

问题描述

我在运行查询时遇到了问题，没有在NotesTbl中截断note字段或返回重复的条目.

I am having problems running a query without either truncating the note field in NotesTbl or returning repeated entries.

UID对于AccessTbl不是唯一的.当我遗漏可区分的"注释时，由于我在非可区分的情况下与AccessTbl一起加入，因此笔记将返回多次.当我使用distict时，note字段会被截断，因为它是一个备忘字段.

UID is not unique for AccessTbl. When I leave out "distinct" notes will return multiple times because I am joining with AccessTbl on a non-distinct condition. When I use distict, the note field is trunctated because it is a memo field.

这是我的查询:

SELECT DISTINCT NotesTbl.pin, NotesTbl.noteid, NotesTbl.note, NotesTbl.date,
AccessTbl.affiliation, AccessTbl.name
FROM NotesTbl
LEFT JOIN AccessTbl
ON NotesTbl.UID = AccessTbl.UID
WHERE PIN = #pin#
AND UID = '#uid#'
ORDER BY NotesTbl.DATE DESC

推荐答案

我找到了一个可行的解决方案.我使用分组依据"在PIN和NoteID上强制区分.我试图通过使用First()来避免截断，从而将注释从不同性比较中排除.

I found a solution that seems to work. I used a "group by" to force distinctness on the PIN and NoteID. I tried to exclude the note from distinctness comparissons by using First() to avoid truncation.

SELECT NotesTbl.pin, NotesTbl.noteid, First(NotesTbl.note) as notebody, NotesTbl.date,
AccessTbl.affiliation, AccessTbl.name
FROM NotesTbl
LEFT JOIN AccessTbl
ON NotesTbl.UID = AccessTbl.UID
WHERE PIN = #pin#
AND UID = '#uid#'
GROUP BY pin,affiliation,name,date,noteid
ORDER BY NotesTbl.DATE DESC

这篇关于由于“不同"而导致访问查询备注字段被截断.的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！