why does this F%&/(& SQL not work!?!

[DrewDog_21]

I have 2 really annoying problems with this SQL and I would
be VERY GRATEFUL for help on either of them! My SQL skills
are fairly weak, so HELP!!! This will be long, but please
read and help if you can! If I am not clear on something,
please let me know!!

Background description: 4 tables in a database called
Students, Students_Details, Contacts, and Contacts_Class.
I use the Students - Students_Details relation and the
Contacts - Contacts_Class relation a good bit, so I have
them combined in queries call consStudents and
consContacts, respectively. The table Students has 3
fields that are related to the Contacts table, called
IdMOTHER, IdFATHER, and IdOTHER. E.g. all three fields in
the table Students are related to the same field in
Contacts.

I need to generate a report that shows each record in the
Students table and all related records in the Contacts
table. This is where I have problem #1: I can't figure
out how to create a query that will pull 3 records from the
Contacts table for all Students. This is because not all
of the students have entries in all 3 fields. If, for
example, a student has an entry in IdMOTHER and IdFATHER
but not in IdOTHER, I need to print on the report the
information on the MOTHER and FATHER and a blank record for
the OTHER.

Problem #2: I have been able to create a query that returns
all of the records that exist, which for now is better than
nothing. BUT IT IS SO FRIGGIN' SLOW! In access the query
executes in less than 15 seconds. But if I create the
query and then try to select the records from it in ADO or
Crystal Reports, it takes over half an hour!

Here is the SQL:

SELECT [consStudents].[LASTNAME]+' '+[consStudents].
[FIRSTNAME] AS Students_Name, [consStudents].[GRADO],
[consStudents].[ADDRESS], [consStudents].[PHONE],
[consStudents].[NEIGHBORHOOD], [consStudents].[BIRTHDATE],
[consStudents].[BIRTHPLACE], [consStudents].[YEAR],
[consStudents].[REL_COURSE], consContacts.*
FROM consContacts RIGHT JOIN consStudents ON
([consContacts].[IdCONTACT]=[consStudents].[IdFATHER]) Or
([consContacts].[IdCONTACT]=[consStudents].[IdMOTHER]) Or
([consContacts].[IdCONTACT]=[consStudents].[IdOTHER]);

One POSSIBLE SIGN of a problem is that initially I have to
generate the query with ANDs instead of ORs, like

([consContacts].[IdCONTACT]=[consStudents].[IdFATHER]) And
([consContacts].[IdCONTACT]=[consStudents].[IdMOTHER]) And
([consContacts].[IdCONTACT]=[consStudents].[IdOTHER]);

but that pulls back all of the information in consContacts as blank. When I open the query in SQL view and change the ANDs to ORs, the query executes right, but I can't open it in design mode. The error that I get is that Access can't represent the preceding 3 SQL lines in design view. Why not?

Okay, I hope I didn't put anyone to sleep with all that,
because while this is a problem, my name and the word sleep
will not ever be found in the same sentence!

Thanks in advance,

Andrew

[Edited by DrewDog_21 on 09-11-2000 at 07:19 PM]

[parksie]

If you're trying to put a query together, often what happens is that the query doesn't have a graphical representation. In that case, don't worry - because it's still likely to work.

How about this? It's a little smaller, and doesn't concatenate the name together, but that shouldn't be a problem:

SELECT * FROM consContacts WHERE
(IdCONTACT=IdFATHER Or IdCONTACT=IdMOTHER Or IdCONTACT=IdOTHER);
RIGHT JOIN consStudents

I'm not exactly an expert on joins, but this should be okay. Paging JHausmann...

[JMuller]

You may try this. I selected all columns in the result, but you can restrict them just to the columns you want.

SELECT *
FROM (([consStudents] AS S LEFT JOIN [consContacts] AS F ON S.[IdFATHER]=F.[IdCONTACT])
     LEFT JOIN [consContacts] AS M ON S.[IdMOTHER]=M.[IdCONTACT])
     LEFT JOIN [consContacts] AS O ON S.[IdOTHER]=O.[IdCONTACT];

Hope it works!

[DrewDog_21]

Guys thanks for the replies here. Unfortunately I have an
emergency on another job and can't try them now. I will
get to it later and post with any more questions. Trust
me, questions will arise on this one!

Editing my post here and adding this, so as not to falsely up my post count
JMuller - tried out your SQL and although it is not exactly
what I had in mind, I think I can work with it. More tests
and comments to come, though. Thanks.

[Edited by DrewDog_21 on 09-12-2000 at 07:08 PM]