[AccessD] Home inventory problem

Dear Group,

A friend who's pretty sharp on computers (PHP development and a fair
amount of VB.NET) asked me to help him figure out a way to handle his
home inventory. He has LPs, Books, video cassettes, etc., half a dozen
different categories in all of things he wants to record in tables. So
far he's made an individual collection table for each type of item:
Books, LPs, Videos, etc.

His question was how to make a master table that had common
information in it -- purchase date, purchase price, location in house,
and category. He was also trying to figure out a way to have a big
master form, perhaps with a tab object, and subforms for all the
tables containing his collections.

I admit that I was a bit stumped. It's easy enough to determine that
the master table should have, say, an AutoNumber key field for each
item in his entire home inventory and that the individual item tables
would link to the master through a foreign key or simply with a Long
Integer field containing the Master key. But there would be a
one-to-one link between the master and the collection tables.

But what he'd like to do is see multiple rows for each of his
collection items at the same time. This implies that the subforms
would NOT be linked to the master. If they WERE linked to the master,
then only ONE item from ONE collection table would show up.

Have you lot ever tried something like this? His notion is that if the
Master table contains the location for each item then he can easily
query the master to find all the items in, say, the upstairs closet.
But then creating a report or a query might be a struggle since he
could conceivably have items from six different collections in that
closet...or only four of the collections.

I think you get the drift. Each collection has enough unique
information that keeping the items in separate tables makes sense, but
pulling all the information together to make an inventory valuation
might be a chore. Any ideas?

Regards,

Steve Erbach
Scientific Marketing
Neenah, WI
www.swerbach.com
Security Page: www.swerbach.com/security