Find a row in a table from a form

H

Harper

Hello,
I am making a database where there is a form where the user can select
whether an education grad. student is unlicensed or licensed. Nearing
the end of the program, their status does change (we hope) from
unlicensed to licensed. Also, the student's status may change simply
because the user may have mistakenly assigned the wrong status.

I have table STUDENT, table LICENSED, table UNLICENSED, table
LICENSED_ARCHIVE, table UNLICENSED_ARCHIVE among others. In form
STUDENT_ENTRY, I have a combo box with "unlicensed," "licensed," and
"unknown" and text boxes, one of which is IDNo (their student id
number). The IDNo is the primary key through the tables.

When the user changes the combo box from one type of status to another
(excluding "unknown," for which nothing happens), I would like to have
access 1.) look up the IDNo field, 2.) Find that IDNo in the original
table, the table that corresponds to the status the student is being
switched from 3.) Copy that row into table LICENSED_ARCHIVE or
UNLICENSED_ARCHIVE as the case may be 4.) Delete said row from original
table.

I understand how to do an append query and delete query so I have 3.
and 4. covered. But I am stuck at 1. and 2. I do not know VBA so if
there is some kind of solution I can understand using expressions or
whatnot, I'd be grateful.

Thanks much,
Harper
 
H

Harper

Because there is a lot more to the data than simply where they are
licensed or not. There are exams and licensing numbers attached to
licensure; there are exams attached to an unlicensed student.

I have normalized the database and that is why the data is separated
out.

Do you have a solution to the question I asked?
 
H

Harper

Okay, it's pretty extensive so I posted an image of the relationships
to http://accesshelpplease.blogspot.com/ There's only one post and it
pertains to what we're discussing. If you click on the .gif image it
will open in a new window to a larger size where it is easy to see
everything. Thanks.
 
B

BruceM

Does an exam involve more than one student? If so, the exam information
should be stored once, not in the record of each student who takes the exam.
Further, if each student can take several exams, and each exam is taken by
several students, there are further considerations in setting up table
relationships. A good general rule for tables is that a table should
contain information about a single real-world entity. Name, address, phone,
Student ID, and so forth can be thought of as attributes of a person. An
exam does not fit into that category.
I agree with Lynn that your database does not seem to be normalized.
Queries, rather than multiple tables, are often the best way of sorting and
filtering data. If you insist on shuffling information between five
different tables you can probably find somebody who will tell you how to do
that, but you should also expect that the people who volunteer here will try
to steer you away from an unsound design.
 
L

Lynn Trapp

It seems to me that it would be much better to have a field in the Student
table that designates whether or not the student is licensed. Then have ALL
the licensing information, for both licensed and unlicensed students in a
tabled called StudentLicensing. You could easily sub-class the information
that is unique to a licensed student into another table and the information
that is unique to an unlicensed student in another.

Students
StudentID
LName
Fname
--Other information related to a student

StudentLicensing
LicenseingID
StudentID -- FK to Students table
--Other fields true about all students whether licensed or not

LicensedStudents
LicensingID -- PK and 1to1 FK to StudentLicensing table
--Other fields specific to licensed students

UnlicensedStudents
LicensingID -- PK and 1to1 FK to StudentLicensing table
--Other fields specific to licensed students

This allows your query to be built much simpler.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Access Junkie List:
http://home.bendbroadband.com/conradsystems/accessjunkie.html
 
H

Harper

I did break out the tables into single-world entities (the students
have just the information you describe) but, though I can see what you
are saying about the problem with the exams, I am having a hard time
visualizing another way to set it up. Each student's scores need to be
recorded somewhere.

I will post two of the tables as images...asking a lot, here, but can
you tell me a little how you would change the structures?

http://accesshelpplease.blogspot.com/
 
H

Harper

Isn't that essentially what I have?

I have

Students
IDNo
Fullname (I should break it up, I know)
LICORNO (number value that signals whether licensed, unlicensed, or
unknown)

Program_Req
IDNo
Interview
Observation
THEN I USED TO HAVE an automatic number with field name LICORNOID this
would be the PK and FK to licensed and unlicensed. But it turned out to
be much simpler to use the IDNo for various reasons

Licensed
IDNo
Licensenumber
Exam 1 score
Exam 2 Score

Unlicensed
IDNo
Exam 3 Score
Exame 4 Score

in other words, the exam you take depend on whether you are licensed or
not. I'm not seeing anything different from what you propose.
 
H

Harper

I wish I could edit my message.
IDNo is the PK for Students and PK and FK for all of the rest. I do
have one-to-one relationships between Program_Req and Licensed and
Unlicensed. I think the only difference is that I continue to use IDNo
rather than an automatic number in Program_Req and the reason is that
when I do queries, I have to go to Program_Req just to figure out whose
IDNo is attached to what LICORNO number (sorry for the random caps; its
not like that in my tables; actually, everything is in caps but I have
reduced to non-caps so as not to seem like I"m shouting in my table
descriptions here).
 
H

Harper

You know what? Screw it. Every point each of you has made is ALREADY
part of my database or ALREADY something I considered. The important
info is NOT that a student took an exam (and no, they don't take the
same exam multiple times) but WHAT score they had. So each student has
a 1:1 relationship to the licensure or (non)licensure information.

There are no many to many relationships.

Thanks anyway for wasting my time when I had a simple question that
begged a simple answer.

"Volunteerism works when you have qualified volunteers"
--A. Mutt
 
E

Edward Reid

Harper said:
Thanks anyway for wasting my time when I had a simple question that
begged a simple answer.
Click to expand...

Harper,

