I'm sure that this is probably 'Databases 101', but I can't seem to figure it out.

So, I'm setting up a database in Access - that I will want to move to MySQL, and I currently have 4 tables, set up as so:

 - ID*
 - itemName
 - etc.
 - ID*
 - table1ID
 - subitemName
 - subitemDescription
 - table3ID1
 - table3ID2
 - table3ID3
 - table4ID1
 - table4ID2
 - table4ID3
 - table4ID4
 - ID*
 - itemName
 - ID*
 - itemName

You see, the items in Table2 are associated with multiple items from both Table3 and Table4. For instance, 1 item in Table2 may be associated with 3 items from Table3 (and therefore, I'm assuming, 3 unique ID's), and 4 items from Table4 (and, again, 4 unique ID's).

So, I type the unique ID* value into each field, and create relationships from the Table3 ID* field to each table3ID field on Table2 (and same for Table4), yet when I expand the items on Table3 or Table4, I get nothing. However, if I only link the ID* to one of the table#ID fields, then expanding the item on its respective table shows all the items referencing it on Table2.

Does anyone know what I'm doing wrong/not doing right/generally effing up? I'd greatly appreciate any help.

  • sorry...don't have time for a fully researched answer at this time. However, as I recall, Access with default settings has trouble with displaying editable data on a form/report if it is drawing from more than just a 2 or 3 tables at a time, but there are way to set the form or the queries upon which is based to allow for it. One other thing: your use of specific columns for table4ID1,2,3,4 etc is in violation of best practices for relational databases. Basically table 2 Should have a ID, subitemName, Description and a new table2a should have ID, Tab2ID, otherTableName, IDNum. – mpag Aug 20 '16 at 0:00
  • Thanks for the reply! So, I would need subtables for each column that's referencing either table3 or table4? And then link 1 column on each subtables to table2, and another column to table3, or table4? For instance, table2 is episodes, table3 is directors, and table4 is writers. Would I need epDirectors1, epDirectors2, epDirectors3, epWriters1, epWriters2, epWriters3, epWriters4 tables? – Steve Aug 20 '16 at 13:49
  • Ok, I was able to register and merge all three accounts - thanks guys! Also, I made some changes to my database, not sure if this is what it needs to be like (doing the same thing for the actors is going to be a task for sure O_O)? imgur.com/LTMazlR – Steve Aug 22 '16 at 13:27
  • somehow I didn't see your replies until now. If still working on this, you shouldn't need more than 1 epDirector and 1 epWriters table. If you're concerned by who is the first listed director from who is the third-listed directory, you could add another field to epDirector - DirectorPosition, which would be an integer position that you would enter 1 2 or 3. If unconcerned about listing order, keep in mind that you can have more than one record in epDirector that points to the same episodeID..it doesn't have to be a 1-to-1 relationship, or it'd be functionally no better than a spreadsheet. – mpag Jan 13 '17 at 22:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.