Access 2003

[kbrizs]

I hope I am in the right place. I need some help in creating a database using Access 2003. If this is the right place, than here is what I am looking for.

I would like to create a table with one column named "Title". I know how to do this.

I would like each title to have an unlimited number of time, notes.

What I mean is, if I have 3 titles in the main table.

"Title"
Batman
Superman
Spiderman

I would like to have the option to be able to click on a title and have an unlimited number of notes to add. Such as ...

"Title"
Batman
     |
      --- "Time"            "Note"
           00:00:00        Start of movie
           00:20:00        Batman gets in batmobile
           00:30:00        ... etc.
           ...                 ... etc.
Superman
     |
     --- "Time"             "Note"
          ...                   ...
Spiderman

I have a little trouble understanding what I would need. I was thinking 2 tables, but I am not sure what other columns I need, such as ID column and foregin key and etc. If someone could help me with this I would appreciate it. I am using Access 2003 and I will be creating the interface in VB .Net 2008 Express, so I am sure I will have more questions to ask.

Also, what is the most lengthy note I can add. Sometimes I could use the option to add a few paragraphs. What data type would I use.

[samtheman]

Hi you should use Text/String data type for your description - the max text per field is 255 characters.

[dee-u]

You have to look for database normalization, a sample design of which is:

Main Table:
Field1: ID (Primary Key, can be an AutoNumber, as long it is unique)
Field2: Title

Child Table:
Field1: ID (Foreign Key, same as ID of Main Table)
Field2: Time
Field3: Note

You will have to specify the relationship between the two tables. Using sql you could join them with something like this (not tested):

SELECT * FROM Table1 INNER JOIN Table2 ON ID=ID

[brucevde]

Every table should have a Primary Key. I would have created this Child table

Child Table:
Field1: ID (Primary Key, can be an AutoNumber, as long it is unique)
Field2: MainId (Foreign Key, same as ID of Main Table)
Field3: Time
Field4: Note

The SQL Statement would then be
SELECT * FROM Table1 INNER JOIN Table2 ON Table1.Id = Table2.MainId