Many-to-Many Is the Future

Gone mid-show

I was watching a video by Dr Steve Turley the other day. It opens with a news presenter finding out, live and in the middle of his own programme, that his network had been barred from travelling with the president on Air Force One. Hence the title: he learned he was gone mid-show. Imagine that: doing the job you've done for years while the world quietly moves on without you.

I'm not here to take sides on the politics. What caught my attention was the feeling, because every Access developer knows it. Every few years somebody on the forums asks whether Microsoft is going to drop Access. Will we find out halfway through a project that the thing we've built our working lives on has been switched off?

I don't know what Microsoft will do. Nobody outside Redmond does, and I'm not convinced everybody inside does either. But I do know something that should make you feel a lot better, and oddly enough it's hiding in the same video.

The bit that made me sit up

About ten minutes in, Dr Turley says that research on digital media describes a shift from "one-to-many broadcasting", the Walter Cronkite era, to "many-to-many communication", with audiences becoming "active nodes".

Dr Steve Turley, "He Learned He's GONE Mid Show!!!" (29 September 2026), starting at 10:03. Watch on YouTube from 10:03.

In plain English: a handful of broadcasters used to send the news out while everybody else sat on the sofa. Now everybody records, shares and chooses. I heard "one-to-many" and "many-to-many" and my ears pricked up. I've been drawing those two phrases on whiteboards since the 1990s.

Yes, I know it's a tenuous link

I admit it. A media researcher and an Access developer mean rather different things by "many-to-many". But the shapes are closer than you'd think.

Old-style broadcasting is a one-to-many relationship: one broadcaster, many viewers. In Access terms that's one record in a main table and many related records in another. One customer, many orders. One student, many hobbies. You've probably built dozens with a main form and a subform.

The internet version is many-to-many. Many people create, many people watch, and anyone can be on either side. In Access terms: many students, many hobbies, and any student can take up any hobby.

The media world got there in the last twenty years or so. Relational databases were doing it long before. So when I say many-to-many is the future, I'm being a bit cheeky. It's also the past.

The spreadsheet trap

Here's how most people get into trouble. They start with something that looks like a spreadsheet: one row per student, a column for each subject, and a tick box in each column.

It works. That's the trap. Then the school starts offering French lessons. In the spreadsheet you just add a "French" column. You can do the same in Access, but if your database is well advanced, with plenty of queries and forms built on that table, you'll have to modify every single one of them. Not something you want to do often!

Books are the same. Years ago on Access World Forums I answered someone whose book table had fields for Author 1, Author 2 and so on. As I told them then, that design makes it "difficult to search for all the books by a particular Author". The answer was to put the authors in a table of their own, and then add another table holding nothing but the book ID and the author ID.

Many-to-many in practice

Here's the students and hobbies example I've used for years. Three tables:

tblStudent

tblHobby

tblStudentHobby (the junction table)

The first two are ordinary tables. The third is the clever one, and it's hardly anything at all. As I wrote on my old Many to Many Relationship page:

"In their simplest form Many to Many relationships are just two columns in a table."

Read tblStudentHobby from the left and you can see that Anne (1) does chess and guitar. Read it from the right and you can ask who plays chess: Anne and Bob. Same table, both directions.

I also pointed out on that page that "the Many to Many name can be misleading because in essence, you only have one student related to many hobbies, and the reverse". A many-to-many is really two one-to-many relationships meeting in the middle, and that little two-column table does all the work.

Want a new hobby? Add a row to tblHobby. No new column, no redesigned queries, no evening lost to fixing forms. Books and authors work exactly the same way: tblBook, tblAuthor, and tblBookAuthor with just BookID and AuthorID.

On screen it's the Access you already know: a main form on tblStudent, a datasheet subform on tblStudentHobby with a combo box looking up tblHobby, and the master/child links set to StudentID. Forget that last bit and every student appears to have every hobby. A lively school, but not an accurate one.

Tables outlived the clay, and they'll outlive any product

Back to that fear of being gone mid-show. Here's the reassuring part. Tables, and the relationships between them, aren't a Microsoft invention. People were keeping lists of who owed what thousands of years before anyone had a computer. I wrote about that in The First Database. The relational model was worked out about twenty years before Access 1.0 arrived, and the same ideas run today in SQL Server, SQLite, PostgreSQL and the rest.

In a normalisation talk I gave around 2007, I described the information moving from a flat format into a column format, and said: "This is the essence of the difference between a spreadsheet and database!" I'd stand by that today. A properly designed set of tables isn't locked inside Access. It's the flat, Author1/Author2 design that gets stranded.

Who owns the junction table?

Now the twist. Remember why the name "many-to-many" is misleading. Anne never links to chess directly. Every link goes through tblStudentHobby, and whoever controls that table decides which students meet which hobbies.

Look at "many-to-many communication" the same way. I don't really talk to you directly, and you don't talk to me. What I post goes through a platform, whether that's YouTube, X or Facebook, and the platform's rules and algorithms decide what gets seen and by whom. Dr Turley reaches us through YouTube too. So perhaps the gatekeeper didn't vanish. Perhaps it just moved into the junction table.

I'm not saying that's good or bad. It's simply the shape of the thing. But it's a fair question to ask of your own database: who owns your junction table? If your tables, your relationships and your data sit somewhere you control, with the design written down, they're much harder to take off air. That's the direction I've been heading at Nifty Access: an open Postgres back end with the Access design preserved, so whatever the front end turns into, the data and the structure survive. If Access ever is gone mid-show, a well-normalised database simply changes channel.

Hence my usual advice: get it right, right from the beginning. And own the junction table.

Get in touch

If your Access database has Author1/Author2-style columns, a tick box for every subject, or you're simply lying awake worrying about the future of Access, get in touch through the contact page on niftyaccess.com. I've been untangling this sort of thing since the 1990s and I'm happy to take a look.

For the media side of the story, watch Dr Turley's video. I'll stick to the tables.