Would Subs be better

[Guest]

I have created a table for Incident Reporting.
In this table I've created numerous fields, Control #, Incident Type,
Individual, Secity Officer, Date, Time, Location, Victim, V-Phone, V-DOB,
V-Race, V-Sex, V-Work Address, V-Home Address, Suspect, S-Phone, S-DOB,
S-Race, S-Sex, S-Work Address, S-Home Address, Description, Last Know local
of Travel, Law Agency, Officer, Case Report, Action Taken, Property
Description, Property Value, Vehicle Owner 1 & 2, Vehicle Make 1 & 2, Vehicle
Model 1 & 2, Year 1 & 2, Color 1 & 2, License 1 & 2, State 1 & 2, Narrative,
and attachments.
A total of 45 Columns. Lots of Info.
My question will this slow the function of the data base or should I look at
creating subs? Will it be easier to graph satistics using subs or should I
use one table? I am new to access this is my first attempt I'm learning as I
go. I would also like to know how to hide my tables to users, I tried to but
when I do the form will not let them access to make updates

 

[Guest]

Hi Lourie,

A total of 45 Columns. Lots of Info.

Yep. As you likely suspect, this is not a good design. It will result in
lots of repeated data for frequent "customers" of your police department (ie.
suspects who commit lots of crimes). It can result in repeated data for
people who are victims more than one time.

A table should represent a single subject. The fields in the table are
attributes that describe the subject. A suspect is not an attribute of a
victim. Neither is a car make, model or licence number an attribute of a
victim.

My suggestion is to learn database design first, if you've never been
exposed to this topic. One of the classic mistakes that beginners make is
poor database design, which then can make the rest of what they want to do
extremely difficult. The time that you spend learning database design will be
useful no matter which relational database management system you choose to
use later on (Access, SQL Server, Oracle, My SQL, etc.). This can be a very
dry (boring) topic, but it is very important. I always give an analogy that
the design of a database is like the foundation for a building; if the
foundation is weak, the building (database) will crumble under it's own
weight. That said, avoid using the table wizard to create any tables in
Access. This is euphemistically known as the denormalization wizard. You'll
want to create all of your tables in design view. Spend the time to create
your database designs using paper and pencil, before you ever open the
software, because it's a lot easier to change a design with an erasure versus
changing it in the software. Here are some links to database design papers to
get you going:

http://www.datatexcg.com/Downloads/DatabaseDesignTips1997.pdf

http://www.datadynamicsnw.com/accesssig/downloads.htm
(See the last download titled "Understanding Normalization")

http://support.microsoft.com/?id=234208

http://support.microsoft.com/?id=289533

Also recommended: Find the copy of Northwind.mdb that is probably already
installed on your hard drive. Study the relationships between the various
tables (Tools > Relationships...)

I also recommend spending some time learning naming conventions and reserved
words. You will avoid common problems that others run into on a frequent
basis by using a good naming convention.

Naming Conventions
Special characters that you must avoid when you work with Access databases
http://support.microsoft.com/?id=826763

Commonly used naming conventions
http://www.mvps.org/access/general/gen0012.htm
http://www.xoc.net/standards/default.asp

Using a Naming Convention
http://msdn.microsoft.com/library/d...us/odeopg/html/deconusingnamingconvention.asp

Reserved Words in Microsoft Access
http://support.microsoft.com/?id=286335

List of reserved words in Jet 4.0
http://support.microsoft.com/?id=321266



Good Luck!
Tom

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

:

I have created a table for Incident Reporting.
In this table I've created numerous fields, Control #, Incident Type,
Individual, Secity Officer, Date, Time, Location, Victim, V-Phone, V-DOB,
V-Race, V-Sex, V-Work Address, V-Home Address, Suspect, S-Phone, S-DOB,
S-Race, S-Sex, S-Work Address, S-Home Address, Description, Last Know local
of Travel, Law Agency, Officer, Case Report, Action Taken, Property
Description, Property Value, Vehicle Owner 1 & 2, Vehicle Make 1 & 2, Vehicle
Model 1 & 2, Year 1 & 2, Color 1 & 2, License 1 & 2, State 1 & 2, Narrative,
and attachments.
A total of 45 Columns. Lots of Info.
My question will this slow the function of the data base or should I look at
creating subs? Will it be easier to graph satistics using subs or should I
use one table? I am new to access this is my first attempt I'm learning as I
go. I would also like to know how to hide my tables to users, I tried to but
when I do the form will not let them access to make updates

 

[Guest]

Using a form is a good alternative to accessing the data directly, but if
users have the full version of Access (as opposed to only the runtime), they
will be able to access the table directly via the database window. Some
things you might consider would be:

1) give users an MDE file to work with, rather than an MDB,
2) customize the startup options so that users can't access the DB window, and
3) open the database in run-time mode (use the /runtime command at the end
of the 'Target' box in a shortcut)

As for why your form was not allowing users to update, a few questions:

1) was your form based on a query or a table? If it was based on a query,
make sure the query is updatable (Access has certain criteria it uses to
determine whether or not a query is updatable).

2) Was the RecordsetType a Snapshot or a Dynaset (it should be Dynaset if
you want to update).

3) Was the form in Data Entry mode, and was Allow Edits set to Yes?

In order to check these things, open your form in design view, right-click
on the box in the upper-left corner of the form, and go to Properties.
Select the tab that says "All" and check to make sure the settings are
correct.

-Chris

:

I would also like to know how to hide my tables to users, I tried to but

 

[Guest]

Thank you for your information it has been very useful. I have checked out
the sites you gave me and they have helped a lot

 

[Guest]

Thanks, You were a big help...
--
LLB

Using a form is a good alternative to accessing the data directly, but if
users have the full version of Access (as opposed to only the runtime), they
will be able to access the table directly via the database window. Some
things you might consider would be:

1) give users an MDE file to work with, rather than an MDB,
2) customize the startup options so that users can't access the DB window, and
3) open the database in run-time mode (use the /runtime command at the end
of the 'Target' box in a shortcut)

As for why your form was not allowing users to update, a few questions:

1) was your form based on a query or a table? If it was based on a query,
make sure the query is updatable (Access has certain criteria it uses to
determine whether or not a query is updatable).

2) Was the RecordsetType a Snapshot or a Dynaset (it should be Dynaset if
you want to update).

3) Was the form in Data Entry mode, and was Allow Edits set to Yes?

In order to check these things, open your form in design view, right-click
on the box in the upper-left corner of the form, and go to Properties.
Select the tab that says "All" and check to make sure the settings are
correct.

-Chris

:

I would also like to know how to hide my tables to users, I tried to but