Append and Delete

[Michelle]

Not sure how to code this. I have a DB that keeps track of Equipment I need
to also keep track of who used the euqipment (who it is assigned to and who
it was assigned to) On my employee table I have a sub from called equipment
where you select the ID that is assigned to them now what I need to do is
once the equipment is no longer assigned to them I would like to copy the
IDTag and Employee 3 over into another table that houses the old equipment
assignments and then delete that piece of equipment from that users current
set of equipment.

tblOldEquipment
TagID
EmpName

tblEquipment
IDTag
EquipType
Locaiton
LocationDesc
Mfg
Model
SerialNum
PONum
Contract
WarrantyType
WarrantyExpDate
Surplus
SurplusDate
InventoryDate
Notes

tblEmployee
ID
FName
MI
LName
JobTitle
Location

I have a query that does the following:

INSERT INTO tblOldEquipAssign ( IDTag, EmpName )
SELECT qurEquipEmpAsg.IDTag, [FName] & " " & [LName] AS FullName
FROM tblEmployees RIGHT JOIN qurEquipEmpAsg ON tblEmployees.ID =
qurEquipEmpAsg.EMPID;

 

[John Spencer]

A better way to manage this would be to have an EquipAssigned table with at
least three fields (perhaps more)
TagID - which piece of equipment
EmployID - which employee signed it out
Returned - Yes/No (or use the next two fields instead)
DateOut
DateReturned
ToolLostDestroyed (Yes/no and use dateReturned to record the date
lost/stolen/destroyed)

Then all you need to do to get the list of tools signed out to an individual
is to search for records where dateReturned is Null or if you don't use the
date fields search for records where returned is fall

Moving records from one table to another is dangerous. If you copy the record
over and then something goes wrong, you end up with the information being in
two places. If you miscode the Delete query, you can end up deleting records
that you did not move or not deleting records that you did move.

Best way to handle the delete might be
DELETE
FROM SOMETable
WHERE Exists
(SELECT *
FROM tblOldEquipment
WHERE TagID = SomeTable.TagID
And EmpID = SomeTable.EmpID)


John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County