Self reducing lists ?

[Derek Joinson]

Hi,
is it possible to do self reducing lists in Access ? I have an Excel sheet
that can accomplish this, but it is getting very cumbersome and with lots of
people inputting data, it is not infallible.

E.G. I need to track serial numbered shock absorbers fitted to buses. They
can be fitted to 4 positions on each bus. Once I have fitted shock absorber
"Ser No 1234" to the front left hub on a bus, on the database how do you
prevent it being fitted to the other 3 positions on the same bus or to any
positions on other buses, as it is obviously physically impossible to have
it in 2 places at once.

Is it also possible to do this without VBA or macro routines, as I am not as
capable with Access as I am with Excel. Any help would be greatly
appreciated.
Cheers for now,
Dekka.

 

[Douglas J. Steele]

You should be able to put a "In Use" flag on each item, so that it's not
available again.

More sophisticated would be to link each item with the actual location where
it was used. How you do this would depend on the level of tracking you need
to do. If a given part can be used in more than one location over time
(i.e.: if Ser No 1234 is initially used in the front left hub of bus ABC,
but later moved to the rear right hub of bus XYZ), then you've got a
many-to-many relationship between the Item table and the Location table. If
you don't need to track over time, then a "Where Used" field in the Item
table should be sufficient (and would eliminate the need for the In Use flag
I talked about later, since a Null value in the Where Used field would
indicate it's not in use, while a value would indicate it is.)

 

[Derek Joinson]

Thank you very much for your help.
Cheers,
Dekka.