[AccessD] Home inventory problem

First of all, I would question the usefulness of placing the objects in 
numerous tables. The first problem with this design is the fact that you 
have to add new tables relatively frequently. I would suggest -- at 
least for purposes of kicking it around -- another structure in which 
two tables -- ObjectTypes and Objects -- are used. Every time you need a 
new object type, you add a row to that table. This new object may 
require new columns that are not yet present in Objects. So you add 
them. Then you create a view/query for each object type that exposes 
only those rows from Objects that are relevant to said object type. For 
example, automobiles have a VIN#, CDs don't, and so on. But on the other 
hand, virtually all the objects have things in common, such as 
Description, purchase date, purchase price and so on, so all queries 
will contain these columns. A good way to do that is to create one query 
that includes all the common columns, and additional queries (one per 
object type) that add the columns unique to that type.

One significant advantage to this design is the ease with which you can 
produce a report that includes all objects of all object types -- just 
report the common columns.

Once you have the queries for each object type, you could generate 
autoForms, set their default to DataSheet, then create a master form 
with a tab control, and plonk each DS form onto its own tab. The master 
form could be based on the ObjectTypes table, making the links to the 
Objects table a no-brainer.

Assuming that you have a list of the properties for each object type, 
you could do this whole app in a morning.

Arthur

Rocky Smolin - Beach Access Software wrote:

> Steve:
>
> Not really responsive but I have to question why he wants to do this 
> at all? Unless it's just a busman's holiday, why not just create a 
> list of his albums, a list of his books, a list of his cassettes in a 
> word doc print it out and be done.  Then he'd know everything he needs 
> by looking at the lists, no?
>
> Rocky
>
>
> ----- Original Message ----- From: "Steve Erbach" <erbachs at gmail.com>
> To: "Access Developers discussion and problem solving" 
> <accessd at databaseadvisors.com>
> Sent: Sunday, April 03, 2005 5:26 PM
> 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
>>
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.9.5 - Release Date: 4/7/2005