I've posted this in general as the answer could involve use of a query, or a report or it could involve changing the table structure, I don't know exactly which other forum would be most relevant.
Say I have a many to one relationship, for simplicity we'll call the table on the 'many' side tblThing and the table on the 'one' side tblGroupofThings.When managing the records in these tables it is important that each Group of Things contains at least one Thing, but it isn't important that each Thing is part of a Group of Things.
Things and Groups of Things are constantly being added to the table. As I see it, the only way to manage this is from the Things table, or at least a form based on the Things table, as I am editing a foreign key for tblGroupofThings inside tblThings. However it would be easier for me to manage it from the Groups of Things end, so that my workflow goes as follows:
I add a new Group of Things to tblGroupofThings,
I then 'put inside' that Group of Things, the Things that belong inside it (i.e. make the foreign key field in those Things point to the Group of Things.
Managing from the Things end means I have to start of with the new Groups of Things which are 'empty' at the moment, decide which things need to go in them then swtich to Things and remember which Things need to go into which Groups of Things.
I hope you can see what I'm getting at. I'm sorry for all the talk about putting records inside other records when I'm really talking about foreign keys. I know that's not know it really works but to the user that's how it should appear to work.
The only really viable option I can think of is a many-to-many relationship instead. I could certainly make it work, as far as the interface goes, but it seems wrong to me if a Things can conceptually be in more than one GroupofThings, when physically it can't, and may cause unforeseen problems down the line.
Say I have a many to one relationship, for simplicity we'll call the table on the 'many' side tblThing and the table on the 'one' side tblGroupofThings.When managing the records in these tables it is important that each Group of Things contains at least one Thing, but it isn't important that each Thing is part of a Group of Things.
Things and Groups of Things are constantly being added to the table. As I see it, the only way to manage this is from the Things table, or at least a form based on the Things table, as I am editing a foreign key for tblGroupofThings inside tblThings. However it would be easier for me to manage it from the Groups of Things end, so that my workflow goes as follows:
I add a new Group of Things to tblGroupofThings,
I then 'put inside' that Group of Things, the Things that belong inside it (i.e. make the foreign key field in those Things point to the Group of Things.
Managing from the Things end means I have to start of with the new Groups of Things which are 'empty' at the moment, decide which things need to go in them then swtich to Things and remember which Things need to go into which Groups of Things.
I hope you can see what I'm getting at. I'm sorry for all the talk about putting records inside other records when I'm really talking about foreign keys. I know that's not know it really works but to the user that's how it should appear to work.
The only really viable option I can think of is a many-to-many relationship instead. I could certainly make it work, as far as the interface goes, but it seems wrong to me if a Things can conceptually be in more than one GroupofThings, when physically it can't, and may cause unforeseen problems down the line.