Results 1 to 7 of 7
  1. #1
    Join Date
    Feb 2004
    Posts
    99

    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.

    Many thanks.
    Attached Files Attached Files
    pachamama
    www.researchbank.co.uk
    Fortune Global 500 Databank

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Your single table design doesn't lend itself to do anything fancy here.

    What you need is to have a table with Coprofiles and a separate table holding your Execs. Then you need to relate the two tables together via an ID for each Coprofile.

    That will give you a start... then you can look at main/subforms.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Feb 2004
    Posts
    99
    Thanks Startrekker for your help, going to have to think clever then.

    If Icreate 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?

    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?
    pachamama
    www.researchbank.co.uk
    Fortune Global 500 Databank

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    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

    HTH

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by StarTrekker
    ...Then you need to relate the two tables together via an ID for each Coprofile...
    Just to clarify that ST means identifier when he uses th term "ID".

    ID should not isntantly mean an autonumber. It simply means something that uniquely identifies a row in a database, which can be a text field or even a number of fields combined!
    George
    Home | Blog

  6. #6
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    @pachamama

    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.

    I hope what they have said helps,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Quote Originally Posted by pachamama
    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

    CO
    ---
    COID <--- Primary Key
    COSpecificFields...


    EXEC
    ----
    ExecID <---- Primary Key
    COID <---Foreign Key. This is the one that links to the CO table
    ExecSpecificFields...

    Hope that helps
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •