Advice required on handling large quantity of records

[Landywednak]

I have an access 2003 dbase that needs to store in excess of 70-80 records
for the purpose of reporting to a government agency. Client Status and
Demographic records, Carer Status and demographic records, disability records
and service usage (the latter is variable) the majority of record are chosen
from combo box lists. Currently the dbase is broken into a variety of tables
for the above + the lists.
this means when a client is entere and provided with an ID, the ID must also
be entered in when filling in the carer info, again for tha disability info
etc. if I bundle the whole lot into 1 table this is too big for a single
form. I wish to avoid typing a client ID for every section... Can I (a)
Collect info in 1 table and break records into sub-forms or other type of TAB
section perhaps? or is there a procedure that can call the

 

[Roger Carlson]

Without knowing your existing table layout, any meaningful answer is
difficult.

However, in general, you should have a separate table for each type or group
of data. For instance, there should be a Client table that has fields that
pertain only to the person of the client (possibly your demographics can go
in this table). Next, for all data that can have multiple values for any
client, this data should be grouped into logical grouping, each in its own
table. For interest, there would probably be a Career table and a
Disability table and a ServiceUsage table. Each record in these tables
would store the ClientID from the Client table in order to allow the tables
to be joined.

You don't need to type in the ClientID into each of these tables, because
the Subform Control will do this for you automatically as long as you have
your table relationships set up properly.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John Vinson]

I have an access 2003 dbase that needs to store in excess of 70-80 records

I presume you mean 70-80,000? Even that is a very modest database for
Access. The largest tables I'm aware of in production use in Access
are somewhere over 20,000,000 records. A few tens of thousands is no
problem at all.

for the purpose of reporting to a government agency. Client Status and
Demographic records, Carer Status and demographic records, disability records
and service usage (the latter is variable) the majority of record are chosen
from combo box lists. Currently the dbase is broken into a variety of tables
for the above + the lists.

If you've properly normalized the data, with one-to-many
relationships, this should be straightforward. You don't say what the
"variety of tables" are so we obviously cannot comment on whether the
design is perfect as it is, or subject to improvement.

this means when a client is entere and provided with an ID, the ID must also
be entered in when filling in the carer info, again for tha disability info
etc. if I bundle the whole lot into 1 table this is too big for a single
form. I wish to avoid typing a client ID for every section... Can I (a)
Collect info in 1 table and break records into sub-forms or other type of TAB
section perhaps? or is there a procedure that can call the

Don't use Tables for data entry, period; they're best kept in the
background, opened only for debugging. Also don't try to base your
form on one monster query. Use a mainform based on the "one" side
table (the Client table presumably); you can put multiple Subforms on
this Form, each subform based on another related table (e.g. a Subform
for the disability records). The ClientID will automatically link the
mainform to the subform, filling it in when you add new records; for
screen real-estate issues, you can use a Tab Control on the form, with
a separate tab page for each subform.


John W. Vinson[MVP]
John W. Vinson[MVP]

 

[Roger Carlson]

Actually, I assumed he meant 70-80 FIELDS instead of records. Which, of
course, shows the futility of assumptions.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[John Vinson]

Actually, I assumed he meant 70-80 FIELDS instead of records. Which, of
course, shows the futility of assumptions.

Ah! The OP did say records, but fields is in fact much more plausible.
Hopefully they'll post back and we can help resolve the issue.

John W. Vinson[MVP]

 

[Landywednak]

Yes late night fo-par I did meen fields and actually it is more like 100+

Yes I know about correct normalization procedures and all fields contained
within the DB are already separated into logical groups/Tables/Queries & kind.
I think The sub-forms are what I am after:?...
Basic Structure...
Client Personal Details (reportable)ClientID(Primary Key) 0000 Auto Number
Client Circumstances (reportable)...ClientID
Client Disability/Health Issues (reported to separate agency)...ClientID
Carer Personal Details (reportable)CarerID...ClientID
Service Epsiodes (reportable)ClientID...CarerID (Some services are for Carer
not Client)
Staff Members (Internal use)StaffID
Departments (Internal use)DeptID
Roster(internal use)StaffID,ClientID,DeptID
Funding Source FundingID
Referral Contract Tracking (Internal Use)ContratcID, FundingID
30 or so tables containing list/choice information like Country of Birth,
Main Language Spoken, Living Arrangements,Functional Status etc. Used as
LookUp Fields
44 fields selected from each of the reportable parts are then gathered by
query and exported as txt or CSV then emailed. There are 2 x separate
Government repository databases. 1 is sent quarterly and 1 is sent yearly.
Both reports are different, they do contain the same personal information but
service episodes are different

