Then I'd suggest that you somehow indicate whether a record is the old one or the new one in a separate column and use that new column in an ORDER BY to force the old/new or new/old order that you want to see.


>Hi Fred,
>I've included some code that I was trying to use. This is only for the user details table which is quite simple anyway but I'm trying to get a solution to cover all situations. uAllUser is the live table and auAllUser is a temporary table created in the load event of the form. The problem with the sort order is highlighted in the code.
>
>lParameter tcType
>Local strDetail, cSite, nCount
>strDetail = ""
>cSite = ""
>nCount = 0
>Do Case
> Case AllT(tcType) = "A" && User Details
> cSite = "ALL SITES"
> ** Check for new users
> SELECT A.*;
> FROM uAlluser A ;
> INTO CURSOR curNew;
> WHERE A.UserName Not in(SELECT B.UserName FROM auAlluser B)
> If _Tally > 0
> Scan
> strDetail = strDetail + "NEW USER: " + AllT(curNew.UserName) + Chr(13) + Space(3)
> FOR i = 2 TO FCOUNT()
> If Mod(i,7) = 0
> strDetail = strDetail + Chr(13) + Space(3)
> EndIf
> strDetail = strDetail + TRANSFORM(FIELD(i)) + Space(1) + TRANSFORM(EVALUATE(FIELD(i))) + Space(1)
> ENDFOR
> strDetail = strDetail + Chr(13)
> EndScan
> Use In curNew
> EndIf
> ** Check for deleted users
> SELECT A.* ;
> FROM auAlluser A ;
> INTO CURSOR curDelete;
> WHERE A.UserName Not in(SELECT B.UserName FROM uAlluser B)
>
> If _Tally > 0
> Scan
> strDetail = strDetail + "USER DELETED: " + AllT(curDelete.UserName) + Chr(13) + Space(3)
> FOR i = 2 TO FCOUNT()
> If Mod(i,7) = 0
> strDetail = strDetail + Chr(13) + Space(3)
> EndIf
> strDetail = strDetail + TRANSFORM(FIELD(i)) + Space(1) + TransForm(EVALUATE(FIELD(i)))+ Space(1)
> ENDFOR
> strDetail = strDetail + Chr(13)
> EndScan
>
> Use In curDelete
> EndIf
> ** Changed records
> SELECT A.*;
> FROM uAlluser A ;
> INTO CURSOR curDistinct ;
> UNION ;
> SELECT B.* ;
> B.M45 ;
> FROM auAlluser B ;
> ORDER By 1
>
> SELECT C.*;
> FROM curDistinct C ;
> INTO CURSOR curChanged ;
> WHERE C.UserName in(Select D.UserName From curDistinct D GROUP BY D.UserName HAVING count(UserName)= 2 )
>
> ** At this point we have all records that were changed with a record of both the old
> ** and new values sorted by user name. The problem is the new and old records are not
> ** sorted consistently, sometimes the old record is first other times the new record.
>
> If _Tally > 0
> DO WHILE !EOF()
> nCount = 0
> strDetail = strDetail + "CHANGED PROFILE FOR " + AllT(curChanged.UserName) + Chr(13) + Space(3)
> FOR i = 2 TO FCOUNT()
> temp1 = EVALUATE(FIELD(i))
> SKIP 1
> temp2 = EVALUATE(FIELD(i))
> IF temp1<>temp2
> nCount = nCount + 1
> If Mod(nCount,4) = 0
> strDetail = strDetail + Chr(13) + Space(3)
> EndIf
> If UPPER(AllT(FIELD(i))) == "USERPASS"
> strDetail = strDetail + "PASSWORD CHANGED "
> Else
> strDetail = strDetail + TRANSFORM(FIELD(i)) + " FROM " + TRANSFORM(temp1) + " TO " + TRANSFORM(temp2) + Space(1)
> EndIf
> EndIf
> SKIP -1
> ENDFOR
> strDetail = strDetail + Chr(13)
> SKIP 2
> EndDo
> EndIf
>EndCase
>
>If !Empty(strDetail)
> ** Add record to dAudit
> Select 0
> Use dAudit Shared
> Append Blank
> Replace dAudit.aSite With cSite,;
> dAudit.aDate With Date(),;
> dAudit.aTime With Time(),;
> dAudit.aUser With oApp.cUser,;
> dAudit.aTypCode With tcType,;
> dAudit.aTypDesc With "User Details",;
> dAudit.aDetail With strDetail
>EndIf
>
>Thanks again,
>Niall