Newbie wants to create simple database, all u experts please help!

[Vampii]

I would like to create a simple video tape database for a video production
company I'm working for. What I want sounds simple enough, but from a
newbie's point of view, it's giving me a big headache.
Basically there are about hundred tapes in the library. Each unique tape
contains many shots. The aim of a database would be to be able to think of a
shot required and then find it efficiently, or even think of a tape, and be
able to find out what shots are in that specific tape.
The reason why I wanted to use access was because the previous company that
was using it had a very productive library and were able to find shots or
tapes easily.
For example, if i were to type in a search of coal mines, every tape that
consisted of coal mines would pop up in a new area, including the time codes
where they started on the tapes. This made finding of the physical tape and
specific time code easy.

The information that I need includes a TapeID, Category and (Timecode
& shot description that were linked). For eg
TAPE ID :COM01 CATEGORY: communications
TIMECODE SHOT DESCRIPTION
00:20 Radars
01:30:00 satellites
02:40:00 equipment

TAPE ID:COM02 CATEGORY: Telephony
TIMECODE SHOT DESCRIPTION
00:30 Phone lines
00:55 Mobile equipment
01:00:00 Radars

I want to be able to type a search after the database is done, for eg radars
and for the database to come up with
COM01 00:20 - Radars
COM02 01:00:00 - Radars

Being the noob that I am, I am trying to play around with access, but can't
get the tables to store the imformation I want about the time codes and
description. I also can't seem to get the find function to work at all. For
the life of me, I can't figure out how the previous company I was working for
did it. Please help, thanks

 

[roccogrand]

Vamppi,

The key to your problem is the use of a parameter query.

Create a simple query of your table and in the Criteria column for the Shot
Description type in [Enter the feature of interest.] The brackets are
required.

Now create a form or report to display the resultant in the format you like.

When the query runs, it asks the user to enter the keyword(s). It matches
this to the the data in the field and displays the data according the form or
report.

There are other ways to achieve this.

LDN

 

[Vampii]

Hi there,
thanks for ur reply. I don't know how to input all the data
properly. I can get the tables up, but don't know how to input multiple lines
of info-timecodes and shot descriptions relating to the tape. The parameter
query, how do I set this up. I'm a total noob to access, please help!

 

[roccogrand]

Based on your original statement, you will need two tables to start your
application.

The first table will contain data about the individual tapes in the library.
I would name this table tblTapes. It should have fields such as txtTapeID,
txtTitle, datDateShot, etc.

The second table should contain data about each Shot. It would contain such
fields as datStart, datEnd, txtDescription. The most important field in
tblShots is txtTapeID. This field will relate the two tables. The
description field could actually be a memo field, depending on how much text
you put in it.

Notice that I am using a standard naming convention for each field, e.g. dat
for dates, txt for text strings, etc. This will help you manage the
application later and it is a good thing to start doing now. (I suggest
making the time stamp a date field.)

The table tblTapes will serve as the data source for a form (name it
frmTapes). The table tblShots will serve as the data source for a subform
(name it subfrmShots). Create the form and subform, I am assuming that you
know how.

Open frmTapes in design view. Position the fields conveniently toward the
top of the form. Now insert subfrmShots on the main form that you named
frmTapes. Use the wizard to tell Access to link the field txtTapeID in the
form (frmTapes) to the field txtTapeID in the subform (subfrmShots).

Set the data in the subform to show in Continuous mode. This form property
is set by selecting the subform (clicking it) and right clicking the Property
selection. Each record in the subform should appear as a line or two in
subfrmShots. Increase the height and width of the subform accordingly.

For each Tape, you will be able to see multiple records for the Shots. That
means that you cannot have a large number of fields in tblShots unless you
have a very large monitor.

After you have positioned the fields in the subform the way you want them,
close down design view. Now you should have a form for storing data in
tblTapes and tblShots. (Technically, this is a one-to-many relationship but
don't worry about that until later. This statement is an act of heresy I
know for true database architects.)

All of this information is basic. If you don't have a good beginning Access
book, get one today from a local library. Later, buy one a month.

Also, the Northwind database that comes with Access is a good application to
start learning Access. It doesn't use a standard naming convention
unfortunately but force yourself to use consistent tlags like dat, txt, mmo,
tbl, frm, subfrm, and qry in your applications.

One more thing, I would like to be able to select a Tape, in your case, from
a List Box. Insert a list box on the main form (frmTapes) and have it show
perhaps 20 TapeIDs. When you insert the listbox, have the Wizard select the
record that you click. I think is the bottom option, if I remember
correctly.

Also, get intimate with Help in Access. I know that I wasted lots of time
myself by not using it early on. I understand that Access 97's help was the
best but 03's help is excellent. And also, check the knowledgebase for tips
and tricks.

Access is a great program but it takes time getting proficient with. Access
2007 is supposed to be easier for newbies and folks with spreadsheet
experience. You may want to create your database in Access 2007 rather than
2003 or earlier. That will keep you from learning one layout now and then
having to learn a new one next year.

Hope this helps.

LDN

 

[mnature]

You might be able to modify this template by renaming some of the labels to
fit your application:

http://office.microsoft.com/en-us/templates/TC010186391033.aspx?CategoryID=CT011366831033

 

[Vampii]

Thank you both for ur replies, will try all of this out.