Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / New Users / October 2005

Tip: Looking for answers? Try searching our database.

Bloated Program

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Randy - 03 Oct 2005 15:04 GMT
I have a program that basically has about 8 fields that are just client
identification information, but have about 200 Yes/No fields.  This
program to me is fairly bloated compared to others I have created.  I
have compressed the program.

Is there anything I can do to the Yes/No fields that will reduce the
size of the program?
Rick B - 03 Oct 2005 15:56 GMT
Yes, normalize your database.

If you have 200 attributes that can be applied to a client, then you should
create a related one-to-many table or a many-to-many relationship and enter
only the attributes that apply to each client.  In the following scenario,
you'd enter the 200 possible attributes in the "TblPossibleEntries" table.
Then you'd use them in the TblAttributes to link each client to the
attributes that fit them.

TblMain
ClientNumber
ClientName
ClientAddress1
.
.
.

TblAttributes
ClientNumber
CleintAttribute

TblPossibleEntries
AvailableAttributes

Signature

Rick B

> I have a program that basically has about 8 fields that are just client
> identification information, but have about 200 Yes/No fields.  This
[quoted text clipped - 3 lines]
> Is there anything I can do to the Yes/No fields that will reduce the
> size of the program?
Randy - 03 Oct 2005 23:29 GMT
Rick

Are you saying I would have to set up a table for each of the 200 check
boxes?  Any or all of the check boxes could apply to a client.
Douglas J. Steele - 03 Oct 2005 23:59 GMT
You should have a table that stores the Client ID and the Category ID. If
categories 2, 4 and 9 apply to client 123, you'd have 3 rows in that table:

ClientId  CategoryId
123          2
123          4
123          9

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Rick
>
> Are you saying I would have to set up a table for each of the 200 check
> boxes?  Any or all of the check boxes could apply to a client.
Vincent Johns - 06 Oct 2005 17:22 GMT
> You should have a table that stores the Client ID and the Category ID. If
> categories 2, 4 and 9 apply to client 123, you'd have 3 rows in that table:
[quoted text clipped - 3 lines]
> 123          4
> 123          9

This is an especially good solution if you typically have only a few
boxes checked (= [CategoryId] values) for each of your clients.  If most
of the boxes are usually checked, however, you'll wind up with lots of
records in this Table.

You could define your [CategoryId] values, for boxes that are usually
checked, to be true if and only if the box is NOT checked.  Then you'd
store records only for the less frequent cases and keep the size of your
Table down that way.

Or you might use a combination -- check boxes in the main record for
some properties, [CategoryId] values in linked records in a separate
Table for other properties.

In any case, I suggest that you CLEARLY    identify in comments or
elsewhere, for anyone else who later has to maintain the database, or
for yourself six months from now, EXACTLY what each checkbox or category
value means.  Otherwise, you'll have endless trouble and be sorry you
ever embarked on this project.

  -- Vincent Johns <vjohns@alumni.caltech.edu>
  Please feel free to quote anything I say here.
Vincent Johns - 04 Oct 2005 00:15 GMT
> Rick
>
> Are you saying I would have to set up a table for each of the 200 check
> boxes?  Any or all of the check boxes could apply to a client.

No, I think he's saying that you can group them.  Are they totally
random, or is a "Yes" in box 50 often associated with "Yes" in box 69?
If they're related, they could share a record in a Table, and other
unrelated boxes could be in another Table.  The record would contain, in
addition to some of the check boxes, a key linking it to the client's
information in the main Table.

Doing all this will actually *increase* the size of the database unless
you can arrange it so that some of these Tables will frequently contain
records that are entirely blank and can thus be omitted.  Otherwise, I
think you might just as well keep everything in one record; Access
doesn't care unless the number of fields reaches 255.

  -- Vincent Johns <vjohns@alumni.caltech.edu>
  Please feel free to quote anything I say here.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.