Check in check out help

[Hackcess2007]

Afternoon all
I have a transaction table the last record is [EquipIn]. during the equipment checkOut process all the other headings in the table are filled in. The idea is to fill in the [EquipIn] record a couple of days later when the equip is returned. How the heck can I make a equipment check in form that will update that one record? I can't do this manually as we have 1000+ pieces of equipment.
Any help would be awesome.
Thank you

 

[jdraw]

Can you show us your tables and relationships? A jpg would be useful. You may have to zip your file because of your post count.

 

[Hackcess2007]

Thanks for the reply I will post in the morning form work.
Thanks

 

[Hackcess2007]

Good morning, i have a transaction table that collects data. I will add a equipment table when i get the master list from corporate. That will include EuipID, EquipName, BarcodeNum, not much else. I don't have a employees table because I made a equipment checkOutForm for each employee that loans out equipment. I made the name fild the default value of that employee and set the tab stop to no.. Then I programmed the curser to go into the jobNumber box with tab stop set to no. The after tabbing out of job number into the barcode field it will keep repeating each time i click tab. When I finish scanning all the equipment i click on finish.

 

[Hackcess2007]

This is a view of the data entry screen. The ScanTheEquimen will berenamed to BarCodeNumber. I will attach the transaction table in the next one.

 

[Hackcess2007]

For the CheckIn Form I just copied the form I attached and set the default value in the EquipOut record the IN and that is it. But the problem is that i can't figure out a query to pull only the equpt that is still out. I would date aparameter query by date.

 

[Hackcess2007]

I am stumped with this. I have made a few ok databases for the company that i work for but I can't seem to figure this out. The worst part is that i thought i saw a you tube on a query that would make this work but for the life of me cant find it.

 

[jdraw]

Here is a technique to try:
I mocked up your table but my field names doon't match yours.

Create 2 queries

First: qEquipOut

SELECT tblEquipInOut.TransactionId
, tblEquipInOut.TransactionDate
, tblEquipInOut.CrewChiefName
, tblEquipInOut.EquipOutIn
, tblEquipInOut.BarCode
FROM tblEquipInOut
WHERE (((tblEquipInOut.EquipOutIn)="OUT"))
ORDER BY tblEquipInOut.TransactionDate;

Second: qEquipIn

This identifies Equipment that has at least 1 IN record

SELECT tblEquipInOut.TransactionId
, tblEquipInOut.TransactionDate
, tblEquipInOut.CrewChiefName
, tblEquipInOut.EquipOutIn
, tblEquipInOut.BarCode
FROM tblEquipInOut
WHERE (((tblEquipInOut.EquipOutIn)="IN"))
ORDER BY tblEquipInOut.TransactionDate;

Then the query that can tell you what is OUT that does Not have an IN record.

qEquipStillOut

SELECT qEquipOut.TransactionId
, qEquipOut.TransactionDate
, qEquipOut.CrewChiefName
, qEquipOut.EquipOutIn
, qEquipOut.BarCode
, qEquipIn.BarCode
FROM qEquipOut LEFT JOIN qEquipIn ON 
qEquipOut.BarCode = qEquipIn.BarCode
WHERE (((qEquipIn.BarCode) Is Null));

Try these and see how it goes.
Good luck.

 

[pbaldy]

jdraw, would that handle when a given bar code item has gone in and out multiple times, which I assume can happen? I have the gut feeling it won't, though I'm embarrassed to say I'm not sure.

I'm not sure I like "I made a equipment checkOutForm for each employee that loans out equipment.". Sounds like an improper design; getting a new employee shouldn't require design changes to the db (in this case creating a new form).

On the general design, I'll maybe start a debate. I'm not personally a fan of having in/out transactions, though I can see where a normalization case can be made for it. I've personally created a few apps with this type of functionality, and I counted on the fact that "everything that goes out must come back in", so I've got in and out fields in the same record. I can easily find who/what is out by looking for Null in the in field, and it easily handles the reality of the person/item going in and out multiple times. In my case it's normally employees, drivers and cars, but I think the concept is the same.

 

[jdraw]

Paul,
I agree the design sounds suspect. It reminds me of the "before database" approach by recording material requirements individually by Person. I'm not sure I follow it entirely but that was the OP's current design. My suggestion was really to find unmatched OUT/IN situations.

I agree that having the IN/OUT in the same record could simplify his situation and improve the quality of the info.

His immediate concern was not being able to determine what is still OUT, and I think my suggestion will do that.

If he ends up with multiple Outs and no INs, that shows an error/weakness in his set up somewhere.

 

[pbaldy]

I wasn't speaking about multiple Outs and no Ins, rather a scenario like

Code - Status - Date
123 - Out - 3/1/14
123 - In - 3/2/14
123 - Out - 3/3/14
123 - In - 3/4/14
123 - Out - 3/5/14

Which my assumption is would happen in the normal course of events. Things go out, come back, go out again, come back again, etc. I can't visualize whether the unmatched SQL will catch that, since the only join is on the item code. Maybe I'm having afternoon brain cramps and need a Dr Pepper.

 

[Hackcess2007]

Thanks so much jdraw and pbaldy. It is really cool reading your thoughts. I will give your query suggestion a shot tomorrow at work. I think pbaldy the equip going out and coming in many times during the month is probable for some pieces but maybe a perimeter query by date range would clear up things. thanks again. I will let you know how it goes...

 

[Hackcess2007]

Thank you and Thank you again!!! jdraw and pbaldy. It was all about the join. Marked this as solved. And on a Friday as well. Soon will pour liquid over ice in a clear glass.

 

[jdraw]

Glad it worked for you. Enjoy the cooool one.