Results 1 to 2 of 2
  1. #1
    DougM is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2020
    Posts
    1

    Minimising indexes per table with one-to-one table relationships

    I'd be grateful for advice at an early stage in database design to avoid a future big overhaul!



    I'm designing a patient management system. I have a table with core patient demographics details and a systemID as the primary key (autonumber) then one-to-many relationships with several other tables. This exceeds the 32 index maximum for Access and so I've re-organised the child tables into groups (e.g. a contacts groups from which stems one-to-many relationships with a GP table, next of kin table, address table etc). The group tables have a one-to-one relationship with the parent, core deompgraphics table, a foreign key ID field (autonumber) and the primary key is the same as the system ID.

    This reduces the number of indexes required on the core demographics tables but I've encountered some problems creating subforms where the parent form is created from the core table, a sub-form from group tables and sub-sub forms from the child tables in each group. Access wont automatically create an autonumber ID for the group tables in one-to-one relationships when a new parent record is created, despite enforcing referential integrity, cascading changes/deletes etc. The form I'm creating wont let me enter data into sub-sub-forms without the subform record being created first.

    My questions....1) is there a simpler way of reducing the index burden on a single table? 2) If not is their a simple way of automatically creating a record in each group table when a new patient record is added with the same systemID, such that the sub sub forms will accept data in a one-to-many with the group tables?

    I'll be migrating the back-end to MS SQL server eventually (which does not have a limit on indexed fields per table) so would like to design it in a way that makes that easy if possible.

    Sorry for the long first post!

    Doug

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You might get some advice if you post a pic of your relationships. 1 to 1 relationships are to be avoided, so if that's what you have, then it doesn't sound right but I can't decide if your post title describes a goal or is the problem. It is normal that you cannot enter subform data if there is no parent record but it you have a parent record and still cannot, then something else is causing that.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Drop all indexes from table
    By Thompyt in forum Programming
    Replies: 6
    Last Post: 04-24-2019, 11:20 AM
  2. Replies: 4
    Last Post: 06-16-2015, 03:53 AM
  3. Table Indexes
    By Joetoben in forum Queries
    Replies: 1
    Last Post: 05-21-2015, 12:14 PM
  4. Indexes in a Table
    By FB93 in forum Access
    Replies: 2
    Last Post: 03-18-2014, 07:27 PM
  5. Table Indexes
    By AnthonyT in forum Access
    Replies: 3
    Last Post: 06-04-2009, 06:16 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums