Memo field in Query

G

Guest

I've seen alot of recent post about people trying to query a memo field and it being limited, and I have tried all of the fixes people mentioned, but to no avail, have I been able to fix it. Here is a copy of my query, hopefully someone can figure out what I can do, thanks in advance

SELECT [tblEmployee Master DCIC].[First Name], [tblEmployee Master DCIC].[Last Name], [tblEmployee Master DCIC].[Alltel ID], [tblEmployee Master DCIC].[Employee Number], [tblEmployee Master DCIC].[Reports To], IIf([SumOfIn Sign]>0,([SumOfIn Sign]/[Total Time]),[SumOfIn Sign]) AS [% In], IIf([SumOfOut Sign]>0,([SumOfOut Sign]/[Total Time]),[SumOfOut Sign]) AS [% Out], [Multiple Outbound Math].[T+WOut], [Multiple Inbound Math].[T+WIn], tblCommentsandAttendance.[Month #1], tblCommentsandAttendance.[Month #2], [Weekly QR Average].[Overall Score], tblCommentsandAttendance.Comments, [QA Scores].Score AS [QA Score], tblCommentsandAttendance.UNumber, tblCommentsandAttendance.UserName, tblCommentsandAttendance.Date, [QA Scores].Date, (Sum([SumOfIn Connects])/Sum([T+WIn]))/24 AS InCallHr, (Sum([SumOfOut Connects])/Sum([T+WOut]))/24 AS OutCallHr, [tblEmployee Master DCIC].[Team Lead], [Multiple Inbound Math].[SumOfIn Sign], [Multiple Outbound Math].[SumOfOut Sign], [SumOfIn Sign]+[SumOfOut Sign] AS [Total Time
FROM [Multiple Inbound Math] INNER JOIN ([Multiple Outbound Math] INNER JOIN ([QA Scores] INNER JOIN ([Weekly QR Average] INNER JOIN (tblCommentsandAttendance INNER JOIN [tblEmployee Master DCIC] ON tblCommentsandAttendance.UNumber = [tblEmployee Master DCIC].[Employee Number]) ON [Weekly QR Average].[Alltel ID] = [tblEmployee Master DCIC].[Alltel ID]) ON [QA Scores].[Alltel ID] = [tblEmployee Master DCIC].[Alltel ID]) ON [Multiple Outbound Math].[Agent ID] = tblCommentsandAttendance.UNumber) ON [Multiple Inbound Math].[Agent ID] = tblCommentsandAttendance.UNumbe
GROUP BY [tblEmployee Master DCIC].[First Name], [tblEmployee Master DCIC].[Last Name], [tblEmployee Master DCIC].[Alltel ID], [tblEmployee Master DCIC].[Employee Number], [tblEmployee Master DCIC].[Reports To], [Multiple Outbound Math].[T+WOut], [Multiple Inbound Math].[T+WIn], tblCommentsandAttendance.[Month #1], tblCommentsandAttendance.[Month #2], [Weekly QR Average].[Overall Score], tblCommentsandAttendance.Comments, [QA Scores].Score, tblCommentsandAttendance.UNumber, tblCommentsandAttendance.UserName, tblCommentsandAttendance.Date, [QA Scores].Date, [tblEmployee Master DCIC].[Team Lead], [Multiple Inbound Math].[SumOfIn Sign], [Multiple Outbound Math].[SumOfOut Sign
HAVING ((([tblEmployee Master DCIC].[Employee Number])=[Forms]![frmCommentsforIndividuals]![UNumber]) AND ((tblCommentsandAttendance.Date) Between [Forms]![frmMain]![Date1] And [Forms]![frmMain]![Date2]) AND (([QA Scores].Date) Between [Forms]![frmMain]![Date1] And [Forms]![frmMain]![Date2]) AND (([tblEmployee Master DCIC].[Team Lead])=[Forms]![frmLeadRepSpec]![AlltelID]))
 
A

Allen Browne

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.
Eric Wade said:
I've seen alot of recent post about people trying to query a memo field
Click to expand...
and it being limited, and I have tried all of the fixes people mentioned,
but to no avail, have I been able to fix it. Here is a copy of my query,
hopefully someone can figure out what I can do, thanks in advance:
SELECT [tblEmployee Master DCIC].[First Name], [tblEmployee Master
Click to expand...
DCIC].[Last Name], [tblEmployee Master DCIC].[Alltel ID], [tblEmployee
Master DCIC].[Employee Number], [tblEmployee Master DCIC].[Reports To],
IIf([SumOfIn Sign]>0,([SumOfIn Sign]/[Total Time]),[SumOfIn Sign]) AS [%
In], IIf([SumOfOut Sign]>0,([SumOfOut Sign]/[Total Time]),[SumOfOut Sign])
AS [% Out], [Multiple Outbound Math].[T+WOut], [Multiple Inbound
Math].[T+WIn], tblCommentsandAttendance.[Month #1],
tblCommentsandAttendance.[Month #2], [Weekly QR Average].[Overall Score],
tblCommentsandAttendance.Comments, [QA Scores].Score AS [QA Score],
tblCommentsandAttendance.UNumber, tblCommentsandAttendance.UserName,
tblCommentsandAttendance.Date, [QA Scores].Date, (Sum([SumOfIn
Connects])/Sum([T+WIn]))/24 AS InCallHr, (Sum([SumOfOut
Connects])/Sum([T+WOut]))/24 AS OutCallHr, [tblEmployee Master DCIC].[Team
Lead], [Multiple Inbound Math].[SumOfIn Sign], [Multiple Outbound
Math].[SumOfOut Sign], [SumOfIn Sign]+[SumOfOut Sign] AS [Total Time]
FROM [Multiple Inbound Math] INNER JOIN ([Multiple Outbound Math] INNER
Click to expand...
JOIN ([QA Scores] INNER JOIN ([Weekly QR Average] INNER JOIN
(tblCommentsandAttendance INNER JOIN [tblEmployee Master DCIC] ON
tblCommentsandAttendance.UNumber = [tblEmployee Master DCIC].[Employee
Number]) ON [Weekly QR Average].[Alltel ID] = [tblEmployee Master
DCIC].[Alltel ID]) ON [QA Scores].[Alltel ID] = [tblEmployee Master
DCIC].[Alltel ID]) ON [Multiple Outbound Math].[Agent ID] =
tblCommentsandAttendance.UNumber) ON [Multiple Inbound Math].[Agent ID] =
tblCommentsandAttendance.UNumber
GROUP BY [tblEmployee Master DCIC].[First Name], [tblEmployee Master
Click to expand...
DCIC].[Last Name], [tblEmployee Master DCIC].[Alltel ID], [tblEmployee
Master DCIC].[Employee Number], [tblEmployee Master DCIC].[Reports To],
[Multiple Outbound Math].[T+WOut], [Multiple Inbound Math].[T+WIn],
tblCommentsandAttendance.[Month #1], tblCommentsandAttendance.[Month #2],
[Weekly QR Average].[Overall Score], tblCommentsandAttendance.Comments, [QA
Scores].Score, tblCommentsandAttendance.UNumber,
tblCommentsandAttendance.UserName, tblCommentsandAttendance.Date, [QA
Scores].Date, [tblEmployee Master DCIC].[Team Lead], [Multiple Inbound
Math].[SumOfIn Sign], [Multiple Outbound Math].[SumOfOut Sign]
HAVING ((([tblEmployee Master DCIC].[Employee
Click to expand...
Number])=[Forms]![frmCommentsforIndividuals]![UNumber]) AND
((tblCommentsandAttendance.Date) Between [Forms]![frmMain]![Date1] And
[Forms]![frmMain]![Date2]) AND (([QA Scores].Date) Between
[Forms]![frmMain]![Date1] And [Forms]![frmMain]![Date2]) AND (([tblEmployee
Master DCIC].[Team Lead])=[Forms]![frmLeadRepSpec]![AlltelID]));
 
A

Allen Browne

Assuming the memo field is:
tblCommentsandAttendance.Comments
try choosing:
First
in the Total row under this field in query design, instead of:
Group By

This means Access does not have to actually group the data on the memo
field - something that restricts it to the first 255 characters. Instead, it
is free to just choose the first match for the memo field, and return the
whole thing.

On a different topic, it's a really good idea to declare your parameters so
Access/JET will not misunderstand the data type. Choose Parameters from the
Query menu, and enter them one per line, e.g.:
[Forms]![frmCommentsforIndividuals]![UNumber] Long
[Forms]![frmMain]![Date1]
Date/Time

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Eric Wade said:
I've seen alot of recent post about people trying to query a memo field
Click to expand...
and it being limited, and I have tried all of the fixes people mentioned,
but to no avail, have I been able to fix it. Here is a copy of my query,
hopefully someone can figure out what I can do, thanks in advance:
SELECT [tblEmployee Master DCIC].[First Name], [tblEmployee Master
Click to expand...
DCIC].[Last Name], [tblEmployee Master DCIC].[Alltel ID], [tblEmployee
Master DCIC].[Employee Number], [tblEmployee Master DCIC].[Reports To],
IIf([SumOfIn Sign]>0,([SumOfIn Sign]/[Total Time]),[SumOfIn Sign]) AS [%
In], IIf([SumOfOut Sign]>0,([SumOfOut Sign]/[Total Time]),[SumOfOut Sign])
AS [% Out], [Multiple Outbound Math].[T+WOut], [Multiple Inbound
Math].[T+WIn], tblCommentsandAttendance.[Month #1],
tblCommentsandAttendance.[Month #2], [Weekly QR Average].[Overall Score],
tblCommentsandAttendance.Comments, [QA Scores].Score AS [QA Score],
tblCommentsandAttendance.UNumber, tblCommentsandAttendance.UserName,
tblCommentsandAttendance.Date, [QA Scores].Date, (Sum([SumOfIn
Connects])/Sum([T+WIn]))/24 AS InCallHr, (Sum([SumOfOut
Connects])/Sum([T+WOut]))/24 AS OutCallHr, [tblEmployee Master DCIC].[Team
Lead], [Multiple Inbound Math].[SumOfIn Sign], [Multiple Outbound
Math].[SumOfOut Sign], [SumOfIn Sign]+[SumOfOut Sign] AS [Total Time]
FROM [Multiple Inbound Math] INNER JOIN ([Multiple Outbound Math] INNER
Click to expand...
JOIN ([QA Scores] INNER JOIN ([Weekly QR Average] INNER JOIN
(tblCommentsandAttendance INNER JOIN [tblEmployee Master DCIC] ON
tblCommentsandAttendance.UNumber = [tblEmployee Master DCIC].[Employee
Number]) ON [Weekly QR Average].[Alltel ID] = [tblEmployee Master
DCIC].[Alltel ID]) ON [QA Scores].[Alltel ID] = [tblEmployee Master
DCIC].[Alltel ID]) ON [Multiple Outbound Math].[Agent ID] =
tblCommentsandAttendance.UNumber) ON [Multiple Inbound Math].[Agent ID] =
tblCommentsandAttendance.UNumber
GROUP BY [tblEmployee Master DCIC].[First Name], [tblEmployee Master
Click to expand...
DCIC].[Last Name], [tblEmployee Master DCIC].[Alltel ID], [tblEmployee
Master DCIC].[Employee Number], [tblEmployee Master DCIC].[Reports To],
[Multiple Outbound Math].[T+WOut], [Multiple Inbound Math].[T+WIn],
tblCommentsandAttendance.[Month #1], tblCommentsandAttendance.[Month #2],
[Weekly QR Average].[Overall Score], tblCommentsandAttendance.Comments, [QA
Scores].Score, tblCommentsandAttendance.UNumber,
tblCommentsandAttendance.UserName, tblCommentsandAttendance.Date, [QA
Scores].Date, [tblEmployee Master DCIC].[Team Lead], [Multiple Inbound
Math].[SumOfIn Sign], [Multiple Outbound Math].[SumOfOut Sign]
HAVING ((([tblEmployee Master DCIC].[Employee
Click to expand...
Number])=[Forms]![frmCommentsforIndividuals]![UNumber]) AND
((tblCommentsandAttendance.Date) Between [Forms]![frmMain]![Date1] And
[Forms]![frmMain]![Date2]) AND (([QA Scores].Date) Between
[Forms]![frmMain]![Date1] And [Forms]![frmMain]![Date2]) AND (([tblEmployee
Master DCIC].[Team Lead])=[Forms]![frmLeadRepSpec]![AlltelID]));
 
G

Guest

Allen

Thank you and I had seen your earlier post or someone else's post about using First. However when the query is run(I use this query on a report), It does not pull the comments, but ask's the user for to input the comments when they've already wrote their comments in a previous form. Checking the table, the comment is saved there already and seems quite ridiculous and redundant for them to re-type it out again. Any inside clue to that? :) Any help is appreciated
----- Allen Browne wrote: ----

Assuming the memo field is
tblCommentsandAttendance.Comment
try choosing
Firs
in the Total row under this field in query design, instead of
Group B

This means Access does not have to actually group the data on the mem
field - something that restricts it to the first 255 characters. Instead, i
is free to just choose the first match for the memo field, and return th
whole thing

On a different topic, it's a really good idea to declare your parameters s
Access/JET will not misunderstand the data type. Choose Parameters from th
Query menu, and enter them one per line, e.g.
[Forms]![frmCommentsforIndividuals]![UNumber] Lon
[Forms]![frmMain]![Date1
Date/Tim

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.htm
Reply to group, rather than allenbrowne at mvps dot org

Eric Wade said:
I've seen alot of recent post about people trying to query a memo fiel
Click to expand...
and it being limited, and I have tried all of the fixes people mentioned
but to no avail, have I been able to fix it. Here is a copy of my query
hopefully someone can figure out what I can do, thanks in advance
SELECT [tblEmployee Master DCIC].[First Name], [tblEmployee Maste
Click to expand...
Click to expand...
DCIC].[Last Name], [tblEmployee Master DCIC].[Alltel ID], [tblEmploye
Master DCIC].[Employee Number], [tblEmployee Master DCIC].[Reports To]
IIf([SumOfIn Sign]>0,([SumOfIn Sign]/[Total Time]),[SumOfIn Sign]) AS [
In], IIf([SumOfOut Sign]>0,([SumOfOut Sign]/[Total Time]),[SumOfOut Sign]
AS [% Out], [Multiple Outbound Math].[T+WOut], [Multiple Inboun
Math].[T+WIn], tblCommentsandAttendance.[Month #1]
tblCommentsandAttendance.[Month #2], [Weekly QR Average].[Overall Score]
tblCommentsandAttendance.Comments, [QA Scores].Score AS [QA Score]
tblCommentsandAttendance.UNumber, tblCommentsandAttendance.UserName
tblCommentsandAttendance.Date, [QA Scores].Date, (Sum([SumOfI
Connects])/Sum([T+WIn]))/24 AS InCallHr, (Sum([SumOfOu
Connects])/Sum([T+WOut]))/24 AS OutCallHr, [tblEmployee Master DCIC].[Tea
Lead], [Multiple Inbound Math].[SumOfIn Sign], [Multiple Outboun
Math].[SumOfOut Sign], [SumOfIn Sign]+[SumOfOut Sign] AS [Total Time
FROM [Multiple Inbound Math] INNER JOIN ([Multiple Outbound Math] INNE
Click to expand...
JOIN ([QA Scores] INNER JOIN ([Weekly QR Average] INNER JOI
(tblCommentsandAttendance INNER JOIN [tblEmployee Master DCIC] O
tblCommentsandAttendance.UNumber = [tblEmployee Master DCIC].[Employe
Number]) ON [Weekly QR Average].[Alltel ID] = [tblEmployee Maste
DCIC].[Alltel ID]) ON [QA Scores].[Alltel ID] = [tblEmployee Maste
DCIC].[Alltel ID]) ON [Multiple Outbound Math].[Agent ID]
tblCommentsandAttendance.UNumber) ON [Multiple Inbound Math].[Agent ID]
tblCommentsandAttendance.UNumbe
GROUP BY [tblEmployee Master DCIC].[First Name], [tblEmployee Maste
Click to expand...
DCIC].[Last Name], [tblEmployee Master DCIC].[Alltel ID], [tblEmploye
Master DCIC].[Employee Number], [tblEmployee Master DCIC].[Reports To]
[Multiple Outbound Math].[T+WOut], [Multiple Inbound Math].[T+WIn]
tblCommentsandAttendance.[Month #1], tblCommentsandAttendance.[Month #2]
[Weekly QR Average].[Overall Score], tblCommentsandAttendance.Comments, [Q
Scores].Score, tblCommentsandAttendance.UNumber,
tblCommentsandAttendance.UserName, tblCommentsandAttendance.Date, [QA
Scores].Date, [tblEmployee Master DCIC].[Team Lead], [Multiple Inbound
Math].[SumOfIn Sign], [Multiple Outbound Math].[SumOfOut Sign]
HAVING ((([tblEmployee Master DCIC].[Employee
Click to expand...
Number])=[Forms]![frmCommentsforIndividuals]![UNumber]) AND
((tblCommentsandAttendance.Date) Between [Forms]![frmMain]![Date1] And
[Forms]![frmMain]![Date2]) AND (([QA Scores].Date) Between
[Forms]![frmMain]![Date1] And [Forms]![frmMain]![Date2]) AND (([tblEmployee
Master DCIC].[Team Lead])=[Forms]![frmLeadRepSpec]![AlltelID]));
 
A

Allen Browne

When you choose First in the query, you get a different field name output.

Open your report in design view.
Change the ControlSource of your Comments field to:
FirstOfComments
or whatever name appears in the Field List (View menu).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Eric Wade said:
Allen,

Thank you and I had seen your earlier post or someone else's post about
Click to expand...
using First. However when the query is run(I use this query on a report),
It does not pull the comments, but ask's the user for to input the comments
when they've already wrote their comments in a previous form. Checking the
table, the comment is saved there already and seems quite ridiculous and
redundant for them to re-type it out again. Any inside clue to that? :)
Any help is appreciated!
----- Allen Browne wrote: -----

Assuming the memo field is:
tblCommentsandAttendance.Comments
try choosing:
First
in the Total row under this field in query design, instead of:
Group By

This means Access does not have to actually group the data on the memo
field - something that restricts it to the first 255 characters. Instead, it
is free to just choose the first match for the memo field, and return the
whole thing.

On a different topic, it's a really good idea to declare your parameters so
Access/JET will not misunderstand the data type. Choose Parameters from the
Query menu, and enter them one per line, e.g.:
[Forms]![frmCommentsforIndividuals]![UNumber] Long
[Forms]![frmMain]![Date1]
Date/Time
Click to expand...
 
Top