ACCESS FEED: Re: [MS_AccessPros] Strange Character Problem

Selasa, 11 Maret 2014

Re: [MS_AccessPros] Strange Character Problem

Jim-

GROUP BY acts like DISTINCT, but both will truncate or turn into garbage any memo fields. One way to do it is to SELECT DISTINCT on all but the memo field, then use another query to fetch the memo using a join with the table that has the memo field.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Mar 10, 2014, at 10:36 PM, Jim Wagner <luvmymelody@yahoo.com> wrote:

John,

Sorry it took so long to reply. Our network drives disconnected and it took most of the morning to get them to come back.

I looked at the query and I took off the totals and compared the count of each with the totals an without. With the totals there is 16 records. Without the totals 107. We surely need the totals. is there another way to get this to work?

Thank You

Jim Wagner

On Saturday, March 8, 2014 12:13 AM, John Viescas <JohnV@msn.com> wrote:

Jim-

When you GROUP BY or DISTINCT a query with a memo field, strange things happen. At a minimum, the memo field gets truncated to 255 characters. Other than to remove duplicates, I can see no reason why this is a Totals query.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

On Mar 7, 2014, at 11:30 PM, Jim Wagner <luvmymelody@yahoo.com> wrote:

I have a query that makes a table from various tables and a query. It has been working for years and all of a sudden it has stopped working. I have a backup from 3 days ago that works perfectly. It is very strange that it worked and then it stopped. Is there something that I can fix quickly?

Thank You
Jim Wagner

The issue is that the query makes a table that has strange characters like 祥), 益). The field is a memo data type. The SQL is below

SELECT [LEAVE UPDATE TABLE].[Person Id], [EMPLOYEES WITH LEAVE ACTION].LASTNAME, [LEAVE UPDATE TABLE].[TYPE OF LEAVE], [EMPLOYEES WITH LEAVE ACTION].FIRSTNAME, [LEAVE UPDATE TABLE].FAMLVDTBEGIN, [LEAVE UPDATE TABLE].FAMLVDTEND, [EMPLOYEES WITH LEAVE ACTION].HR_HOME_DEPARTMENT_CODE, [LEAVE UPDATE TABLE].EXTLVDTBEGIN, [LEAVE UPDATE TABLE].EXTLVTERMDT, tblIncident.IntermittentLeave, [R&D-CURRENTEMPLOYEES].[Jobcode Ld], [R&D-CURRENTEMPLOYEES].[Dept Id], [R&D-CURRENTEMPLOYEES].[Dept Ld], tblIncident.IncidentSummary, [R&D-CURRENTEMPLOYEES].[Person Id] INTO INTERMITTENTFORABSENCEDBASE3
FROM [R&D-CURRENTEMPLOYEES] INNER JOIN ((tblIncident INNER JOIN ([EMPLOYEES WITH LEAVE ACTION] INNER JOIN [LEAVE UPDATE TABLE] ON [EMPLOYEES WITH LEAVE ACTION].[Person Id] = [LEAVE UPDATE TABLE].[Person Id]) ON tblIncident.NoOfIncident = [LEAVE UPDATE TABLE].NoOfIncident) INNER JOIN qryIntermittentLeaveMaxActionOrder1 ON ([EMPLOYEES WITH LEAVE ACTION].[Person Id] = qryIntermittentLeaveMaxActionOrder1.[Person Id]) AND ([LEAVE UPDATE TABLE].ACTIONDT = qryIntermittentLeaveMaxActionOrder1.MaxOfACTIONDT1) AND ([LEAVE UPDATE TABLE].ActionOrder = qryIntermittentLeaveMaxActionOrder1.MaxOfActionOrder)) ON [R&D-CURRENTEMPLOYEES].[Person Id] = [LEAVE UPDATE TABLE].[Person Id]
WHERE (((tblIncident.[Closed/Open])=False))
GROUP BY [LEAVE UPDATE TABLE].[Person Id], [EMPLOYEES WITH LEAVE ACTION].LASTNAME, [LEAVE UPDATE TABLE].[TYPE OF LEAVE], [EMPLOYEES WITH LEAVE ACTION].FIRSTNAME, [LEAVE UPDATE TABLE].FAMLVDTBEGIN, [LEAVE UPDATE TABLE].FAMLVDTEND, [EMPLOYEES WITH LEAVE ACTION].HR_HOME_DEPARTMENT_CODE, [LEAVE UPDATE TABLE].EXTLVDTBEGIN, [LEAVE UPDATE TABLE].EXTLVTERMDT, tblIncident.IntermittentLeave, [R&D-CURRENTEMPLOYEES].[Jobcode Ld], [R&D-CURRENTEMPLOYEES].[Dept Id], [R&D-CURRENTEMPLOYEES].[Dept Ld], tblIncident.IncidentSummary, [R&D-CURRENTEMPLOYEES].[Person Id]
HAVING ((([LEAVE UPDATE TABLE].[TYPE OF LEAVE]) Like "int*") AND ((tblIncident.IntermittentLeave)=True))
ORDER BY [EMPLOYEES WITH LEAVE ACTION].LASTNAME, [EMPLOYEES WITH LEAVE ACTION].FIRSTNAME, [LEAVE UPDATE TABLE].FAMLVDTBEGIN;

Jim Wagner