What would the SQL be for this statement?

[Crispywafers]

Hi,

Quick question. I have been trying and trying to get this to work--
but obviously nested "WHERE"'s do not work. How would this statement
be translated into SQL? (Using it in an append query)

Only select Orders from the Orders Table where Orders.SchoolYear =
[Specific School Year] and Student_Records.Active = True and
Orders.OrderID is not equal to Invoices.OrderID were Invoices.TriID =
"J"

TRIED:

PARAMETERS [Specify School Year] DateTime;
INSERT INTO [Invoices] (OrderID, TriAmountDue)
SELECT Orders.OrderID, Orders.JulyTuition
FROM Student_Records INNER JOIN (Orders LEFT JOIN [Invoices] ON
Orders.OrderID=[Invoices].OrderID) ON
Student_Records.StudentID=Orders.StudentID
WHERE(((Exists (SELECT * FROM Orders WHERE Orders.SchoolYear =
[Specify School Year] AND ((Student_Records.Active) = True) AND
Orders.OrderID <> Invoices.Order ID WHERE Invoices.TriID = "J"));

 

[John Vinson]

Quick question. I have been trying and trying to get this to work--
but obviously nested "WHERE"'s do not work. How would this statement
be translated into SQL? (Using it in an append query)

Only select Orders from the Orders Table where Orders.SchoolYear =
[Specific School Year] and Student_Records.Active = True and
Orders.OrderID is not equal to Invoices.OrderID were Invoices.TriID =
"J"

A NOT IN clause should work for the last criterion. One concern - is
SchoolYear a Date/Time value (which corresponds to a precise instant
of time) or a year number like 2003? Secondly, you should NOT include
the Invoices table in a query inserting into the Invoices table. Try:


PARAMETERS [Specify School Year] DateTime;
INSERT INTO [Invoices] (OrderID, TriAmountDue)
SELECT Orders.OrderID, Orders.JulyTuition
FROM Student_Records INNER JOIN Orders ON
Student_Records.StudentID=Orders.StudentID
WHERE Orders.SchoolYear = [Specify School Year]
AND Student_Records.Active = True
AND Orders.OrderID NOT IN (SELECT Invoices.OrderID WHERE
Invoices.TriID = "J");

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Crispywafers]

Thanks for your help.

I kept getting a syntax error when I tried the code. I tried inserting
() in various places but with no luck.

The schoolyear is used as a year marker. It is always "9/1/2004" or
"9/1/2005", etc.


I just need the statement to append the new july invoices ... and not
append them again if they already exist in the invoices table and the
query is run again. If that makes sense. :O)

Thank you again for your help!

Quick question. I have been trying and trying to get this to work--
but obviously nested "WHERE"'s do not work. How would this statement
be translated into SQL? (Using it in an append query)

Only select Orders from the Orders Table where Orders.SchoolYear =
[Specific School Year] and Student_Records.Active = True and
Orders.OrderID is not equal to Invoices.OrderID were Invoices.TriID =
"J"

A NOT IN clause should work for the last criterion. One concern - is
SchoolYear a Date/Time value (which corresponds to a precise instant
of time) or a year number like 2003? Secondly, you should NOT include
the Invoices table in a query inserting into the Invoices table. Try:


PARAMETERS [Specify School Year] DateTime;
INSERT INTO [Invoices] (OrderID, TriAmountDue)
SELECT Orders.OrderID, Orders.JulyTuition
FROM Student_Records INNER JOIN Orders ON
Student_Records.StudentID=Orders.StudentID
WHERE Orders.SchoolYear = [Specify School Year]
AND Student_Records.Active = True
AND Orders.OrderID NOT IN (SELECT Invoices.OrderID WHERE
Invoices.TriID = "J");

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Crispywafers]

John,

I got it to work. Thanks so much-- your code worked like a charm.

Thank you!

Quick question. I have been trying and trying to get this to work--
but obviously nested "WHERE"'s do not work. How would this statement
be translated into SQL? (Using it in an append query)

Only select Orders from the Orders Table where Orders.SchoolYear =
[Specific School Year] and Student_Records.Active = True and
Orders.OrderID is not equal to Invoices.OrderID were Invoices.TriID =
"J"

A NOT IN clause should work for the last criterion. One concern - is
SchoolYear a Date/Time value (which corresponds to a precise instant
of time) or a year number like 2003? Secondly, you should NOT include
the Invoices table in a query inserting into the Invoices table. Try:


PARAMETERS [Specify School Year] DateTime;
INSERT INTO [Invoices] (OrderID, TriAmountDue)
SELECT Orders.OrderID, Orders.JulyTuition
FROM Student_Records INNER JOIN Orders ON
Student_Records.StudentID=Orders.StudentID
WHERE Orders.SchoolYear = [Specify School Year]
AND Student_Records.Active = True
AND Orders.OrderID NOT IN (SELECT Invoices.OrderID WHERE
Invoices.TriID = "J");

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps