|
|
The code below shows everything _almost_ working. Note this is the actual code
rather than the simplified version.
However my problem now is the report layout (but linked to the db problem).
I'm after a table that looks like
Asset ID Description Location Make
Visual date retest
Full_test date retest
but I can only get details of the 2nd or 3rd line Or I get
Asset ID Description Location Make
Visual date retest
Asset ID Description Location Make
Full_test date retest
which isn't quite as useful as I'd like.
Any suggestions before I revert to plan F which is to add the latest actionIDs
to the asset table and grab them from that as needed
Michael
SELECT assetLIST.*, assetLISTIDs.* , bookingsRoom.roomID,
bookingsRoom.roomRoom, assetDescription.*, assetMake.*, assetModel.*
FROM bookingsRoom, assetDescription, assetMake, assetModel, asset assetLIST
JOIN (
SELECT fullActions.*
FROM assetAction fullActions
JOIN (
SELECT MAX(assetActionID) AS assetActionID
FROM assetAction WHERE AssetActionAction = 'E' OR
AssetActionAction = 'V'
GROUP BY assetActionAssetID, AssetActionAction
) AS ActionIDs ON ActionIDs.assetActionID = fullActions.assetActionID
)
AS assetLISTIDs ON assetLISTIDs.assetActionAssetID = assetLIST.assetID
WHERE assetLIST.assetLocation = bookingsRoom.roomID
AND assetLIST.assetDescription = assetDescription.assetDescriptionID
AND assetLIST.assetMake = assetMake.assetMakeID
AND assetLIST.assetModel = assetModel.assetModelID
AND assetLIST.assetPATCode <> 'X'
ORDER BY assetID
|
|