I am just seeking ways to improve

 

[Landywednak]

Please see what I have posted in reply to Roger Carlson

 

[John Vinson]

Yes late night fo-par I did meen fields and actually it is more like 100+

Ouch. That's a LOT. Not all 100 in one table I hope!?
How many *records* (roughly, not asking for confidential information -
order of magnitude is fine) are you talking about?

Yes I know about correct normalization procedures and all fields contained
within the DB are already separated into logical groups/Tables/Queries & kind.

The separation should be into logical groups, in this sense: each type
of Entity (real-life thing, person, or event) should have its own
Table. That entity's Attributes (discrete, atomic, non-repeating
chunks of information that you need to know about each entity of that
type) should correspond to table fields.

For instance, a Client (a person) is an entity; so is a Service
Episode.

I think The sub-forms are what I am after:?...
Basic Structure...
Client Personal Details (reportable)ClientID(Primary Key) 0000 Auto Number

Client Circumstances (reportable)...ClientID

ClientID should NOT be the Primary Key of this table, unless each
client can have one and only one Circumstances record. In that case,
you might as well just put the circumstances in the Client record! Is
this a nonunique Foreign Key (as would be normal)?

Client Disability/Health Issues (reported to separate agency)...ClientID

Do you have a table listing all the valid Disability/Health issues?
This sounds like a many to many relationship: you'ld have a table with
records like

HealthIssueID <Primary Key>
HealthIssue <e.g. "Legally blind">

and another with

ClientID
HealthIssueID
Comments <or other information about this person/this issue, such as
severity rating>

Carer Personal Details (reportable)CarerID...ClientID

Related one to many to Clients? Will you ever have one carer caring
for multiple clients?

Service Epsiodes (reportable)ClientID...CarerID (Some services are for Carer
not Client)

How is this table related to the Client and the Carer tables?

Staff Members (Internal use)StaffID
Departments (Internal use)DeptID
Roster(internal use)StaffID,ClientID,DeptID
Funding Source FundingID
Referral Contract Tracking (Internal Use)ContratcID, FundingID
30 or so tables containing list/choice information like Country of Birth,
Main Language Spoken, Living Arrangements,Functional Status etc. Used as
LookUp Fields

I'd recommend NOT using table lookup fields. They have one benefit:
they make it slightly easier to put a combo box onto a Form. The cost
for this benefit is a great deal of inefficiency and confusion. See
http://www.mvps.org/access/lookupfields.htm for a critique.

By all means use lookup Tables - *on Forms*, though, not in the table
design.

44 fields selected from each of the reportable parts are then gathered by
query and exported as txt or CSV then emailed. There are 2 x separate
Government repository databases. 1 is sent quarterly and 1 is sent yearly.
Both reports are different, they do contain the same personal information but
service episodes are different

That's fine - that's just what relational databases are designed to
do, select, assemble and report information!

I am just seeking ways to improve

If you're having some specific problems do let us know.

John W. Vinson[MVP]

 

[Landywednak]

Ouch. That's a LOT. Not all 100 in one table I hope!?
How many *records* (roughly, not asking for confidential information -
order of magnitude is fine) are you talking about?

About 18,000 records and growing

The separation should be into logical groups, in this sense: each type
of Entity (real-life thing, person, or event) should have its own
Table. That entity's Attributes (discrete, atomic, non-repeating
chunks of information that you need to know about each entity of that
type) should correspond to table fields.
Done



ClientID should NOT be the Primary Key of this table, unless each
client can have one and only one Circumstances record. In that case,
you might as well just put the circumstances in the Client record! Is
this a nonunique Foreign Key (as would be normal)?

Foreign Keys used in other tables 1 to 1 relationship

Do you have a table listing all the valid Disability/Health issues?...YES!
This sounds like a many to many relationship: you'ld have a table with
records like

HealthIssueID <Primary Key>
HealthIssue <e.g. "Legally blind">

and another with

ClientID
HealthIssueID
Comments <or other information about this person/this issue, such as
severity rating> Yes


Related one to many to Clients? Will you ever have one carer caring
for multiple clients?... NO


How is this table related to the Client and the Carer tables?

EXAMPLE: If it is a respite service then it is the Carer that receives the
service (Becomes the Client). If it is Social Support and all other 22
service types then the care recipient (Client) recieves the services (Funding
related report element)

