Inventory Query

[UT227]

I'm trying to make an inventory database. In this case, weapons are issued out. I need it to show only weapons that are available. Once returned, I need it to show the weapon to be available again. I made a query. But when the weapon is issued again, it still shows up. I'm sure that I over simplified it. I can really use some help.

[ranman256]

The weapon record should have a flag, CheckedTo (as long integer)
when checked out, it should have the persons ID #

once returned, the field becomes NULL, to show its checked in.

[UT227]

I was trying to use the issued date and returned date instead of a checked out box. I'm trying to preserve history so we can go back and know who it was issued to.

[pbaldy]

It would help to know the table structure. With a weapons table and a transactions table, the items in the transaction table where the returned Date is null are out. An unmatched query of those vs the weapons table tells you which are available.

[orange]

UT227,

If you step back a little from your specific problem, I think you will find it very much like a Library System.

You have a number of Books and a number of Borrowers. You keep info on the Books_Out_on_Loan (Loans and Returns).

Books ===weapons
Borrowers
Books_out_On_Loan=== WeaponsIssued/WeaponTransactions

as Paul said, Weapons and Transaction tables --you'll probably need a Persons/Borrowers table also.

[UT227]

Here's the query. I'm using a tblEquipment and a tblEquipIssued. The rest are drop down lists.

[UT227]

Yes. I think you're absolutely correct. It is much like a library. Unfortunately, I don't know how those databases are put together either. I'm kinda flying blind. Trying to problem solve. I'm just coming up blank.

[pbaldy]

Could a record have a null issue date? I wouldn't think so. My suggestion stands though: 1 query to find those that are out, a second to find those that aren't.

[aytee111]

For the purposes of development, I would first make a query that contains only tblEquipmentIssued. There are going to be multiple records on this table for one piece of equipment and you want it to show only one. Get this query right before proceeding to join all the other tables.

When would IssueDate be Null? Why would a record exist in this situation?

If equipment is available then there will be no records with ReturnDate = Null. All you need to show is the equipment, you don't need to show the dates.

[UT227]

If a piece of equipment is new and never been issued, the IssueDate will be Null. Correct, I don't want to see records with ReturnDate = Null because it should be available. But, because a returned equipment will have a date in the ReturnDate, I put Is Not Null to show returned equipment.

[UT227]

pbaldy,
That sounds like a good solution. I'm going to try it. Yes, an IssueDate can be Null (new equipment, never been issued). Will be trying that.

[pbaldy]

If I were doing it (and I have done similar things) a new item wouldn't have a record in the transactions table. It's for...transactions.

[UT227]

That would be my tblEquipmentIssued. How do you keep history, without having a new record in it?

[pbaldy]

There is no transaction until it first goes out in my opinion. You're keeping a record of it being issued before it actually is.

[UT227]

Ah. I understand. No, actually that table is only for in and out. But, I will be trying your suggestion. Will keep you up to date. Thanks.