How do I display two sub-tables?

[jhunton]

I have a database I'm creating for myself - and it's a learning process for other stuff I hope to do later.

This is my list of movies. I have a primary table called Video Collection. I have two sub-tables linked off the ID in the primary table - one is the catagories (i.e. comedy, horror, etc.), and the other is people that I would search by (Steven Spielburg, Will Smith, etc).

As I enter data into these tables, I would like to have both subtables available with one click. Is there any way to do it?

Also, I feel really terrible, but for the life of me, I can't figure out how to change from one subtable to the other... I haven't worked on this for the better part of a year.

Thanks for any help you can give.

jhunton

 

[Len Boorman]

Before you go any further I think you have a problem.

You say "I have a primary table called Video Collection. I have two sub-tables linked off the ID in the primary table ".

I suggest that what you should actually have is two other tables that have their own primary key which are linked to foreign keys in the table Video Collection.

However

If the Category table would consist of only a single field then there is a question as to whether or not this table is required.

The other table "People" maybe is in a similar position. It depends on whether or not you wish to associated more than one person with a particular video.

I suggest that you need to address these first. You may then find you do not have a problem

Len B

 

[neileg]

While I agree with Len, I would add that forms are used for data entry, not tables. While you can enter data directly into the table, you don't get anything like all the benfits of using Access this way.

The facilities that Microsoft have added over the years, like subtables and lookups at table level, are only really useful for quick and dirty applications. If you want ease of use and a nice interface, you need to forget tables except for data storeage and use forms (and reports) for the user to work with.

 

[Pat Hartman]

You can use a tab form with a subform on each tab. Or, you can use a button that swaps the ControlSource for a single subform control from sfmA to sfmB.

 

[jhunton]

OK, let's see if I can reply to each question/comment along the way...

I know almost nothing about Access, and this was the way that my predecessor at a different job used to use it, so Tables is the way I thought to use it.

I currently have quite a few movies entered, but would not be opposed to converting to a different entry and/or storage format if I could convert the current info over.

I often have more than one person, and more than one category for each film, hence, those two are not just "columns" in my table - I do have lots of single value data for each movie, like the year it was made and what format I own it in (DVD or VHS).

What I want to use this for is to deal with the following types of things...
1. Do we own "XYZ Movie"?
2. I'm in the mood for a comedy, what do we have?
3. Hey, let's watch everything we own that John Smith stars in.
4. It's Christmas, I want to pull out every Christmas movie we own.

Any thoughts on how best to re/organize this database? I own over 600 movies, most older VHS, which I am slowly replacing with DVDs, so I need to be able to go in and change things later (I sell off the VHS tapes when I get the movie on DVD... unless there are features on it that aren't on the DVD... wait, you don't need all these details... sorry <g>).

Thanks!

 

[Pat Hartman]

Download the DVD/Video Database Template from the Microsoft Templates site. It should give you a pretty good start. Press the Program Information button on the first form to get to the detail info screen.