Web Development



Simple way of dealing with many-many relationships in MS Access

By beingveryanonymous ·
I'm having trouble working out a way that doesn't require programming to deal with a set of many to many relationships in Access. I've gathered that the best way to achieve this is to have three linked tables, with two one-to-many relationships. So, with a set of names and details in one table, and a set of conditions in the other, I'm linking them by a third table with <nameID>;<conditionID>

Is there a simple way of setting up a form to enter data, either by multiply-selecting from a list, or having a list of checkboxes to tick against?

And how do I set up queries to generate data about numbers, totals, averages, etc?

Many thanks,

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

A tad confusing...

by Tony Hopkinson In reply to Simple way of dealing wit ...

Data entry on many to many is done as one to many, after all you can only enter one record at a time.

Say you had People and addresses, many people could be at one address, many p0eople could have several addresses

PersonID, PersonName
AddressID, Address
PersonID, AddressID

If you wanted to enter a new address against an existing person then you would
insert a new address record, get the id
insert a new link record with the personid

This effectivley reduces the link to a 1 - 1

If you wanted to show all the addresses a person had that's 1 to many just a join from persons to personaddresees to addresses where personID = ...

To show all the persons at an address
join Addresses to PersonAddresses to Persons where addressid = ..., again 1 to many

You could show all addresses of all persons who had at least one address in Kent which is a many to many, but it would be very unfriendly

The main reason for a linktable aside from normalisation is so you can have a person with 0 to many addresses and vice versa, without having acolumn in one table for each possible instance of address, which it restrictive, wasteful and damn messy.

The link table lets you implement as two one to manys.

Aggregate examples might be

Select PersonID, PersonName,Count() from Persons, Personaddresses
Where Persons.PersonID = PersonAddresses.PersonID
group by PersonID


Select PersonID, PersonName,Count() from Persons, Personaddresses, Addresses
Where Persons.PersonID = PersonAddresses.PersonID
and PersonAddresses.AddressID = Addresses.AddressID
and Addresses.Address Like '%Kent%'
group by PersonID


Related Discussions

Related Forums