[AccessD] Home inventory problem

They do have a point though.
Sometimes the simplest or non-technical solution is the best.
I have spent hours - days - no weeks on a database and gave it to the customer.  Only to have them decide it's too much work to enter the data and maintain.  So the database became a throw-away sample.  At least I got paid for it... besides as Rocky suggested - there can be a fun side of doing things.

Jim H.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Steve Erbach
Sent: Monday, April 04, 2005 3:00 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Home inventory problem


Jim,

I think we're on the right track here. You're talking about
many-to-many linking tables.

Thanks for detailing this method. At least YOU took me seriously.
Rocky and Gustav just came up with "take time to smell the roses!"
Sheesh!

Steve Erbach

On Apr 3, 2005 9:56 PM, Jim Hewson <JHewson at karta.com> wrote:
> Steve,
> What you need are junction tables.  Tables that hold PKs from different tables.
> I've done this for several different databases.
> I'm currently working on one for pastoral musicians to keep track of their music.
> Songs, composers (with multiple songs), who arranged the piece (could be composer or someone else), instruments (single or multiple), copyright holders and addresses.  A song could be sheet, book, cd, octavo or combination.  A song could be used for one or more liturgical season (e.g. Tridiuum and Easter).  I have seen two songs with the same title, different composers.  What is the vocal arrangement (choir, duet, quartet, etc.) if any.
> 
> In your sample below, you had a closet with several different collections.  Let's use the family room, hall closet and guest room.  PK for these three rooms are 1, 2, and respectively.
> The second collection is for videos - Video 1, Video 2 and Video 3.
> Third collection is for Music CDs - CD 1, CD 2, and CD3.
> 
> I typically keep junction tables to two columns (one for each table) to allow a multiple combinations between the them.
> 
> By using a junction table the PKs of four tables become FKs.
> 
> RmID   MusicID
> 1             2
> 2             1
> 3             3
> 1             1
> 
> RmID    VidioID
> 1             1
> 2             3
> 3             2
> 2             1
> 
> RmID     CDID
> 1             2
> 2             1
> 3             3
> 1             1
> 
> Using junction tables, you can create a reports.
> For example, you want a report by room of everything that's in the rooms.
> Using the Junction table you can pull the room with ID 1 into one report.
> The common thread in your example are the rooms.
> 
> HTH
> Jim
>
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com