J
Joe
Please help. In an Access 2003 Asset database (based on Microsoft’s
template) I have the following tables - tblAssets, tblAssetDescription,
tblDepartments,
tblEmployees, and tblAssetAssignment that contains the fields
AssetAssignmentID (autonum,pk), AssetID (num,fk), DepartmentID (num,fk),
EmployeeID (num,fk), AssignmentDate (date/time) and AssignmentDescription
(txt).
My forms are - frmAssetAssignment is a subform of frmAssets and records the
history of the assignment of each asset by date, department and employee.
These forms are working well.
My problem is that I am having difficulty getting frmEmployeesAsset which is
a subform of frmEmployees to accurately show the latest recorded asset(s) for
each employee. The record source for the form contains the following query -
SELECT TblAssetAssignments.AssignID, TblAssetAssignments.EmployeeID,
DMax("AssignmentDate","tblAssetAssignment","[EmployeeID]=" &
Nz([EmployeeID],0)) AS Expr1, tblAssetDescription.AssetDescription,
Assets.SerialNumber
FROM tblAssetDescription LEFT JOIN (Assets LEFT JOIN TblAssetAssignments ON
Assets.AssetID = TblAssetAssignments.AssetID) ON
tblAssetDescription.AssetDescriptionID = Assets.AssetDescriptionID;
When the query is run the Expr1 field contains error# and some of the assets
are allocated to more than one person because the latest date is not being
selected by the query. I have tried changing the criteria element of the
statement to AssetID and AssignID and still get the same error message. I
can’t see where I've gone worng and would greatly appreciate any guidance.
Thanks,
Joe
template) I have the following tables - tblAssets, tblAssetDescription,
tblDepartments,
tblEmployees, and tblAssetAssignment that contains the fields
AssetAssignmentID (autonum,pk), AssetID (num,fk), DepartmentID (num,fk),
EmployeeID (num,fk), AssignmentDate (date/time) and AssignmentDescription
(txt).
My forms are - frmAssetAssignment is a subform of frmAssets and records the
history of the assignment of each asset by date, department and employee.
These forms are working well.
My problem is that I am having difficulty getting frmEmployeesAsset which is
a subform of frmEmployees to accurately show the latest recorded asset(s) for
each employee. The record source for the form contains the following query -
SELECT TblAssetAssignments.AssignID, TblAssetAssignments.EmployeeID,
DMax("AssignmentDate","tblAssetAssignment","[EmployeeID]=" &
Nz([EmployeeID],0)) AS Expr1, tblAssetDescription.AssetDescription,
Assets.SerialNumber
FROM tblAssetDescription LEFT JOIN (Assets LEFT JOIN TblAssetAssignments ON
Assets.AssetID = TblAssetAssignments.AssetID) ON
tblAssetDescription.AssetDescriptionID = Assets.AssetDescriptionID;
When the query is run the Expr1 field contains error# and some of the assets
are allocated to more than one person because the latest date is not being
selected by the query. I have tried changing the criteria element of the
statement to AssetID and AssignID and still get the same error message. I
can’t see where I've gone worng and would greatly appreciate any guidance.
Thanks,
Joe