Large Database Structure Question

[PrincessApril]

Hi all,

I'm having a bit of trouble envisioning my structural setup and want to make sure I'm headed in the right direction.

OBJECTIVE
I am turning a large paper form (3 main sections, 25 subsections in total) into one electronic form with, ideally, one long record source for each form fill.

CONTEXT
Each record represents a caseworker-client assessment. The caseworker goes through Section I (the first 15 subsections), and if the client meets certain criteria, they complete Section II (next 9 subsections) and Section III (subsection 25). Clients are reassessed periodically, so clients will have multiple records over time.

TABLES


I have 25 tables, one for each subsection.

IDs
The first table (which represents Section I, Subsection 1 of the paper form) contains a unique autonumber primary key. The remaining 25 tables have a number ID field marked as the primary key (I guess this is what people mean when they say foreign key--I plan to autopopulate these 24 ID fields with the autogenerated unique ID from table 1).

TABLE RELATIONSHIPS
I wanted to just make 24 one-to-one relationships all stemming from (SS) 1 (SS1 to SS2, SS1 to SS3, ... SS1 to SS25), but Access stopped that plan somewhere in the middle, saying it was too many relationships.
I then tried going one-to-one from SS1 to SS2, from SS2 to SS3, and so forth, but Access did not allow that either.
So now I have gone SS1 to SS2, SS1 to SS3, etc. for all the Section I tables (the first 15). Then I go one-to-one from SS1 to SS16 (the first table in Section II) to ensure that Sections I and II are linked. Then I go SS16 to SS17, SS16, to SS18, etc. to 24 (so all the tables in Section II are linked to one another). Then finally I go SS1 to SS25 (the only table in Section III). It feels like there should be a better way, but it's what I've come up with so far.

FORM SETUP
I have started to make a form with a tab control with 25 page sheets (blank right now except for the tab control).

QUESTIONS
Will my table relationships work with my objective to have a single record source for each caseworker-client interaction with 15-25 tables of data?
Will my form setup achieve this objective?
Should I be considering another approach in my setup?

Thank you in advance for any ideas you have. I want to get this right as early as I can to avoid pain down the road!

[PrincessApril]

So actually this approach fails for at least one reason. I started adding fields to each section, and on the 10th page sheet, I got an error "too many fields defined." This seems like about the same place that the one-to-one table relationships failed, so I must be hitting some wall in Access. I'm aware of the 255 fields limit, and I'm wondering if that's what it is (I didn't count the number of fields in those first 10 control-source tables, but it's possible).

So now back to the drawing board. It's looking like I might have to create separate forms. I guess I could make 25 forms and create some navigation controls to go through them. I'd have to make sure the same records are linked, but am I going to run into issues with the table relationships I have set up?

[GinaWhipp]

Hmm, well more than one issue...
1. You need one main table that holds the Contract Primary Key. All other tables should be linked via a Foreign Key with their own Primary Key.
2. You will not be using one query, form or report. This will require main form\subform and main report\subreport with their record sources.
4. Let's get rid of all those spaces in the field names, they will require bracketing down the road and believe me you'll want to avoid the extra typing. Use CamelCase instead.

[PrincessApril]

Hmm, well more than one issue...
1. You need one main table that holds the Contract Primary Key. All other tables should be linked via a Foreign Key with their own Primary Key.

Thanks for your reply Gina.

I can see creating a main table with the randomly generated unique autonumber, which would be the primary key of that main table and the foreign key (autopopulated I hope) of all other tables. That still leaves me with my main question about the structure.

2. You will not be using one query, form or report. This will require main form\subform and main report\subreport with their record sources.