I'd recommend NOT using table lookup fields. They have one benefit:
they make it slightly easier to put a combo box onto a Form. The cost
for this benefit is a great deal of inefficiency and confusion. See
http://www.mvps.org/access/lookupfields.htm for a critique.

The look up tables contain a reportable code like...Country of Birth
1101=Australia, or 1=person can do X, 2 = Person Can do x Sometimes, 3 person
cannot do X. 9 Not Sted...etc. Only the numeral (code) is reported. This is
entered once only during the initial entry to the system stage of proceedings.

By all means use lookup Tables - *on Forms*, though, not in the table
design.


That's fine - that's just what relational databases are designed to
do, select, assemble and report information!

If you're having some specific problems do let us know.

Thanks very much for assisting me

 

[John Vinson]

Foreign Keys used in other tables 1 to 1 relationship

Again...

One to one relationships are VERY rare.

Is it in fact appropriate that each Client has one, AND ONLY ONE,
EVER, record in these related tables?

Maybe it is; I don't know your service procedures. But it seems odd.

If in fact each of these tables is related one to one, do you have
some reason not to just incorporate the fields all into one table?

John W. Vinson[MVP]

 

[Roger Carlson]

I'm with John on this one. It doesn't really look like Normalized data.
Just separating data into multiple table is not necessarily normalization.

For instance, Client Disability/Health Issues has the ClientID as a primary
key (if I'm reading this right). But that would mean you'd have to have
multiple fields to hold multiple issues (fields like Issue1, Issue2, and so
forth). This is NOT normalized. Instead, you should have the CientID be a
foreign key (long integer) which will allow a One-to-Many relationship
between the tables rather than a One-To-One.

I have similar suspicions of Client Circumstances and Service Epsiodes.
Without the actual fields and business rules, it's impossible to tell
exactly. I'd suggest you get a book called "Database Design For Mere
Mortals" by Michael Hernandez. After that, you might want to check out some
database design tutorials I created for my database classes that are based
on Hernandez's process. You can find them here:
http://www.rogersaccesslibrary.com/Tutorials.html

In all my years, I've never had a data-entry database with that many fields
in a table. The only time I've had that is with a Data Mine where the table
values were pre-calculated from a normalized database. Your immediate
concern is with your form design, but your real problem is with your table
design. You will continue to run into problems until you fix the root
cause.

Sorry for the bad news.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Landywednak]

Yes absolutley to both comments...

1st The clients do not get Autism many times, or go deaf many times, if they
have non verbal communication, or can't walk, or require assistance dressing.
there circumstanses rarely change. They usually have one family or one carer,
one contract per year. one gender, one date of birth etc.

Tried the one table idea that... worked when only one government agancy
required a quarterly report, but second teir (State government) created a
similar (annual) reporting procedure and modified some of the required
information which tipped the scales a bit.

Besides the Govt like to change terminology too often and one large table
became too large to modify regularly

The only variables are the hours of care and they are calculated and then
totalled from the roster and in turn entered into the corresponding fields
just prior to sending the report. There is only 6 categories of care hours
and most clients only use 2-3 in this organisation.

I just thought instead of having a bunch of individual Forms corresponding
to each of the tables I might get away with coverting them into sub forms so
that the coordinator could readly see the various parts without opening and
closing forms, similar to the one table idea.

Sorry if my descriptions mislead you of course I was using foreign keys

Meanwhile I have managed to create a TAB object with 3 TABS each with a
sub-form, Disabilty Information, Carer Information and Services. I have
located these in my client details form, which now provides the equivalent of
4 table worth of information on the one form. testing using data entry seems
to be ok! yet I haven't fully tested it as yet

 

[Roger Carlson]

Yes, but can't a client be autistic, deaf, unable to walk, and require
assistance dressing? These should each be separate *records* in a related
table, and not fields in a single table.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Landywednak]

Table Structure
List=combo box

