Inventory control

[Brigitte P]

I need a database that keeps track of equipment (wheelchairs, walkers, etc),
who has the equipment, and whether the equipment is available or not. This
seemed simple, but it doesn't work like I anticipated.
I have table 1 for equipment (one unique number per piece equipment), tbl 2
patients, and tbl 3 for checkout which has a related field to equipment, one
to patients, and date checked out, and date checked in. The problem is the
availability of equipment.
When I enter checkout/checkin, I have a simple expression: Available:
IIf([DateAssigned]=0 Or [DateReturned]<>0,"Yes","No") which works fine. But
how do I then get the next record (it's in datasheet view) to only show the
equipment that is available? It seems that I can't requery on the source
query for the data entry form.
Do I need to put the available field in the equipment table and update it
via an update query when the checkout/checkin conditions are met? I tried it
but it didn't work very well, and I'm afraid that the users may not manually
update the available field anyway (which would make it easy). I need to
somehow automate that field.
I'm stumped; is there a solution database out there that I could lean on?
As always, many thanks for your help.
Brigitte P.

 

[Graham Mandeno]

Hi Brigitte

A piece of equipment is unavailable if a record for its EquipmentID exists
in the Checkout table with a Null DateReturned.

So the converse is true - anything else is available.

The following WHERE clause, using a subquery will return the available
equipment:

WHERE EquipmentID Not In (Select EquipmentID from Checkout where
DateReturned is Null)

 

[Brigitte P]

Thanks Graham. My solution was a bit different, but you put me on the right
track; I just couldn't get the logic together, and you jumpstarted my brain.
Since it is one piece of equipment per sign-out, and that could be a patient
or a storage location, I put the available field into the equipment table,
and if the equipment is signed out to a patient, the subform (where the
equipment shows up) changes available to "No," and changes it back to "Yes"
if the patient number becomes Null. I then requery the field from the
signout table to the available only equipment. It works like a gem, but I
needed someone to remind me that only one condition must be met - not
several. I'm getting old and the logic doesn't work so well anymore. Thanks
again.
Brigitte

Hi Brigitte

A piece of equipment is unavailable if a record for its EquipmentID exists
in the Checkout table with a Null DateReturned.

So the converse is true - anything else is available.

The following WHERE clause, using a subquery will return the available
equipment:

WHERE EquipmentID Not In (Select EquipmentID from Checkout where
DateReturned is Null)


--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

I need a database that keeps track of equipment (wheelchairs, walkers,
etc), who has the equipment, and whether the equipment is available or
not. This seemed simple, but it doesn't work like I anticipated.
I have table 1 for equipment (one unique number per piece equipment), tbl
2 patients, and tbl 3 for checkout which has a related field to
equipment, one to patients, and date checked out, and date checked in.
The problem is the availability of equipment.
When I enter checkout/checkin, I have a simple expression: Available:
IIf([DateAssigned]=0 Or [DateReturned]<>0,"Yes","No") which works fine.
But how do I then get the next record (it's in datasheet view) to only
show the equipment that is available? It seems that I can't requery on
the source query for the data entry form.
Do I need to put the available field in the equipment table and update it
via an update query when the checkout/checkin conditions are met? I tried
it but it didn't work very well, and I'm afraid that the users may not
manually update the available field anyway (which would make it easy). I
need to somehow automate that field.
I'm stumped; is there a solution database out there that I could lean on?
As always, many thanks for your help.
Brigitte P.