[AccessD] Home inventory problem

Steve,

I may be a bit late on this, but if the fields for his collections are
basically the same, then use a master form that is not tied to any
particular database. Then use SQL to populate a view form. I did this with a
program several years ago where we had two databases of programs that were
being run on a mainframe. It tracked the ownership, description of what the
programs did, when the programs were run and other details. It takes a bit
of work, but it eliminates a ton of redundant programming for each tab (in
this case). The main form is like a switchboard that the user would select
the table they want to see. The view form is then loaded based on sql from a
hidden field on the switchboard. What's nice about doing this, is once you
have the basics down on it, its very easy to create a search form that
allows you to filter the records returned on the view form. Also, then one
or two reports are all that's needed based again on the sql used to display
the view form. Although the initial programming may take a bit longer, you
save a ton of time by not having to reinvent the wheel at every turn.

Hth

John Gould


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Steve Erbach
Sent: Sunday, April 03, 2005 8:26 PM
To: Access Developers discussion and problem solving
Subject: [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
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com