Access

C

Chalres

Could I get some help from our Access gurus? Below are examples of students
with more than 1 major. How could I make Major2 a “null†since it’s the same
as Major1 (1st row) and make Major3 a “null†(2nd row)?



STUD_ID Major1 Major2 Major3 Major4
1001 SPAN SPAN ENG CHEM
1002 ENG SPAN SPAN CHEM


Thank you.
 
B

Beetle

I think you need to determine the cause of the problem instead of trying to
work around it. Why is it that Major2 is getting a value of "SPAN" if Major1
already has that value? Is it your users or your DB design that is causing
the problem? Once the source of the problem is determined, then someone here
will be able to help you come up with a proper solution.
 
T

Tom Wickerath

Hi Charles,

This design may be appropriate for a spreadsheet, but it is not a correctly
normalized design. You really need to change the design, to eliminate the
multivalued fields. Here is a link with several good database design papers.
Make sure to read the paper by Michael Hernandez:

http://www.accessmvp.com/JConrad/accessjunkie/resources.html#DatabaseDesign101


You can use an append query that uses a union query as it's source, when you
are ready to re-arrange your data into a normalized format. Here is a
tutorial on using union queries:

http://home.comcast.net/~tutorme2/samples/unionqueries.zip


Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________
 
D

Douglas J. Steele

Is that the way your table is actually structured, with fields named Major1,
Major2, Major3 and Major4? That's called a repeating group, and it's a bad
idea.

For one thing, it essentially hides data: the name of the field hides a
piece of information.

For another things, what happens if some hotshot ends up taking 5 majors?

You should be using two tables, one with STUD_ID as its primary key,
containing the information about the student, and a second with a primary
key of STUD_ID and MAJOR_ID that would look like:

STUD_ID MAJOR_ID MAJOR_DS
1001 1 SPAN
1001 2 ENG
1001 3 CHEM
1002 1 ENG
1002 2 SPAN
1002 3 CHEM

You could also create a unique index (as opposed to a primary key) on
STUD_ID and MAJOR_DS, thus preventing duplication.

The usual way to maintain this information would be through a form/subform
setup.
 
Top