The problem is that your original description didn't fit what you later
said you were doing. You said you wanted to move rows, but then later
posted a list of table items implying that you weren't actually wanting
to copy rows at all.

I was going to try to help, but given the attitude you've shown, I
won't bother.

Edward
 
B

BruceM

From the quote that slaps at those who have responded I take it that you are
aware this is a volunteer group. What puzzles me is your attittude toward
volunteers. People who respond to questions do so out of interest in
Access, and from a wish to pass along what they have learned as others
helped them. Not every response is on target, but then neither is every
question. You may one day learn that it is rarely necessary to move records
between tables, and it is almost always more work than the alternative.
Then again, it seems you have learned all there is to know.
On the subject of wasting time, if you had taken the time to read my
response you would know that my question was whether the same exam could be
taken by multiple students, not whether each student took the same exam
repeatedly. One student --> many exams plus one exam --> many students
equals many-to-many.
I saw the relationships in the other posting before you peevishly pulled the
screen shot. You certainly are making it about as difficult on yourself as
you can.
 
H

Harper

First of all, I do not think I know all there is to know about Access,
by far. But saying right off the bat that my db is not normalized
without knowing everything that goes into it cuts off any discussion
about the original question because we have to spend post after post
arriving at the point where you see that yes my database is normalized.


On the subject of those darn exams again, multiple students take the
same exam (I'm pretty sure I explained this) but they each have scores
all different and unique. I had a table that served as a sort of
junction table to exams and other things they had to do but it served
no purpose, because each record was unique. Its not like they either
had scores of 10, 20, 50, or 100 I could put in a table with a PK/FK
called "Exam_Scores." Sam has 1200, Charlene has 1340, Alice has 1101.
They are all different.

FYI
This "you may one day learn" is exactly what irritates me. Did you know
that I originally HAD a yes/no field called "deactivated" in my
licensed and unlicensed tables? That I never originally planned to move
and delete records? No. There are reasons why I thought to go the other
way, maybe bad reasons, but as I search the discussion in the forums on
Access, I see that other people wanted to do the same thing (but they
got answers about using VBA, where I do not like to use code I don't
understand) without being admonished for it. Now, I would've been happy
to have been asked why I want to do it the latter way and not the
former, and I couldn't explained all my reasons, and then someone
perhaps could've been pointed them out as wrong reasons, and I'd come
away with a simple solution to my problem. But then someone comes along
and says "your db isn't normalized, here is the solution" with the same
&*#$&@#% relationships and tables I had in the first place.

I did not "peevishly" remove my images. I took them down because I
figured no one would respond after what I said, and I was/am fine with
that.
 
H

Harper

Because I never got around to the point where I was going to say that I
hadn't yet created those tables yet. I had originally thought to do a
"deactivated" yes/no field in the tables instead, and was still
waffling when I posted my question (among my considerations: maybe its
not worth it to copy and delete records; maybe it just bloats my db;
but yet even if it bloats it, at least its all in one "place"). No one
would've seen those contradictory images if no one questioned my db's
normalization before answering my original question.
 
B

BruceM

Responses inline.

Harper said:
First of all, I do not think I know all there is to know about Access,
by far. But saying right off the bat that my db is not normalized
without knowing everything that goes into it cuts off any discussion
about the original question because we have to spend post after post
arriving at the point where you see that yes my database is normalized.
Click to expand...
Your description did not seem to be of a normalized database. People
pointed that out. How could they know that you were providing fragmentary
information? If somebody wants to know the best circular saw blade to use
for cutting sheetrock I could point them to a masonry blade or something,
but more likely I would point out that a circular saw probably is not the
best approach to cutting sheetrock. But maybe you need very thin strips for
some unusual reason, and a circular saw is in fact the best (or only)
option. In that case you should offer some additional explanation, because
any responsible advisor would point out that a utility knife or specially
designed handsaw is usually the tool of choice.
On the subject of those darn exams again, multiple students take the
same exam (I'm pretty sure I explained this) but they each have scores
all different and unique. I had a table that served as a sort of
junction table to exams and other things they had to do but it served
no purpose, because each record was unique. Its not like they either
had scores of 10, 20, 50, or 100 I could put in a table with a PK/FK
called "Exam_Scores." Sam has 1200, Charlene has 1340, Alice has 1101.
They are all different.
Click to expand...
So you never want to see a listing of who took a particular test. Fine.
FYI
This "you may one day learn" is exactly what irritates me. Did you know
that I originally HAD a yes/no field called "deactivated" in my
licensed and unlicensed tables? That I never originally planned to move
and delete records? No.
Click to expand...

You are correct. There was no way of knowing what you did not say.
There are reasons why I thought to go the other
way, maybe bad reasons, but as I search the discussion in the forums on
Access, I see that other people wanted to do the same thing (but they
got answers about using VBA, where I do not like to use code I don't
understand) without being admonished for it. Now, I would've been happy
to have been asked why I want to do it the latter way and not the
former, and I couldn't explained all my reasons, and then someone
perhaps could've been pointed them out as wrong reasons, and I'd come
away with a simple solution to my problem. But then someone comes along
and says "your db isn't normalized, here is the solution" with the same
&*#$&@#% relationships and tables I had in the first place.
Click to expand...

Lynn's suggestion was very different from what you had posted, as I recall.
I did not "peevishly" remove my images. I took them down because I
figured no one would respond after what I said, and I was/am fine with
that.
Click to expand...

Yeah. Right.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Finding a Record from a subform 1
Creating a table on a form 3
recall of infor on a form 2
Choose a value from a combo box based on a text box 0
Data entry for a table from a form 2
Data entry to table from a form 1
Can't pull all the 2
update many fields in a form from existing info. 2

Top