Relation Report

[harringg]

I'm looking for assistance on a report that would look like this:

AssetName
Relations: Date
Relations: Type
Relations: Asset/User
Relations: Comment

Where there is a line for each related item a given asset would have

PC01
11/15/2013
Documented by
Office 2013 License 01
Room 123

PC01
11/15/2013
Documented by
Adobe Acrobat XI License 01
Room 123

PC02
11/15/2013
Documented by
Adobe Acrobat XI License 02
Room 456

[harringg]

Works great. Thanks!

That opens up a whole new level of asset management record keeping.

[Hemoco]

try this

select * from (SELECT tblAssets.AssetID, tblAssets.AssetName,
tsysAssetRelationTypes.RelationTypeIcon10 AS icon,tblAssetRelations.StartDate,
tsysAssetRelationTypes.Name AS RelationType, tblAssets_1.AssetName AS [Asset-User],
tblAssetRelations.Comments
FROM tblAssets INNER JOIN
tblAssetRelations ON tblAssets.AssetID = tblAssetRelations.ParentAssetID INNER JOIN
tsysAssetRelationTypes ON
tblAssetRelations.Type = tsysAssetRelationTypes.RelationTypeID INNER JOIN
tblAssets tblAssets_1 ON tblAssetRelations.ChildAssetID = tblAssets_1.AssetID
UNION
SELECT tblAssets.AssetID, tblAssets.AssetName, tsysAssetRelationTypes.RelationTypeIcon10 AS icon, tblAssetUserRelations.StartDate,
tsysAssetRelationTypes.Name AS RelationType, tblADusers.Name AS [Asset-User], tblAssetUserRelations.Comments
FROM tblAssets INNER JOIN
tblAssetUserRelations ON tblAssets.AssetID = tblAssetUserRelations.AssetID INNER JOIN
tblADusers ON tblAssetUserRelations.Username = tblADusers.Username AND
tblAssetUserRelations.Userdomain = tblADusers.Userdomain INNER JOIN
tsysAssetRelationTypes ON tblAssetUserRelations.Type = tsysAssetRelationTypes.RelationTypeID) as tbl
order by AssetName, relationtype