Lookup/Linking Help Desperately Needed

  • Thread starter Thread starter Manuel
  • Start date Start date
M

Manuel

I have three main tables all linked to one another.
a) First table is called Students with ST_ID# as primary
key.
b)Then I have a table called registrations with reg# set
as primary key. This table then looks up ST_ID# and DOB
from the first table (used Look-Up Wizzard for Data type
to make this connection). Works fine.
c) The last table is called LOG, which has CONT# AS
primary key. I again used the look up wizzard to link to
look up to those clients in Registration table who are
currently active (no discharge date). Our guidance
counsellors enter each time they have contact with one of
the registered students

Everything works great as far as getting all the
information that I want. The serious problem is when the
student becomes discharged, all the log entries completely
disappear as they are no longer an active registrant. How
do I make this information stay in the log once
discharged? I am using access 97.

Thanks so much for your help.
 
I have three main tables all linked to one another.
a) First table is called Students with ST_ID# as primary
key.
b)Then I have a table called registrations with reg# set
as primary key. This table then looks up ST_ID# and DOB
from the first table (used Look-Up Wizzard for Data type
to make this connection). Works fine.
c) The last table is called LOG, which has CONT# AS
primary key. I again used the look up wizzard to link to
look up to those clients in Registration table who are
currently active (no discharge date). Our guidance
counsellors enter each time they have contact with one of
the registered students

Everything works great as far as getting all the
information that I want. The serious problem is when the
student becomes discharged, all the log entries completely
disappear as they are no longer an active registrant. How
do I make this information stay in the log once
discharged? I am using access 97.

Thanks so much for your help.
Click to expand...

This is one of the BIG disadvantages of a) using table datasheets and
b) using the Lookup Wizard. How are you "discharging" the students? If
you're deleting the Students table entry, and if you have the
relationships set up with Cascading Deletes, you will indeed be
deleting the log table entry (after a warning that "data in related
tables will be deleted". You may want to consider instead putting a
Yes/No field ACTIVE in the Students table; just set it to FALSE when
the student is discharged.

And... use Forms, with combo boxes, to do your data entry, not table
datasheets.
 
I do not delete any records out at all. I use what is
called a discharge date. I do use a form and combo box to
pick the correct student. In fact the code for the lookup
box is below. I just need to know how to keep the student
name from disappearing in the log once discharge date is
entered. Everything stays there as far as the time spent
and type of contact. It is just the student's name
disappears completely.


SELECT DISTINCTROW tbl_IntakesDischarges.IntakeID,
[lastname] & "," & [firstname] AS Client,
tbl_IntakesDischarges.st_id#,
tbl_IntakesDischarges.IntakeDate,
tbl_Employees.EmployeeName, tbl_ProgramName.ProgramName,
tbl_IntakesDischarges.DischargeDate FROM tbl_ProgramName
INNER JOIN (tbl_Clients RIGHT JOIN (tbl_Employees INNER
JOIN tbl_IntakesDischarges ON tbl_Employees.EmployeeId =
tbl_IntakesDischarges.EmployeeID) ON tbl_Clients.st_id# =
tbl_IntakesDischarges.st_id#) ON
tbl_ProgramName.ProgNameId =
tbl_IntakesDischarges.ProgNameID WHERE
(((tbl_IntakesDischarges.DischargeDate) Is Null)) ORDER BY
[lastname] & "," & [firstname];
 
Manuel, the WHERE clause in your query is why the student record disappears
when you enter a discharge date.

WHERE (((tbl_IntakesDischarges.DischargeDate) Is Null))

Just take out that clause and the student name will no longer disappear.

Manuel said:
I do not delete any records out at all. I use what is
called a discharge date. I do use a form and combo box to
pick the correct student. In fact the code for the lookup
box is below. I just need to know how to keep the student
name from disappearing in the log once discharge date is
entered. Everything stays there as far as the time spent
and type of contact. It is just the student's name
disappears completely.


SELECT DISTINCTROW tbl_IntakesDischarges.IntakeID,
[lastname] & "," & [firstname] AS Client,
tbl_IntakesDischarges.st_id#,
tbl_IntakesDischarges.IntakeDate,
tbl_Employees.EmployeeName, tbl_ProgramName.ProgramName,
tbl_IntakesDischarges.DischargeDate FROM tbl_ProgramName
INNER JOIN (tbl_Clients RIGHT JOIN (tbl_Employees INNER
JOIN tbl_IntakesDischarges ON tbl_Employees.EmployeeId =
tbl_IntakesDischarges.EmployeeID) ON tbl_Clients.st_id# =
tbl_IntakesDischarges.st_id#) ON
tbl_ProgramName.ProgNameId =
tbl_IntakesDischarges.ProgNameID WHERE
(((tbl_IntakesDischarges.DischargeDate) Is Null)) ORDER BY
[lastname] & "," & [firstname];
-----Original Message-----


This is one of the BIG disadvantages of a) using table datasheets and
b) using the Lookup Wizard. How are you "discharging" the students? If
you're deleting the Students table entry, and if you have the
relationships set up with Cascading Deletes, you will indeed be
deleting the log table entry (after a warning that "data in related
tables will be deleted". You may want to consider instead putting a
Yes/No field ACTIVE in the Students table; just set it to FALSE when
the student is discharged.

And... use Forms, with combo boxes, to do your data entry, not table
datasheets.


.
Click to expand...
Click to expand...
 
Thanks for your response. I put the where clause in there
because I need to select from only OPEN cases. Students
may be registered and discharged many times and I need to
ensure that the time is being attached to the current open
record for that client and not one say two years ago.
This also potentially will be a very large database over
time and would be too confusing to have all registrations
over that time period.

-----Original Message-----
Manuel, the WHERE clause in your query is why the student record disappears
when you enter a discharge date.

WHERE (((tbl_IntakesDischarges.DischargeDate) Is Null))

Just take out that clause and the student name will no longer disappear.

I do not delete any records out at all. I use what is
called a discharge date. I do use a form and combo box to
pick the correct student. In fact the code for the lookup
box is below. I just need to know how to keep the student
name from disappearing in the log once discharge date is
entered. Everything stays there as far as the time spent
and type of contact. It is just the student's name
disappears completely.


SELECT DISTINCTROW tbl_IntakesDischarges.IntakeID,
[lastname] & "," & [firstname] AS Client,
tbl_IntakesDischarges.st_id#,
tbl_IntakesDischarges.IntakeDate,
tbl_Employees.EmployeeName, tbl_ProgramName.ProgramName,
tbl_IntakesDischarges.DischargeDate FROM tbl_ProgramName
INNER JOIN (tbl_Clients RIGHT JOIN (tbl_Employees INNER
JOIN tbl_IntakesDischarges ON tbl_Employees.EmployeeId =
tbl_IntakesDischarges.EmployeeID) ON tbl_Clients.st_id# =
tbl_IntakesDischarges.st_id#) ON
tbl_ProgramName.ProgNameId =
tbl_IntakesDischarges.ProgNameID WHERE
(((tbl_IntakesDischarges.DischargeDate) Is Null)) ORDER BY
[lastname] & "," & [firstname];
-----Original Message-----
On Fri, 19 Mar 2004 13:40:22 -0800, "Manuel"

I have three main tables all linked to one another.
a) First table is called Students with ST_ID# as primary
key.
b)Then I have a table called registrations with reg# set
as primary key. This table then looks up ST_ID# and DOB
from the first table (used Look-Up Wizzard for Data type
to make this connection). Works fine.
c) The last table is called LOG, which has CONT# AS
primary key. I again used the look up wizzard to link to
look up to those clients in Registration table who are
currently active (no discharge date). Our guidance
counsellors enter each time they have contact with one of
the registered students

