Prev: Don't understand the relationship between a combo box and a table
Next: Missing reference
From: forest8 on 30 Mar 2010 23:41
Currently it's one field per issue.



"John W. Vinson" wrote:

> On Tue, 30 Mar 2010 19:32:01 -0700, forest8
> <forest8(a)discussions.microsoft.com> wrote:
>
> >In previous posts, I was asking for assistance in creating a Case Management
> >Datatabase.
> >
> >In this database, there are 5 categories of investigation:
> >
> >School, Community, Individual, Peers, and Family.
> >
> >In the School category, there can be anywhere from one to seven actions a
> >youth can take depending on how he has answered previous questions.
> >
> >For instance, if the youth answered that he has no role models, then the
> >reaction is to help the youth obtain positive role models, positive support,
> >and/or Empowerment but not necessary all can/would be chosen.
> >
> >All this information is hopefully placed in a form in which at each phase of
> >their involvement in the program, all their youth's responses can be reviewed.
> >
> >If I split up my form into the categories by phase, this can mean an
> >additional 25 tables.
> >
> >This would bring my total tables to almost 100.
> >
> >I can't create any more relationships since I am at the limit.
>
> How about:
>
> Students
> StudentID <primary key>
> LastName
> FirstName
> <other biographical info as appropriate>
> SchoolID <where is this student enrolled>
>
> Cases
> CaseNo <primary key>
> StudentID <link to Students, who is being investigated>
> <other fields relevant to the case as a whole>
>
> Categories
> CatgoryID <primary key>
> Category (e.g. School, Community, ...)
>
> Issues
> IssueID <primary key>
> Description <text, e.g. "lack of role models">
> CategoryID <in which category is this issue>
>
> StudentIssues
> CaseNo <link to Cases and thence to Students>
> IssueID <link to Issues, which issue did this student raise>
>
> Similar tables for Responses, not sure how you want them linked.
>
> Note that NONE of these tables need more than two or three indexes.
>
> How are your tables structured? One field per issue perhaps, or one field per
> response? That may be the source of your problems!
> --
>
> John W. Vinson [MVP]
> .
>
From: Richard on 31 Mar 2010 00:30
"forest8" wrote:

> What do you mean by "simple, properly normalized tables"?

>"John W. Vinson" wrote:

How about:

Students
StudentID <primary key>
LastName
FirstName
<other biographical info as appropriate>
SchoolID <where is this student enrolled>

Cases
CaseNo <primary key>
StudentID <link to Students, who is being investigated>
<other fields relevant to the case as a whole>

Categories
CatgoryID <primary key>
Category (e.g. School, Community, ...)

Issues
IssueID <primary key>
Description <text, e.g. "lack of role models">
CategoryID <in which category is this issue>

StudentIssues
CaseNo <link to Cases and thence to Students>
IssueID <link to Issues, which issue did this student raise>


This is the primer for a normalized table, take the MVP's advice. If your
table aren't right nothing else matters.

Richard
From: forest8 on 31 Mar 2010 00:38
Thank for the advice. I'm taking your suggestions into my database.

If I run into any issues, I'll start a new thread.

Thanks again.

"John W. Vinson" wrote:

> On Tue, 30 Mar 2010 15:54:02 -0700, forest8
> <forest8(a)discussions.microsoft.com> wrote:
>
> >Hi there
> >
> >Currently I am getting the following message:
> >
> >"The operation failed. There are too many indexes on table 'Orders'. Delete
> >some of the indexes on the table and try the operation again."
> >
> >In my table, I am trying to change a text box into a multi=select combo box.
> >
> >There are 45 fields in my table of which 40 have this multi-select combo box.
> >
> >I don't understand where these indexes are being created.
> >
> >I do have 1 primary key in my table.
> >
> >Thank you
>
> My guess is that these combo boxes have indexes of their own which contribute
> toward the form's index count.
>
> I would *VERY STRONGLY* suggest that putting combo boxes into a table -
> particularly multiselect combo boxes - is a major misuse of Access, and that
> it is unnecessary!
>
> Table datasheets are becoming more complex with recent releases of Access, but
> they're still *very limited*. There is nothing that you can do with a
> complicated table that you cannot do with simple, properly normalized tables,
> and a well designed Form. You do NOT need multiselect combos, or any combos at
> all, in your Table in order to do so.
> --
>
> John W. Vinson [MVP]
> .
>
From: John W. Vinson on 31 Mar 2010 01:22
On Tue, 30 Mar 2010 20:41:02 -0700, forest8
<forest8(a)discussions.microsoft.com> wrote:

>Currently it's one field per issue.
>

So... when you add a new issue you need to redesign your table, change all
your queries, restructure all your forms, all your reports?

Sorry, but that design is *just plain wrong*, and is the source of your
difficulties with indexing and with form design. You're using a relational
database - use it relationally! Tables should be tall and thin, not wide and
flat; data should be stored in fields, not in fieldnames.

--

John W. Vinson [MVP]
From: John W. Vinson on 31 Mar 2010 01:22
On Tue, 30 Mar 2010 19:52:01 -0700, forest8
<forest8(a)discussions.microsoft.com> wrote:

>What do you mean by "simple, properly normalized tables"?
>

What I said elsewhere in the thread:

How about:

Students
StudentID <primary key>
LastName
FirstName
<other biographical info as appropriate>
SchoolID <where is this student enrolled>

Cases
CaseNo <primary key>
StudentID <link to Students, who is being investigated>
<other fields relevant to the case as a whole>

Categories
CatgoryID <primary key>
Category (e.g. School, Community, ...)

Issues
IssueID <primary key>
Description <text, e.g. "lack of role models">
CategoryID <in which category is this issue>

StudentIssues
CaseNo <link to Cases and thence to Students>
IssueID <link to Issues, which issue did this student raise>

Similar tables for Responses, not sure how you want them linked.

--

John W. Vinson [MVP]
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: Don't understand the relationship between a combo box and a table
Next: Missing reference