Main Tables:
tblClient Details: ClientID, Names, DOB, Address details etc, contact
details, gender & kind
tblClient Circumstances: Primary Key, COB,Indigenous status (list), living
arrangements(list), communication methods(List), functional status(list) Govt
Pension (List) & kind
Client ID (Foriegn) CarerID (Foriegn) ,,,"in some cases it is the Carer
that must be reported on (they become the client and some of the
circumstances relate to them in that instance".

tblCarer Details,CarerID (Primary Key) Names, DOB, Address Details, Contact
Details, Gender, Relationship to care recipient(list) ClientID (Foreign) &
kind

tblDisabiliy Details: Primary Key, Prime Disbility (List) Secondary
disabilty x 12 (Yes/No), Other Health Issues (memo), Alergies(memo),
behaviour issues(memo) other remarks(memo)

tblRoster: date, Start Finish, Client(List), Department(List),Program(List),
Activity(List), Staff Member (list),Funding code(List)

Other Tables, 23 Code Lists with between 3 and 20 codes & descriptions as
set out by government agency only one code chosen from each list for each
response

3 or so Tables to provide lists for roster

tblContract/Referral Tracking; Primary Key, ClientID(Foreign), Funding
Source, Funding Type, Duration, Amount & kind

 

[John Vinson]

Yes absolutley to both comments...

1st The clients do not get Autism many times, or go deaf many times, if they
have non verbal communication, or can't walk, or require assistance dressing.
there circumstanses rarely change. They usually have one family or one carer,
one contract per year. one gender, one date of birth etc.

I guess the question I would have: what if the client were both deaf
and autistic (like the child of my neighbors across the street some
years back)? If it's working for you, ok - but it will continue to
work even with a one to many relationship, and *allow* for the
possibility.

...

That you can do, of course; in fact it would seem to be the most
reasonable approach!

Sorry if my descriptions mislead you of course I was using foreign keys

Meanwhile I have managed to create a TAB object with 3 TABS each with a
sub-form, Disabilty Information, Carer Information and Services. I have
located these in my client details form, which now provides the equivalent of
4 table worth of information on the one form. testing using data entry seems
to be ok! yet I haven't fully tested it as yet

You're on the right track I'd say!

John W. Vinson[MVP]

 

[John Vinson]

On Thu, 5 Jan 2006 08:35:03 -0800, "Landywednak"

Comments inline.

Table Structure
List=combo box

Table fields ARE NOT LISTBOXES - or combo boxes.

The Lookup Wizard is generally rather disliked by serious developers.
It creates new relationships between the tables, *even if the
relationship already exists*; adds redundant indexes; conceals the
actual content of your tables; hurts performance...

Its only virtue is that it makes it one mouseclick easier to create a
Listbox or Combo Box on a Form - hardly worth the drawbacks IMO.

Main Tables:
tblClient Details: ClientID, Names, DOB, Address details etc, contact
details, gender & kind
ok...

tblClient Circumstances: Primary Key, COB,Indigenous status (list), living
arrangements(list), communication methods(List), functional status(list) Govt
Pension (List) & kind

"kind"...? I assume that a client will have one and only one pension?

Client ID (Foriegn) CarerID (Foriegn) ,,,"in some cases it is the Carer
that must be reported on (they become the client and some of the
circumstances relate to them in that instance".

and (I think we covered this) one and only one carer?

tblCarer Details,CarerID (Primary Key) Names, DOB, Address Details, Contact
Details, Gender, Relationship to care recipient(list) ClientID (Foreign) &
kind

This may be a problem. You seem to have a CarerID in tblClient, and a
ClientID in tblCarer. You should NOT have both. I'd be inclined to
have a (non-unique) ClientID in tblCarer as a foreign key (on the
assumption that one client may have more than one carer, but that each
carer will be caring for only one client).

tblDisabiliy Details: Primary Key, Prime Disbility (List) Secondary
disabilty x 12 (Yes/No), Other Health Issues (memo), Alergies(memo),
behaviour issues(memo) other remarks(memo)

If you have twelve secondary disability fields - your table structure
is in fact non-normalized. This is a repeating field. It sounds like
you may also be storing multiple Health Issues, Allergies, and
Behavior Issues in memo fields - this is iffy, since it makes it
impossible to control the terms used, and makes data entry more
difficult.

tblRoster: date, Start Finish, Client(List), Department(List),Program(List),
Activity(List), Staff Member (list),Funding code(List)

What's the PK of this table? I presume the Client list is a foreign
key to ClientID?

Other Tables, 23 Code Lists with between 3 and 20 codes & descriptions as
set out by government agency only one code chosen from each list for each
response

sounds good...

3 or so Tables to provide lists for roster

sounds good... these are for Staff Member, Department etc I presume?

tblContract/Referral Tracking; Primary Key, ClientID(Foreign), Funding
Source, Funding Type, Duration, Amount & kind

that sounds fine.

In general looks good (with the few caveats above), and Subforms
should work fine for you.

John W. Vinson[MVP]