I considered subforms but read that circumventing the field limit with subforms can lead to significant performance issues. I'm not sure if that's true, but I had discarded it before posting above. What do you suggest? (Keeping in mind that this is indeed one long form on paper--in the database, I'm not saying it has to be one form, but the same record needs to be linked across all tables and forms). I'm not quite sure how to handle linking the forms if, for instance, I have 25 of them. I can envision autopopulating the foreign key with the unique ID but not sure how that would work.

4. Let's get rid of all those spaces in the field names, they will require bracketing down the road and believe me you'll want to avoid the extra typing. Use CamelCase instead.

Hmm I don't see any spaces, unless you mean that underscores count as spaces? I thought the underscore solved the issue of spaces...

[GinaWhipp]

1. You have all you tables linked on the one PK which is a PK in every table when it should not be. It's a Foreign Key (FK). You join PK to FK, not PK to PK. So those tables need to be fixed. See...
https://www.access-diva.com/dm15.html

Notice the PK to FK relationships.

Once you set it up the Foreign Key will update provided the Form\Subform are linked properly. Oh, and you have your Relationships set up.

2. Not sure where you read that as I have a few Forms that have the limit of subforms with no performance degradation. In your case one Form will not handle this and you will not be able to make one query to show all the fields in question.

3. Image kind of small and cannot see underscores. You're good with those. I never use those either as another character to type and when you have been doing this for a while you eliminate key strokes where you can.

[PrincessApril]

Thank you Gina. Okay I am working on fixing the PK/FK issue.

I now have a master table which is just the client's first and last name, DOB, SSN and a couple other pieces of info. This table has a unique autonumber that will be a Client ID (PK in the master table).

Each of my remaining tables will have two ID fields (a PK autonumber field like in your recipes tables) and a Client ID field, which will receive the one-to-many relationships from the master table's PK. So I will have 25 one-to-many relationships from the client ID (PK) in the master table going out to the FK in each of the 25 other tables.

Assuming that is what you had in mind, my question is, if I place the 25 tables as subforms into a main form, will that setup ensure that the same record goes across all 25 subforms for each assessment encounter?

[PrincessApril]

Also, I think I will still run into the same problem with too many relationships when I try to relate my main PK id to my 25 FK IDs?

[CJ_London]

You do have a number of issues

your main table - can include the address data unless they have multiple addresses. The same goes for any other fields which are specific to the client like social security number.

you are using multivalue fields which may cause you problems down the line, depends on how you are going to actually use them

you have tables which appear to have values as separate fields - as in your transport, health, tobacco tables to name but a few. These look like they should have a many to many relationship to the main table. Cant see the detail but something like this

tblClients
ClientPK
ClientName
etc


tblHealth
HealthPK
HealthCondition

tblClientHealth
ClientHealthPK
ClientFK
HealthFK

[PrincessApril]

You do have a number of issues

your main table - can include the address data unless they have multiple addresses. The same goes for any other fields which are specific to the client like social security number.

you are using multivalue fields which may cause you problems down the line, depends on how you are going to actually use them

you have tables which appear to have values as separate fields - as in your transport, health, tobacco tables to name but a few. These look like they should have a many to many relationship to the main table. Cant see the detail but something like this

tblClients
ClientPK
ClientName
etc


tblHealth
HealthPK
HealthCondition

tblClientHealth
ClientHealthPK
ClientFK
HealthFK

Thanks Ajax. So I have to keep the main table limited because our clients tend to move. Actually, even their names change sometimes (I have three clients undergoing a name change now, for different reasons). Although I don't think I really need a longitudinal record of that information anyway.

My main reason for this database is to export the interaction to a Word file as mentioned in my previous post, but the record keeping is a bonus.

I'm curious, how would I avoid using a listbox for an item that has multiple possible responses? The only thing I can think of at the moment is using a separate yes/no field for each response (Race-Black, Race-White, Race-Asian, etc.). Is that the solution you recommend? The problem I see with that is that it makes the mail-merge bookmarking more complex. Right now I'm using Allen Browne's Concatenate function, which I can call in a single line of code to send the value from a multivalue field into the Word document. In the multiple-fields approach, I suppose I'd have to make 2-n bookmarks for every case. Perhaps that is the lesser of two evils though, as in that case I'd have the better data to use moving forward.

[PrincessApril]

Also I'm not sure what is meant by the separate fields except maybe you are referring to the "other" responses. So if someone says they identify as, say, another Asian subgroup than we have listed on the form, there is a blank where they can write in the other value. So in the multivalue list they can choose other, and then the next field, a text field, they can specify if needed. Those text fields are labeled differently than the main listbox.

[CJ_London]

So I have to keep the main table limited because our clients tend to move. Actually, even their names change sometimes

in that case you either change the name or address, but if you need to keep a history you would have additional tables something like

tblClients
ClientPK
etc (you could store the latest name here or reference tblNames for the latest name)

tblNames
NamePK
ClientFK
ClientName
DateFrom

tblAddresses
AddressPK
ClientFK
ClientAddress
DateFrom

how would I avoid using a listbox for an item that has multiple possible responses?

depends if the choices are mutually exclusive or not - can someone be black and white? or Asian and European? If mutually exclusive, use a normal combo or listbox on the form, not in your table. Personally I avoid multivalue combos (which is what you have in health insurance for example), but might be relevant in your mental health table where someone could be both paranoid and schizophrenic. But the way I would do it is the same way I illustrated in my previous post.

Your approach seems to be very 'excel' in your thinking. Databases are a completely different animal. It might be worth you googling 'normalisation' to gain an understanding of how data is stored in databases - it is nothing like excel!

[CJ_London]

Also I'm not sure what is meant by the separate fields except maybe you are referring to the "other" responses. So if someone says they identify as, say, another Asian subgroup than we have listed on the form, there is a blank where they can write in the other value. So in the multivalue list they can choose other, and then the next field, a text field, they can specify if needed. Those text fields are labeled differently than the main listbox.

I don't know what your data looks like, so difficult to comment - but on my basis if someone selects 'other', you would have some code on your form that requires the user to complete your text box

[GinaWhipp]

Hmm, looks like Ajax is getting you fixed up quite nicely. I'll just hang on the sidelines till a new Relationships image is posted.

Just a little side, don't get tangled up in the details yet. Let's get the basic table put together and then expand from there. The *weeds* start to get confusing when too many of them pop-up.

[PrincessApril]

in that case you either change the name or address, but if you need to keep a history you would have additional tables something like

tblClients
ClientPK
etc (you could store the latest name here or reference tblNames for the latest name)

tblNames
NamePK
ClientFK
ClientName
DateFrom

tblAddresses
AddressPK
ClientFK
ClientAddress
DateFrom


depends if the choices are mutually exclusive or not - can someone be black and white? or Asian and European? If mutually exclusive, use a normal combo or listbox on the form, not in your table. Personally I avoid multivalue combos (which is what you have in health insurance for example), but might be relevant in your mental health table where someone could be both paranoid and schizophrenic. But the way I would do it is the same way I illustrated in my previous post.

I'd say most of the multivalue list boxes (I have no combo boxes in my tables) allow multiple values, but some do not. But if I see your point about just creating the list boxes on the form and not within the table. If we take an example of an item that allows multiple responses, such as race, in the table, would you recommend using yes/no items for each of those responses? I looked at your response above but that seems more about the PKs and FKs (I have since updated my tables to follow that structure).

Your approach seems to be very 'excel' in your thinking. Databases are a completely different animal. It might be worth you googling 'normalisation' to gain an understanding of how data is stored in databases - it is nothing like excel!

I started reading about this. I don't view my tables as having duplicate data, but maybe my understanding of duplicate data is incorrect. For instance, the table on tobacco use--it's hard to imagine breaking that into a smaller component. I do have a few tables, such as the demographics table, where I could break down into smaller tables (e.g., an entire table just on ethnicity, a table just on gender, etc.). Perhaps that is one of the limitations in my perspective--I was grouping items based on their thematic divisions on our paper form. So, essentially, is the idea to break the tables into their smallest possible (related) parts?

[PrincessApril]

Okay I am starting with a completely fresh DB. Here are my first few tables. Am I on the right track? (in particular, not sure if I should be using yes/no in the child tables and if I should be specifying anything in the properties at the bottom of the screen that would be helpful). Also not sure about the join type...