Jim Hewson
JHewson at karta.com
Sun Apr 3 21:56:31 CDT 2005
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 ________________________________ From: accessd-bounces at databaseadvisors.com on behalf of Steve Erbach Sent: Sun 4/3/2005 7: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