The way your database is set up is a little backward for what you're doing I think, at least from what I understand of your problem.
I would approach this from the equipment side, NOT the employee side.
In other words, when you scan in your returning piece of equipment, it will bring up the most recent record of that tool being checked out, and you can enter the check in date on the same record as the last check out. You do not (I don't think, based on your structure) want to have a check out record and a check in record. Using this method you also can make some assumptions of logic.
For instance if your most recent record has both an in and out date you create a new record with a check out date
if the check out date is present on the most recent record but no check in date, you can assume it's being checked back in.
You would also be able to see if the person returning the tool is the same one that took it out, I don't know if that's important or not but if the person who took the tool out is responsible for it if it becomes lost, broken or stolen.
You'd only need 1 form to do this, all you'd have to do is scan the tool, have the form navigate to that item. Have the history of that item displayed and do whatever you need to do.
On the reporting side there's only 2 records in the Equipment history table so if your data entry is working the way you want I don't really have enough data to go forward
When you check an item back in, is it your intent (and does the database work this way) that the check in date be on the same record of the last check out? or do you have a out/in pair of records? can you perhaps. It's impossible to say from the two records you have in the history table what your end result is.
If your data DOES check out/in on the same record you can do something like this:
(in your example table I put in a check out date of 1/1/2013 for both records, I also put in equipment numbers 2 and 3 in the equipment ID field)
First I made this query:
Code:
SELECT EquipmentHistory.EquipmentID, Max(EquipmentHistory.CheckOutDate) AS MRCheckOut
FROM EquipmentHistory
GROUP BY EquipmentHistory.EquipmentID;
I saved this as qry_MRCheckout
then you can run this query:
Code:
SELECT qry_MRCheckout.EquipmentID, Equipment.EquipmentMake, Equipment.EquipmentDescription, Equipment.InService, Equipment.BeingRepaired, Equipment.SerialNumber, Equipment.EquipmentModel, EquipmentHistory.CheckOutDate, EquipmentHistory.CheckInDate, IIf(IsNull([checkoutdate]),"No Loan Record",IIf(IsNull([checkindate]),"Out on Loan","In Stock")) AS CurrentLocation
FROM (qry_MRCheckout LEFT JOIN EquipmentHistory ON (qry_MRCheckout.EquipmentID = EquipmentHistory.EquipmentID) AND (qry_MRCheckout.MRCheckOut = EquipmentHistory.CheckOutDate)) LEFT JOIN Equipment ON qry_MRCheckout.EquipmentID = Equipment.EquipmentID;
It will show you the location of every tool based on the assumptions that:
1. A tool with no loan record is a 'new' tool and is noted with a tag 'no loan record'
2. A tool where the most recent record has a check out date but no check in date is currently on loan and is noted with a tag of 'Out on loan'
3. A tool where the most recent record has a check out date AND a check in date is currently in stock and is noted with the tag of 'In Stock'
Windows XP 
Access 2010 32bit
Reply With Quote
Thanks for the replies
Ill try n explain. note this is all abreviated and not the way it really reads. It was built correctly with correct formatting and relating/normalized.
