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 >automatically because the sheet has dynamic
formulas<, 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. >The database is primarily to get a snapshot of
where items are placed for maintenance purposes. It does not track amount
of time fitted, as they have a daily inspection for serviceability and if
they are removed for maintenance, they don't necessarily get fitted back on
the same bus or position.<

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.

 

[Rick B]

How many times are you going to ask this?

After using the part, remove it from your inventory table, or enter a "date
installed" or check off an "installed" checkbox.

Then, in your list, filter appropriately.

This is a very basic design question. What are you currently doing to your
parts table after you install a part? Are you updating it in some way?


Rick B


Hi,
is it possible to do self reducing lists in Access ? I have an Excel sheet
that can accomplish this >automatically because the sheet has dynamic
formulas<, 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. >The database is primarily to get a snapshot of
where items are placed for maintenance purposes. It does not track amount
of time fitted, as they have a daily inspection for serviceability and if
they are removed for maintenance, they don't necessarily get fitted back on
the same bus or position.<

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.

 

[Derek Joinson]

Hi,
sorry but I'm very new to Access and I'm trying to design a database to
replace an Excel sheet which does the job, but which is grinding to a halt
with all the look up lists.

I'm also trying to make it as idiot proof as possible, as more than one
person will be updating it and they won't necessarily be IT literate. They
will be mechanics and engineers who understand the reason for tracking
items, but want to spend their time fruitfully fixing things. On the
spreadsheet it required only one mouse click to select the item at the 4
locations on the bus and because the majority of Excel's functions are
dynamic, a formula saw its selection had been added to the locations ranges
and then it automatically removed it from the available items list. This
available items list uses a static items list, where the fitted item
remains, so when it is removed physically from the bus, it will reappear
again on the available list when the sheet is updated. This alleviated the
problem on the earlier version of the spreadsheet, where the mechanics had
to fit the item in the correct place and also delete it from either another
buses location or the list for when the item was held in storage. This due
to laziness or lack of knowledge invariably meant the same item being in 3
or more locations on the spreadsheet.
Now if a one click selection could automatically achieve all that in a
Access database, so that mere mortals wouldn't have to remember to tick this
or put an installed date somewhere else, without having to resort to VBA
that would be brilliant. But I'm beginning to suspect I'm expecting the
solution to be a lot simpler than it is actually going to be. Again I'm
sorry I didn't fully explain the reason behind the request and I would be
grateful for any help in resolving this.
Thanks,
Dekka.

 

[Tim Ferguson]

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

It seems to me that you have (at least) two separate tables

Parts(*PartNumber, Description, Supplier, DateBought,
CurrentlyFittedTo(fk references Buses), etc...)

Buses(*RegNo, NumberOfSeats, etc...)


If you need a history -- and I am surprised you say you don't -- you would
drop the FK field Parts.CurrentlyFittedTo and have another table:-

Fittings( *PartNumber, *FittedDate, Bus(fk references Buses),
FittedBy(fk references Mechanics),
RemovedDate, etc...)

You could find the current fitting either by MAX(FittedDate) or WHERE
RemovedDate IS NULL -- the first would be safer, I guess -- but you would
still be able to check which mechanic was fitting all the faulty parts and
so on.

I don't understand all the Excel stuff you mentioned. If you need a
multiuser set up, make sure you split the database properly and give each
user their own front end.

Hope that helps


Tim F