Multiple data entries for one field

[kejasa]

I am creating a database in Microsoft Access which contains data about
reports. The data in the table includes fields such as report id#,
title, link to file, date prepared, and author name. The problem that
I run into is with the author name field. A couple of notes about this
field: (1) it is possible for there to be more than one author for a
report, and (2) it is possible for one author to be the author of many
different reports.

My primary key is defined as the report id#, because it is a unique
number that is assigned from our books to identify the reports that we
generate. I do not understand the best way to include the author name
in the field since there can be more than one. I know enough about
database design to know that putting more than one entry in one field
isn't good. I've read some other posts on similar topics, and it seems
as though creating two different tables (one with the author names and
the other with all other information) and then relating them is the way
to go. I admit that I'm no expert when it comes to database design. I
was hoping that someone might be able to provide a possible solution
and how I might go about doing it.

 

[Guest]

You will need thre tables at a mimimum --
- Report table with ReportID as a primary key field.
- Author table with AuthorID as a primary key field.
- ReportAuthor table with ReportID and AuthorID as foreign keys.

Set a one-to-many relational from Report table ReportID to
ReportAuthor.ReportID and another one-to-many relational from Author table
AuthorID to ReportAuthor.AuthorID.

Use a form/subfom for data entry with the report information in the main
form and author information in the subform. Use datasheet view for the
subform.