One To Many Relationships In Access

Take That were recently voted Best British Group at the Brit Awards, two decades after they released their debut single. This prize was hailed as a complete turnaround by those fans who had been devastated back in 1995 when the band split. After the TV news cameras' film of sobbing girls had faded from our TV screens, the band members took stock of their situations and the most prominent post-Take That solo career belonged to Robbie Williams. But why am I telling you this?

When Robbie released his debut album Life Thru a Lens in 1997, I was the manager of a video rental store that also hired out CDs and the way that information relating to these CDs was stored serves as a good example of how relationships work in Access, although I will be referring to versions from this side of the millennium.

The first database to set up will be called Albums and it will consist of just two fields, the album ID and the title. To do this I create a new database and open it in Design View. I enter the title of the first field as Title ID and from the Data Type menu I select AutoNumber to give each album its own unique identifying number. I highlight this field and from the toolbar in pre-2007 versions, or the Design ribbon in post-2007 versions, I click on the key icon and in so doing I have made this field the Primary Key, which allows me to access the details of each album by its unique identifying number. I name the next field Title and from the Data Type menu this time I select Text.

With my fields set I switch to Datasheet view and enter the titles of all of the albums in stock. I need only type in the titles, as the Title ID field will number the albums automatically. I save the database and put the kettle on.

Now I create my second database, this time called Artists. As before I go into Design View and the first field I create is Artist ID. Again I select AutoNumber to list the artists and I make this field my Primary Key. The next field gets the title Artist and I enter the artists' details and save as before.

I now have two databases relating to the CD stock in the store, and I want to create a relationship between them. I need to go back into my Albums database in Design View and add another field. I will call this field Artist ID and from the Data Type menu I will select Number. I enter the ID for each artist and then save and close, and I now have an Artist field in my Album table.

On the Database Tools ribbon I click on Relationships and this opens up the Show Table dialog box. I click on my two databases and add them to the dialog box and I see that they have both appeared on the Relationships screen. Now I close the Show Table dialog box and work on the relationship between my two databases.

To create the relationship I click on the Artist ID in my Album database and drag it across to the Artist ID in my Artist database, and then release it. The Edit Relationship box will then come up to confirm that I am relating the Artist ID in the Artist table with the Artist ID in the Album table. I check the Enforce Referential Integrity box, which sounds quite technical but it simply means that I will not be allowed to delete a record from the Artist table if the same details are present in the Albums table.

The dialog box also shows that I am creating a One-to-Many relationship. This means that each record in the Artist table may have several linked records in the Album table but each record in the Album table will have only one corresponding record in the Artist table. For example, our friend Robbie released seven studio albums before returning to the Take That fold. If I stocked his entire repertoire, he would be linked to seven records in the Album table, but each of those albums would only be linked to Robbie Williams in the Artists table.

I click on Create and the two tables on the screen become linked, and the One-to-Many relationship id signified by a figure 1 at one side of the link and a double zero at the other. I save and close and the open the Albums table and, in the new Artist ID field I just added, I enter the artists' ID from the Artist table.

Now if someone comes in saying they enjoyed Life Thru a Lens and they would like to listen to more stuff from this artist, then I can access our entire Robbie Williams stock by using the relationship I set up above.

Relationships in Access can be a lot more complex than the example I have given, but they are an extremely useful tool in the vast Access toolbox. Learning more about how relationships work would be a worthwhile step. You can Take That as read.