Re: Find a row in a table from a form

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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.

Lynn Trapp wrote:
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



"Harper" <harperwork18@xxxxxxxxx> wrote in message
news:1150400742.274161.193290@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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.


Lynn Trapp wrote:
Well, it doesn't sound normalized to me. The other data should be stored
in
tables that are children of the main student table. Can you post your
table
structures?

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



"Harper" <harperwork18@xxxxxxxxx> wrote in message
news:1150398667.847545.228110@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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?


Lynn Trapp wrote:
Harper,
Why do you have different tables for this data? You should simply
place a
couple of boolean fields in your student table that identify whether
or
not
the student is licensed or unlicensed and whether or not it is an
active
(archived) student.

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



"Harper" <harperwork18@xxxxxxxxx> wrote in message
news:1150395999.754294.237510@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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




.

Relevant Pages

  • Re: Find a row in a table from a form
    ... IDNo is the PK for Students and PK and FK for all of the rest. ... table that designates whether or not the student is licensed. ... UnlicensedStudents ... There are exams and licensing numbers attached to ...
    (microsoft.public.access.forms)
  • Re: Licencing terms for students?
    ... Ireland) student may use Oracle ... position on such use of its database products. ... work at have pretty generous licensing terms with Oracle. ...
    (comp.databases.oracle.server)
  • Re: Find a row in a table from a form
    ... Lynn Trapp wrote: ... table that designates whether or not the student is licensed. ... UnlicensedStudents ... There are exams and licensing numbers attached to ...
    (microsoft.public.access.forms)
  • Re: Find a row in a table from a form
    ... It seems to me that it would be much better to have a field in the Student ... UnlicensedStudents ... Lynn Trapp wrote: ... There are exams and licensing numbers attached to ...
    (microsoft.public.access.forms)
  • Re: Find a row in a table from a form
    ... Lynn Trapp wrote: ... There are exams and licensing numbers attached to ... there are exams attached to an unlicensed student. ... The IDNo is the primary key through the tables. ...
    (microsoft.public.access.forms)