If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
DB Table Design in MS Access Question (Sub DB)
Is there a way to create an "array" of items within a MS Access DB Field?
For example, I'm making a movie DB and I want one field for Actor/Actresses. I have a separate DB with Actor/Actress Names and it's automatically enumerated and each actor or actress is assigned a number. Basically, I want to allow the Actor(s)/Actress(es) field in the main DB contain more than one Actor/Actress "ID". Is there a way to do that? -- Brian Millikan Computer/Software Engineer Microsoft Control Applications (Dialog-Based) Specialist / Hardware Driver Development / User-Level Hardware Device Interfaces |
#2
|
|||
|
|||
That's not the way to go! The relationship between movies and actors is
a classic many-to-many one, so what you need is another table with one field for MovieID and one for ActorID (foreign keys), which will have one record for each actor in a movie - as well as one record for each movie each actor has been in. You could call it tblCast, I suppose? HTH, Nikos BMillikan wrote: Is there a way to create an "array" of items within a MS Access DB Field? For example, I'm making a movie DB and I want one field for Actor/Actresses. I have a separate DB with Actor/Actress Names and it's automatically enumerated and each actor or actress is assigned a number. Basically, I want to allow the Actor(s)/Actress(es) field in the main DB contain more than one Actor/Actress "ID". Is there a way to do that? |
#3
|
|||
|
|||
Thanks for the input. I'm relatively new to "Database Engineering". I"m a
Visual C++ software design by trade and work mostly in embedded software applications and Visual C++ Dialog-based control program to "talk" to the embedded system. So, I'm very much a "newbie". Can you explain a bit further the many-to-many philosophy because there are other multiple search criteria that I want for each movie.... like Genre (the move may fit into more than one genre), Content (language, violence, sexuality... etc), Actor/Actresses, etc. So, it seems I need several tables to describe each movie. So, what is your suggestion. Is there a template in Access that I might be able to use an an example? Eventually, I'm going to "port" this DB to a server (MySQL or ProgreSQL) for maintenance and SQL searches. I want to be able to search or filter selection based on rating, content, actor, etc. Thanks, Brian "Nikos Yannacopoulos" wrote: That's not the way to go! The relationship between movies and actors is a classic many-to-many one, so what you need is another table with one field for MovieID and one for ActorID (foreign keys), which will have one record for each actor in a movie - as well as one record for each movie each actor has been in. You could call it tblCast, I suppose? HTH, Nikos BMillikan wrote: Is there a way to create an "array" of items within a MS Access DB Field? For example, I'm making a movie DB and I want one field for Actor/Actresses. I have a separate DB with Actor/Actress Names and it's automatically enumerated and each actor or actress is assigned a number. Basically, I want to allow the Actor(s)/Actress(es) field in the main DB contain more than one Actor/Actress "ID". Is there a way to do that? |
#4
|
|||
|
|||
suggest you read up on table normalization/relationships. see the following,
from a newsgroup post of March 13, 2005: ******** See the following link for more information than you could possibly imagine: http://www.ltcomputerdesigns.c*om/JCReferences.html There are sections on Normalization and Book Recommendations. -- Jeff Conrad Access Junkie Bend, Oregon ******** hth "BMillikan" wrote in message ... Thanks for the input. I'm relatively new to "Database Engineering". I"m a Visual C++ software design by trade and work mostly in embedded software applications and Visual C++ Dialog-based control program to "talk" to the embedded system. So, I'm very much a "newbie". Can you explain a bit further the many-to-many philosophy because there are other multiple search criteria that I want for each movie.... like Genre (the move may fit into more than one genre), Content (language, violence, sexuality... etc), Actor/Actresses, etc. So, it seems I need several tables to describe each movie. So, what is your suggestion. Is there a template in Access that I might be able to use an an example? Eventually, I'm going to "port" this DB to a server (MySQL or ProgreSQL) for maintenance and SQL searches. I want to be able to search or filter selection based on rating, content, actor, etc. Thanks, Brian "Nikos Yannacopoulos" wrote: That's not the way to go! The relationship between movies and actors is a classic many-to-many one, so what you need is another table with one field for MovieID and one for ActorID (foreign keys), which will have one record for each actor in a movie - as well as one record for each movie each actor has been in. You could call it tblCast, I suppose? HTH, Nikos BMillikan wrote: Is there a way to create an "array" of items within a MS Access DB Field? For example, I'm making a movie DB and I want one field for Actor/Actresses. I have a separate DB with Actor/Actress Names and it's automatically enumerated and each actor or actress is assigned a number. Basically, I want to allow the Actor(s)/Actress(es) field in the main DB contain more than one Actor/Actress "ID". Is there a way to do that? |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Relations between tables | KLP | Database Design | 12 | March 7th, 2005 04:42 AM |
SIMULTANEOUSLY access to a table?! | Julia | General Discussion | 1 | November 5th, 2004 10:02 PM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Autonumber | Ally H. | General Discussion | 7 | August 27th, 2004 04:51 PM |
database design question | e-mid | Database Design | 9 | June 16th, 2004 09:42 PM |