Unanswered: One to Many relationship modelling & Form design (Was "Urgent help needed please")
I am sure this is a no brainer but can't figure it out at all for myself and would really welcome some help.
I've attached an mdb with a table called coprofiles with a table called coprofiles.
There are 50 company profiles with their respective officers making the table look like there are 841 entries as all coprofiles have multiple execs.
What I need to do is to group the officers with the coprofiles so that I can edit cleanly in a form so that I can look at 1 coprofile at a time with all the respective officers listed on that one page withot having to scroll through oodles of gubbins.
I know that this not very technically explained but in a nutshell I want to be able to edit in a one view snapshot rather than have to scroll up and down all the time.
I hope I am going someway to making sense and would really like some help.
I haven't dowloaded and opened your MDB but based on ST's response it sounds like you have designed a spreadsheet rather than a relational database. Forget about forms for now - form design follows on from good, clean, normalised database design. Get your database right first and the forms will follow: http://www.tonymarston.net/php-mysql...se-design.html
The advice you have been given thus far is excellent advice. Once you create the two tables, one the Company Profiles and the other the Company Executives you will be able to relate those tables on a unique identifier (ID) field(s). This will then allow you to do as you are wanting with the forms. Always keep in mind that if you make the database design correct the forms will be easy, simple, and correct.
You are wanting a 1 to Many relationship setup. 1 Company Profile to Many Company Executives.
I hope all the advice you have received thus far helps you complete this project. If you need any further help please let us know.
I haven't really offered any of my own advice, I was just re-capping what other people have stated in a different manner for a possible better understanding of what was needed.
If I create two tables, 1 with contact details other with execs and relate on coID or other field, i can follow that....
Then create a form with just the execs (possibly grouped) then logically I would get one co at a time snapshot form with the related execs... is that right or am I talking twaddle?
Yes and no. You would need to create a mainform (singleform) for your co's and a subform (continuous) for your execs. Place the subform on the mainform and THEN you'll have a form that shows one CO at a time and on that form, the subform shows all the related Execs.
I would then update via a form and ultimately be able to rebuild the table to mimic the original as all entries would be related?
Am I on the right tracks?
Yes!! You update via form and you can re-create the original look of your current table by using a query that has both tables
COID <--- Primary Key
ExecID <---- Primary Key
COID <---Foreign Key. This is the one that links to the CO table