Access Help: Primary key and SQL statement

[Guest]

Hi,

I am creating a database that includes classroom information, such as:
grades, attendance, etc. I have one table that is called Student information
and the primary key is the Social Security Number. The rest of the ten
tables don't have a primary key because I was obtaining a duplicate error
when I was entering it into the subform. In addition, all my tables have
outer joins and I come up with an error when I create a query. It says: "the
SQL statement could not be executed because it contains an ambiguous outer
joins. To force one of the outer joins to be performed first, create a
separate query that performs the first join and then include that query in
your SQL statement." I am not sure how to create the SQL statement.

I can also send the database to you by email, if you want to look at it.
Thank you

Stephen

 

[Guest]

Hi Stephen,

A couple of points:
(1) Social Security Numbers make for bad primary keys. There's some laws
about using them; you might be held responsible if someone swipes them from
the database and it causes identify theft; and some people don't even have
one. Actually about 6 billion people don't have a SSAN.

(2) If you can not create a primary key in your other tables, you either
have (a) duplicate data that needs to be cleaned and/or (b) your structure
isn't normalized enough. If you clean up the data and structure where you can
use inner joins, the problem will go away.

 

[Roger Carlson]

First of all, I second Jerry's advice about the SSN. Not a good choice.

Second, the fact that you were getting duplicate errors just means you are
having a data integrity problem. By getting rid of the primary key, you
have not solved the problem, you've made it worse. You won't be able to fix
it until you go through and manually get rid of the duplicates and find out
why you were getting them in the first place.

Third, about Ambiguous Outer Joins: On my website
(www.rogersaccesslibrary.com) I have a document that explains what they are
and how to solve them. Go here and download:
http://www.rogersaccesslibrary.com/AmbiguousOuterJoins.zip

Note: It's just a document. No viruses or such.

--
--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]

Hi,

I am creating a database that includes classroom information, such as:
grades, attendance, etc. I have one table that is called Student information
and the primary key is the Social Security Number. The rest of the ten
tables don't have a primary key because I was obtaining a duplicate error
when I was entering it into the subform. In addition, all my tables have
outer joins and I come up with an error when I create a query. It says: "the
SQL statement could not be executed because it contains an ambiguous outer
joins. To force one of the outer joins to be performed first, create a
separate query that performs the first join and then include that query in
your SQL statement." I am not sure how to create the SQL statement.

As Jerry says, the SSN is a dubious choice. If you still want to use
it, note that it must NOT be the "Primary Key" of the related tables.
A Primary Key uniquely identifies one record in its table; each
Student will be uniquely identified by a SSN, but student will have
many attendance records. The Attendance table should have ITS OWN
primary key; it will contain the SSN as a "foreign key", a link to the
student table, but it should not be the Primary Key.

Secondly, if you're trying to create One Grand Master Query and do all
your editing in that query datasheet, you're on the wrong track.
Instead, use a Form for the "one" side data (Students perhaps), with a
Subform for each "many" side table. You'll probably want several forms
for different aspects of the application; you don't *need* to handle
absolutely everything simultaneously on the same screen. Several Forms
(e.g. for entering student contact information; setting up classes;
entering enrollment information; entering grades), each with a
mainform and zero, one or more subforms, will be better. You can have
a Switchboard to choose which form to open.

You may want to look at some of the resources at Jeff Conrad's website
http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html,
especially the Database Design 101 links.

I can also send the database to you by email, if you want to look at it.
Thank you

I can also send you my resume with consulting terms. Private EMail
support is for paying customers; free support is provided here in the
newsgroups.

John W. Vinson[MVP]