Query causing me problems

[DebbieG]

Here's what the tables look like:

Students:
SSN
LN
FN
MI
etc.

PreTestData:
SSN
PreTestDate
PreTest

PostTestData:
SSN
PostTestDate
PostTest


Here's my data:

SSN Name PreTestDate PreTest PostTestDate PostTest
123456789 Joe 01/08/2003 130
234567890 Sam 01/08/2003 128 04/06/2003 225
123456789 Joe 01/12/2002 123 04/04/2002 234
234567890 Sam 01/12/2002 134
123456789 Joe 122 04/02/2001


Here's what the user wants in a report is:

SSN Name PreTestDate PreTest PostTestDate PostTest
123456789 Joe 01/08/2003 130
234567890 Sam 01/08/2003 128 04/06/2003 225


As you can see, students may or may not take both tests every year. I
cannot figure out how to get "Joe" to show up if he didn't take the test in
2003. If it's something obvious or simple, please be kind -- I'm kinda
brain dead!

HELP! ... please

Debbie

 

[Rowan]

Debbi

Using Left Joins will allow you to show all students regardless if they took tests. The code should look something like

Select J1.SSN, J1.Name, J1.PreTestDate, J1.PreTest, Q2.PostTestDate, Q2.PostTes

fro

(SELECT Students.SSN, Students.Name, Q1.PreTestDate, Q1.PreTes

FROM Student

Left Joi

(Select SSN, PreTestDate, PreTes
Fro
PreTestDat
Where PreTestDate > #01/01/02# ) Q

On Students.SSN = Q1.SSN ) J

Left Joi

(Select SSN, PostTestDate, PostTes
Fro
PostTestDat
Where PostTestDate > #01/01/03# ) Q

On J1.SSN = Q2.SS

I have left you some work to do on the dates

Regard
Rowan

 

[DebbieG]

Rowan,

Thanks so much for responding. I was able to get it to work from your
example.

SELECT J1.SSN, J1.LastNM, J1.FirstNM, J1.LastSerDt, J1.DateTaken,
J1.PreACTTestPrep, J1.ACTEnglishUM, J1.ACTEnglishRH, J1.ACTEnglishOverall,
J1.ACTMathEA, J1.ACTMathAG, J1.ACTMathGT, J1.ACTMathOverall,
J1.ACTReadingSS, J1.ACTReadingAL, J1.ACTReadingOverall, J1.ACTScience,
Q2.DateTaken, Q2.PostACTTestPrep
FROM [SELECT Students.SSN, Students.LastNM, Students.FirstNM,
Students.LastSerDt, Q1.DateTaken, Q1.PreACTTestPrep, Q1.ACTEnglishUM,
Q1.ACTEnglishRH, Q1.ACTEnglishOverall, Q1.ACTMathEA, Q1.ACTMathAG,
Q1.ACTMathGT, Q1.ACTMathOverall, Q1.ACTReadingSS, Q1.ACTReadingAL,
Q1.ACTReadingOverall, Q1.ACTScience
FROM Students
Left Join
(Select SSN, DateTaken, PreACTTestPrep, ACTEnglishUM, ACTEnglishRH,
ACTEnglishOverall, ACTMathEA, ACTMathAG, ACTMathGT, ACTMathOverall,
ACTReadingSS, ACTReadingAL, ACTReadingOverall, ACTScience
From
PreTestPrepACTComp
Where Year(DateTaken) = forms!frmMain!txtEndDate) Q1
On Students.SSN = Q1.SSN]. AS J1 LEFT JOIN [Select SSN, DateTaken,
PostACTTestPrep
From
PostTestPrepACTComp
Where Year(DateTaken) = forms!frmMain!txtEndDate]. AS Q2 ON J1.SSN = Q2.SSN;

but now I need to add 2 more tables and cannot figure out where to put the
information.

Here's what I need to add:

-- Students --
SSN
LN
FN
MI
etc.

-- OtherInfo --
SSN
CurrentGradeLV

-- CurrentGradeLV --
CurrentGradeLV
CurrentGradeLVNM

I need to get the CurrentGradeLVNM to be listed for each student. I've
tried but I keep getting syntax errors.

Can you help me one more time?

Thanks,
Debbie


Debbie

Using Left Joins will allow you to show all students regardless if they took
tests. The code should look something like:

Select J1.SSN, J1.Name, J1.PreTestDate, J1.PreTest, Q2.PostTestDate,
Q2.PostTest

from

(SELECT Students.SSN, Students.Name, Q1.PreTestDate, Q1.PreTest

FROM Students

Left Join

(Select SSN, PreTestDate, PreTest
From
PreTestData
Where PreTestDate > #01/01/02# ) Q1

On Students.SSN = Q1.SSN ) J1

Left Join

(Select SSN, PostTestDate, PostTest
From
PostTestData
Where PostTestDate > #01/01/03# ) Q2

On J1.SSN = Q2.SSN

I have left you some work to do on the dates.

Regards
Rowan

 

[DebbieG]

OK, I thought about it some more and just created a second query and joined
the first query to the 2 tables that I needed and that worked fine. But,
when I use the second query in a report the 2 dates (J1.PreTestDate and
Q2.PostTestDate) won't print. Do I need to change them in some way?

Thanks,
Debbie


Debbie

Using Left Joins will allow you to show all students regardless if they took
tests. The code should look something like:

Select J1.SSN, J1.Name, J1.PreTestDate, J1.PreTest, Q2.PostTestDate,
Q2.PostTest

from

(SELECT Students.SSN, Students.Name, Q1.PreTestDate, Q1.PreTest

FROM Students

Left Join

(Select SSN, PreTestDate, PreTest
From
PreTestData
Where PreTestDate > #01/01/02# ) Q1

On Students.SSN = Q1.SSN ) J1

Left Join

(Select SSN, PostTestDate, PostTest
From
PostTestData
Where PostTestDate > #01/01/03# ) Q2

On J1.SSN = Q2.SSN

I have left you some work to do on the dates.

Regards
Rowan