AnsweredAssumed Answered

Join table / many-to-many problem

Question asked by freeflight on Sep 21, 2009
Latest reply on Sep 25, 2009 by comment_1


Join table / many-to-many problem


I've created a database by importing data from Excel related to horse breeding. Its two main tables are 1)Stallions and 2)Mares (there are others, but these are the main ones).


The database will be used primarily for analysis and reporting, with a data update to each table once per year to input new statistics.


Problem: The Stallions table shows stallions (by name & unique ID) by total number of mare services per year and the total outcomes - i.e: one record per stallion per year (with a number of other fields in the table).


The Mares table shows each individual service of all named mares (by name and unique ID), by stallion (by name and ID), by year - i.e: many records per stallion per year, and single records per mare per year.


This looks to me like a complicated many-to-many relationship that I assume needs a join table. I want to show, for example, stallion and named mares by year (from the Mares table) together with other related data from fields in the Stallions table.


Question: Is a join table what I need? If so, is it possible to create and fill a join table using, say Stallion ID and Covering Year as keys, when all of the table data for both tables already exists in the database? (There are several hundred thousand records in Table 2).