Mikail Mikail - 1 month ago 6
MySQL Question

Need to convert ms query into mysql query

I'm looking for a converter from ms to mysql, not sceema but queries such as this :

SELECT Last([HISTORY CARD].[PART NUMBER]) AS [LastOfPART NUMBER],
[HISTORY CARD].[SERIAL NUMBER],
Last([HISTORY CARD].POSITION) AS LastOfPOSITION,
Last([HISTORY CARD].[RELEASE DATE TO AIRCRAFT]) AS [LastOfRELEASE DATE TO AIRCRAFT],
Last([HISTORY CARD].[DATE OFF AIRCRAFT]) AS [LastOfDATE OFF AIRCRAFT],
Last([HISTORY CARD].[LAST CAP CHECKED DATE]) AS [LastOfLAST CAP CHECKED DATE],
Last([HISTORY CARD].[DUE CAP CHECK DATE]) AS [LastOfDUE CAP CHECK DATE],
Last([HISTORY CARD].[JOB REMARKS]) AS [LastOfJOB REMARKS],
Last([HISTORY CARD].TSO) AS LastOfTSO,
Last([HISTORY CARD].[BO NUMBER]) AS [LastOfBO NUMBER],
Last([HISTORY CARD].[REPAIR ORDER NUMBER]) AS [LastOfREPAIR ORDER NUMBER],
Last([HISTORY CARD].[LAST OVERHAULED DATE]) AS [LastOfLAST OVERHAULED DATE],
Last([HISTORY CARD].[DUE OVERHAUL DATE]) AS [LastOfDUE OVERHAUL DATE],
Last([HISTORY CARD].[REFRESHER DATE]) AS [LastOfREFRESHER DATE],
Last([HISTORY CARD].[REFRESHER DONE]) AS [LastOfREFRESHER DONE],
Last([HISTORY CARD].[GRN ISSUE DATE]) AS [LastOfGRN ISSUE DATE],
Last([HISTORY CARD].WORKSHEET) AS LastOfWORKSHEET,
Last([HISTORY CARD].[ADDITIONAL ATTACHMENT]) AS [LastOfADDITIONAL ATTACHMENT],
Last([HISTORY CARD].GRN) AS LastOfGRN
FROM [HISTORY CARD]
GROUP BY [HISTORY CARD].[SERIAL NUMBER]
HAVING (((Last([HISTORY CARD].[DUE CAP CHECK DATE])) Between Date() And Date()-60))
ORDER BY Last([HISTORY CARD].[DUE CAP CHECK DATE]) DESC;

Answer

You may have problems reproducing the behavior of the MS Access set function LAST. The idea is that it will return the last entered (real time) value but in practice may end up seeming to be arbitrary e.g. compacting the database file may physically reorder the data on disk.

See ACC2000: First and Last Functions Return Unexpected Records.

The best approach might be to find a different way e.g. converting the table to a temporal table (history or transaction table) by adding temporal columns.

(A little hopeful Maybe you could get away with simply replacing LAST with MAX ?!)