Query from two related tables

[Guest]

I have two tables, students & homeless students. They are joined with a left
outer join based on student ID number because not all students are homeless.
The student table has the student ID number, first name, last name, address
etc. The second table has student ID number and details about their homeless
situation. I want to develop a query where we can enter records into the
homeless table. I want to use the student ID number in the query from the
homeless table and have the query pull the first name, last name and address
from the student table. When I try this and enter the student ID of a
student who is already in the student table, the last name etc. doesn't fill
in until I close the query and re-open it. Any helpful hints. (I do have
many other tables in the database (schools, principals etc) but I don't think
any of them have to be part of this query.

Thank You

 

[Jeff Boyce]

Not sure what you mean by "pull" fname, lname, addr...

Post the SQL statement for your query.

More info, please...

Jeff Boyce
<Access MVP>

 

[Guest]

The problem is that when you run a query, it extracts the data as it was AT
THAT TIME. Therefore if you add a student ID, it doesn't know you need to add
the student details in. If you must work with queries, this is a limitation
you may have to live with (although there are plenty of more experienced
access people than I)

You would be better off putting your "homeless students" data on a form and
accessing the data through the form. Then, you can have a subform or suchlike
which shows the details of the student associated with that student ID.

You would probably still need to use subform.requery on the after update
event of the student ID field, however.

 

[Guest]

When I say, "pull" I want it to fill in the student's first name and last
name automatically as it exists in the student table when they are already in
the system as a student. They may be in the system as a student and have
just become a homeless student, so I am adding them to my homeless student
table, viewing their demographics from the main, student table.

SQL
SELECT HomelessInfo.StudentID AS HomelessInfo_StudentID,
StudentInfo.LastName, HomelessInfo.REACHOpenDate, StudentInfo.FirstName,
StudentInfo.BirthDate, StudentInfo.LastNameGuardian,
StudentInfo.FirstNameGaurdian, StudentInfo.OtherParentMemo,
StudentInfo.Homeless, StudentInfo.Phone, StudentInfo.OtherPhone,
StudentInfo.Address, StudentInfo.City, StudentInfo.State, StudentInfo.Zip,
StudentInfo.Schoollookup, StudentInfo.Grade, HomelessInfo.REACHCloseDate,
HomelessInfo.REACHSocialWorker, HomelessInfo.CloseReason,
HomelessInfo.HomelessStatus, HomelessInfo.OtherHomelessStatus,
HomelessInfo.[ESE Programlookup], HomelessInfo.SpecifyOtherClosedReason,
HomelessInfo.TutoringNeeded, HomelessInfo.SchoolSuppliesNeeded,
HomelessInfo.MedicationAsstNeeded, HomelessInfo.DentalAsstNeeded,
HomelessInfo.TransportationNeeded, HomelessInfo.ToiletryItemsNeeded,
HomelessInfo.CCEFFundsNeeded, HomelessInfo.ReinholdFundsNeeded,
HomelessInfo.Title1PONeeded, HomelessInfo.AmountPerStudentTtlI,
HomelessInfo.[Free/ReducedLunchNeeded], HomelessInfo.ImmediateContactNeeded,
HomelessInfo.InitialCorrespondence,
HomelessInfo.BegininingAverageDailyAttendance AS
HomelessInfo_BegininingAverageDailyAttendance,
HomelessInfo.EndingAverageDailyAttendance AS
HomelessInfo_EndingAverageDailyAttendance, HomelessInfo.[Student Notes] AS
[HomelessInfo_Student Notes]
FROM StudentInfo INNER JOIN HomelessInfo ON StudentInfo.StudentID =
HomelessInfo.StudentID;

 

[Jeff Boyce]

I may not be fully following, but it sounds like you want to copy
information from one table to another. This is generally neither necessary
nor advisable when using a relational database like Access.

Are you working via forms, or directly in the tables?

Would it be sufficient to see the student demographic info when you select a
"homeless" student?

Good luck

Jeff Boyce
<Access MVP>