Everything works great as far as getting all the
information that I want. The serious problem is when the
student becomes discharged, all the log entries completely
disappear as they are no longer an active registrant. How
do I make this information stay in the log once
discharged? I am using access 97.

Thanks so much for your help.

This is one of the BIG disadvantages of a) using table datasheets and
b) using the Lookup Wizard. How are you "discharging"
Click to expand...
the
students? If
you're deleting the Students table entry, and if you
Click to expand...
have
the
relationships set up with Cascading Deletes, you will indeed be
deleting the log table entry (after a warning
Click to expand...
that "data
in related
tables will be deleted". You may want to consider
Click to expand...
instead
putting a
Yes/No field ACTIVE in the Students table; just set it
Click to expand...
to
FALSE when
the student is discharged.

And... use Forms, with combo boxes, to do your data entry, not table
datasheets.


.
Click to expand...
Click to expand...


.
Click to expand...
 
I do not delete any records out at all. I use what is
called a discharge date. I do use a form and combo box to
pick the correct student. In fact the code for the lookup
box is below. I just need to know how to keep the student
name from disappearing in the log once discharge date is
entered. Everything stays there as far as the time spent
and type of contact. It is just the student's name
disappears completely.
Click to expand...

Are the student ID's disappearing *FROM THE LOG TABLE*? I suspect
they're just disappearing from this particular Query (because of the
criterion). You may just want a different query without the date
criterion to display the history.
 
Manuel said:
Thanks for your response. I put the where clause in there
because I need to select from only OPEN cases. Students
may be registered and discharged many times and I need to
ensure that the time is being attached to the current open
record for that client and not one say two years ago.
This also potentially will be a very large database over
time and would be too confusing to have all registrations
over that time period.
Click to expand...

Anytime you have a ComboBox that "stores one value, but displays another"
then the list will appear blank any time the current entry is not included
in the list provided by the RowSource property. You could use a WHERE
clause that filters on "all students not yet discharged OR who are in the
current record of the form". Then you still see entries no longer in the
list provided that particular record has focus.
 
Back
Top