Inventory Query

[UT227]

I'm trying to build an inventory database. I have items that will be checked out and returned. I have the inventory based on and issued date and a returned date. I'm having a problem when an item is returned, it will show up as returned. When it is reissued, it's showing twice in my query. This throws my inventory off. Any suggestions?

[UT227]

I had to break up the query into 2 sections

[aytee111]

First create a query(s) which contains only the primary key of the records that you want shown. Use the Totals feature (icon at the top of the screen) to determine which records, probably using Max. But get this query working first. Then you will use this query to then get the rest of the fields.

[orange]

Do you have a clear statement -in plain English- of your requirement.

Employee borrows Equipment
Equipment falls into/belongs to a Category
Equipment is associated with an EquipmentType
Equipment is associated with a Caliber

I would also suggest a unique composite index on fields
EquipID + EmployeeID + LoanDate in table EquipmentOnLoan to prevent duplicates.

Sample model attached.
Good luck.

[UT227]

Each employee is issued weapons. Upon retirement, being fired, quitting, or for another reason, the weapons must be returned. I have a date the weapon is issued. I also have a date where the weapon is returned. I'm trying to use the return date to show the weapon is no longer issued. I have been able to do this. However, when the same weapon is reissued, the query is showing the returned weapon in the inventory and it's also shown as issued. This messes up my inventory.

[UT227]

Orange,
I apologize, but I didn't understand the flow chart or your possible solution. I didn't understand when you put, "I would also suggest a unique composite index on fields
EquipID + EmployeeID + LoanDate in table EquipmentOnLoan to prevent duplicates."

[UT227]

Aytee111,
I can make the query with only the primary keys you like. I'm not sure how this will help with the inventory.

[aytee111]

This first query shows only one IssueID, and with that you can get all the other data that you are trying to show and there won't be any duplicates.

[InsuranceGuy]

I had to break up the query into 2 sections

Replace tblEquipmentIssued with a query that selects only the currently issued equipment.


Jeff