Help with creating tables/forms to collect unlimited data

[Luvz2Fly]

Greetings All!

My first post here so I hope I got the right area (or even have the right Title!). I am somewhat new to Databases so go easy I often get the theory of what to do but have a tough time crossing over into the execution. Anyway my question is as follows (even if you don't give me the answer, pointing me in the right direction makes it fun for me to figure out myself!)

I have a database where I need to enter the names of people that belong to a type of bank account account. Normally I would just add a First/Last or Company field to account information table but the problem is that these accounts can have up to 20 or more people associated with them. I of course can take the easy way out and make a form/table with Name1 Name2 Name3 etc... but that would be a waste of space in the form and table and not the correct way to do it. So I believe I needed to make a Table of Names and relate the names to the Accounts Table

What I don't get yet is how to PROPERLY make this happen, how to make a form where the number of text fields is unknown/unlimited. I tried Subforms which I think is the best answer but it didn't seem to work correctly (I am sure problem lies between keyboard and chair!) - the names in the subform would appear in every new account info record. I also read something about ListView but not sure if thats the correct path to head down.

Anyway I know its very basic but I am missing something in my understanding... I wasn't even sure what the relationship is since the people in the names table could be associated with many accounts (many-to-many sounds logical but I read its not common)

I tried all night - I now ask for someones help! I thought the closes and most common example to search on might be a Teacher/Student database where each Teacher (account ) could have unknown number of Students (account name) but that wasn't fruitful either

Thank you in advance!!

(using Access 2007)

 

[MarkK]

You likely need the tables tAccout, tPerson, and tAccountPerson

[B]tAccount[/B]
AccountID
AccountName

[B]tPerson[/B]
PersonID
FirstName
LastName

[B]tAccountPerson[/B]
AccountPersonID
AccountID
PersonID
JoinDate

This is textbook many-to-many, which is in fact very common.

 

[Luvz2Fly]

You likely need the tables tAccout, tPerson, and tAccountPerson

[B]tAccount[/B]
AccountID
AccountName

[B]tPerson[/B]
PersonID
FirstName
LastName

[B]tAccountPerson[/B]
AccountPersonID
AccountID
PersonID
JoinDate

This is textbook many-to-many, which is in fact very common.

Got it - that makes complete sense .. I guess for me the part I haven't wrapped my brain around is how to turn that into a form for user input. Do I use "subform" - I tired that but it didn't seem to work as expected. Any pointers?

Thanks a million for the help!

 

[MarkK]

Here's a fast & dirty DB that demonstrates a many-to-many relationship and how you can rig a subform on the join table. The subform, based on the join table, can appear on either of the parent forms, and hides columns that would be redundant as the case may be.
Check it out. See if it gives you ideas about how to proceed.
Cheers,

 

[Luvz2Fly]

Here's a fast & dirty DB that demonstrates a many-to-many relationship and how you can rig a subform on the join table. The subform, based on the join table, can appear on either of the parent forms, and hides columns that would be redundant as the case may be.
Check it out. See if it gives you ideas about how to proceed.
Cheers,

Excellent! I think I might get it
I just got back from work travels and am looking at the example - much appreciated!!!