I'm looking at your database.
What differentiates and Item from an InventoryItem? Some attribute/characteristic should clearly tell you that it is one or the other--can't be both. It isn't clear to me, but you know your set up better than anyone.
Using paper and pencil, make some test data and some test scenarios, then work them against the model. Look for any anomaly and reconcile whatever is causing an issue-- eg bad data, bad table design, wrong relationship...
Then readjust and retest until the mode supports your test (which should represent your business needs).
This sql shows me the status values in Inventoryitem table
Code:
SELECT InventoryItem.InventoryItemID
, InventoryItem.ItemID
, InventoryItem.Status
, InventoryItem.Condition
, InventoryItem.Comment
FROM InventoryItem;
with results
Code:
InventoryItemID |
ItemID |
Status |
Condition |
Comment |
1 |
1 |
Available |
New |
|
2 |
2 |
Checked Out |
Good |
|
3 |
4 |
Available |
Fair |
|
4 |
3 |
Checked Out |
Poor |
|
5 |
1 |
Available |
Good |
|
6 |
4 |
Available |
Poor |
|
7 |
4 |
Checked Out |
Good |
|
8 |
3 |
Available |
Good |
|
9 |
2 |
Available |
Good |
|
10 |
1 |
Available |
Fair |
|
11 |
3 |
Available |
Poor |
|
DO NOT USE TableLookups AT THE TABLE FIELD LEVEL
Use traditional Lookup/Reference tables
see Evil Lookups in Table fields