Can anyone help!!!!?????

[Scooper2241]

I am starting a data base from scrach for a library of thousands of
video tapes. The company I work for is broken up into many differnt
departments and groups. The videos need to be sorted into the appropriate
category. The fields that accompany the are the same for all (Date, Program
Title, Footage Type, Agenda and the big one which i assigened ten seperate
feilds Speekers).
Currently I have all of the different categories in seperate tables
which has been brought to my attention is wrong and that all the categories
sould be merged together? If this is the case should i merge them all
together and then create a combo box with a list of all the categories? and
for identification purposes the tapes have to have a number but the number
has to corospond with the category the video fall under or even better a
system that combines the categoty name and a number but the number in each
category need to start at 1. an example of the key I am trying to create
might look like this Coprorate 1 or COR1 and the next would be COR2 and so on
for each category.
Can anyone help?
Can anyone help me?

 

[Rick B]

Why don't you just download the video collection template from the Microsoft
website and modify it to meet your needs?

 

[Scooper2241]

I tried to download a tepmlate but their isn't one for access 2002 also I
still have know i dea how to link the speekers tabe with the video table to
get everything to come together.

 

[Sprinks]

Hi, Scooper.

There is a natural one-to-many relationship between a tape and the speakers
on it, so this would be better implemented with a separate table (much like
an OrderItems table is to an Orders table). You should make a table for the
different tape categories, and include a numeric TapeCategory field (a
foreign key) in the Tapes table:

Tapes
-------------------
TapeID AutoNumber or Integer (PK)
TapeDate Date/Time
ProgramTitle Text
FootageType Text (or Integer foreign key to a FootageType table)
Agenda Text
TapeCategory Integer (Foreign key to TapeCategories)

TapeCategories
--------------------
TapeCatID AutoNumber (PK)
TapeCat Text

Speakers
-----------------
SpeakerID AutoNumber (PK)
FName Text
LName Text

TapeSpeakers
-----------------------
TapeSpeakerID AutoNumber (PK)
TapeID Integer (FK to Tapes)
SpeakerID Integer (FK to Speakers)

Your main form would be based on Tapes, with an embedded continuous subform
based on TapeSpeakers. Use combo boxes for any fields where you wish to
limit the entry to specific values.

As a number is just an identifier, I'm not sure why you need a separate
numbering system for each category, unless it's to satisfy users who don't
want any "gaps" in their category. In that case, I suggest you add an
integer field called TapeNumber, and use the Category AfterUpdate event to
determine the maximum number of that category, add one, and assign it to the
field. See VBA Help on the DMax function.

Hope that helps.

Sprinks