Multiple entries in one field? If not, how should I design the table?

[zhypaul]

Let's say if I am building a database for a library and in a table the book titles are the primary key and there is a field "authors" to record the authors' names.

But sometimes a book may have several writers and if I type all of them into one field, Access only recognise as one person. So how can I format/set up so that when I use query or filter, each one of names can be identified?

If multiple entries are not feasible, how else should I design my tables?

Sorry Im new to access and this is in fact my first assignment, I hope you can help me, thank you!

 

[Isskint]

create a table for authors, lets call it BookAuthors

BkAuthID AutoNumber Long PK
AuthorName Text
BookID Text FK to Book titles table

Remove the author field in Book titles table

You would need to add a form to record the authors. This could be a subform on the form you use to add/edit books or a popup form. This way you/your users can record each author separately.
Create a query that lists all authors for each book and you can reference this in any filter/query when looking for authors.

have a look at the attached. I have included a table of authors and use this to supply author names for the books. Also i have changed the books primary key to an autonumber and use this to link books and authors

 

[zhypaul]

create a table for authors, lets call it BookAuthors

BkAuthID AutoNumber Long PK
AuthorName Text
BookID Text FK to Book titles table

Remove the author field in Book titles table

You would need to add a form to record the authors. This could be a subform on the form you use to add/edit books or a popup form. This way you/your users can record each author separately.
Create a query that lists all authors for each book and you can reference this in any filter/query when looking for authors.

have a look at the attached. I have included a table of authors and use this to supply author names for the books. Also i have changed the books primary key to an autonumber and use this to link books and authors

Thank you very much, but the problem is, within the table I am building, alomst every field has the same problem, e.g. "issues" the book covers may invovle 6 or 7 topics, coutries of publication may be more than one country, so I don't think it is feasible to create so many "sub-tables"

Thanks again!

 

[Beetle]

so I don't think it is feasible to create so many "sub-tables"

That's exactly what you should do. That's how relational databases like Access are designed to work.

 

[Galaxiom]

Books and Authors will have a many-to-many relationship. There should be a table for each and then a junction table to connect them. It sounds like some of the other relationships will require similar data structures.

Having the information scattered across many tables looks like a nightmare to a human but it is important to remember that relational tables are designed for the comuter to work with.

It is crucial to get this right and there are many ways to get it wrong. You should post your data structure here for confirmation before you go on to design forms and reports.

Make sure you thoroughly understand normalization and the use of junction tables before you proceed.