I have a two tables, one is an overview of assets and the other table is how many tests that asset has had. So one asset may have had many tests.
I have created the following expression that does some date adds to work out when I should next test an asset based on when it was last tested and the score it got (class)
the expression above works fine when there is only one or no previous tests however if there is more than one test then it throws up #error and I am assuming that is down to the one to many relationship.
My join query is below...
INNER JOIN street
ON AS_Asset.USRN = street.USRN
INNER JOIN AS_GWSL_INVE_DATA
ON AS_Asset.CentralAssetID = AS_GWSL_INVE_DATA.CentralAssetID
FULL OUTER JOIN AS_GWSL_STRUCTEST_DATA
ON AS_Asset.CentralAssetID = AS_GWSL_STRUCTEST_DATA.CentralAssetID
Can anyone offer advice on how I can get this query to only bring back the very latest date record in the AS_GWSL_STRUCTEST_DATA table, and therefore make the expression above work every time...
thanks in advance