Need help on SQL query to use in ASP page - please??

[sudz]

Good Day!

I have the following queries that are executed in Access from linked SQL tables. I am using the queries in an ASP page to generate some letters. My goal is to reduce the queries to the fewest possible. Can someone on the list help me learn how to combine them to get the same end result as done in Access? You will notice that the queries are building off of a previous query as shown in the brackets. If anyone has any links that will help me figure this out or a guru on this site who would be willing to help me learn how to this - I would be GREATLY appreciative! You may contact me off the list at info@jxnmichigan.com

Thank you for any and all assistance.

CURRENTLY USED QUERIES:

QUERY ONE: List of Case Load Events:
SELECT Patient.Full_Name, Patient.DataSource_ID, evCase_Load.Create_Date, evCase_Load.Primary_Cont_5035
FROM Patient LEFT JOIN evCase_Load ON Patient.Patient_Key = evCase_Load.Patient_Key;

QUERY TWO: List of Case Load Events Max Dates:
SELECT Patient.Full_Name, Patient.DataSource_ID, Max(evCase_Load.Create_Date) AS MaxOfCreate_Date
FROM Patient LEFT JOIN evCase_Load ON Patient.Patient_Key = evCase_Load.Patient_Key
GROUP BY Patient.Full_Name, Patient.DataSource_ID;

QUERY THREE: Primary Contract:
SELECT [List of Case Load Events Max Dates].Full_Name, [List of Case Load Events Max Dates].DataSource_ID, [List of Case Load Events].Primary_Cont_5035
FROM [List of Case Load Events Max Dates] LEFT JOIN [List of Case Load Events] ON ([List of Case Load Events Max Dates].DataSource_ID = [List of Case Load Events].DataSource_ID) AND ([List of Case Load Events Max Dates].MaxOfCreate_Date = [List of Case Load Events].Create_Date);

QUERY FOUR: Build Consumer Letter
SELECT Date() AS [Date], Auths.Auth_Date, Month([Auths]![Auth_Date]) AS [Month], Day([Auths]![Auth_Date]) AS [Day], Year([Auths]![Auth_Date]) AS [Year], DateSerial([Year],[Month],[Day]) AS [Auth Date], Patient.First_Name, Patient.Last_Name, Patient.Middle_Init, Trim([First_Name]) & " " & Trim([Middle_Init]) & " " & Trim([Last_Name]) AS Name, Patient.Address1, Patient.Address2, Trim([Patient]![City]) & ", " & Trim([Patient]![State]) & " " & Trim([Patient]![Zip]) AS CityStZip, Provider.Provider_Name, Provider.Address1, Provider.Address2, Trim([Provider]![City]) & ", " & Trim([Provider]![State]) & " " & Trim([Provider]![Zip]) AS ProvCityStZip, Provider.Phone, Auths.Eff_Date, Auths.Exp_Date, Auths.LOC_Desc, Auths.Create_By, Auths.Auth_Units, "Dear" & " " & Trim([First_Name]) & "," AS DearName, LOC.Modality_Desc, Auths.Auth_Number, Auths.Frequency, [Primary Contract].Primary_Cont_5035
FROM (((Auths LEFT JOIN Provider ON Auths.Provider_Key = Provider.Provider_Key) LEFT JOIN Patient ON Auths.Patient_Key = Patient.Patient_Key) LEFT JOIN LOC ON Auths.LOC_Key = LOC.LOC_Key) LEFT JOIN [Primary Contract] ON Auths.xPatient_ID = [Primary Contract].DataSource_ID;

QUERY FIVE: Build Consumer Letter 2
SELECT [Build Consumer Letter].Auth_Date, [Build Consumer Letter].Month, [Build Consumer Letter].Day, [Build Consumer Letter].Year, [Build Consumer Letter].[Auth Date], [Build Consumer Letter].Patient.Address1 AS [Mem Add 1], [Build Consumer Letter].Patient.Address2 AS [Mem Add 2], [Build Consumer Letter].CityStZip, [Build Consumer Letter].Provider_Name, [Build Consumer Letter].Provider.Address1 AS [Prov Add 1], [Build Consumer Letter].Provider.Address2 AS [Prov Add 2], [Build Consumer Letter].ProvCityStZip, [Build Consumer Letter].Phone, [Build Consumer Letter].Eff_Date, [Build Consumer Letter].Exp_Date, Int(DateDiff(&quot;d&quot;,[Eff_Date],[Exp_Date])/30) AS Monthsa, IIf([Monthsa]<1,[Monthsa]+1,[Monthsa]) AS Months, [Build Consumer Letter].LOC_Desc, [Build Consumer Letter].Create_By, [Build Consumer Letter].Auth_Units, [Build Consumer Letter].Name, [Build Consumer Letter].DearName, [Build Consumer Letter].Modality_Desc, [Build Consumer Letter].Auth_Number, [Build Consumer Letter].Primary_Cont_5035, [Build Consumer Letter].Date
FROM [Build Consumer Letter]
WHERE ((([Build Consumer Letter].[Auth Date])=#10/4/2002#));

Best Regards,
Sue Kurczewski

======================================
An Ez-intranet is a private, secure space on the Web where all members of your workgroup or family can easily communicate with each other, share information, coordinate resources and collaborate on projects. Our easy-to-use, hosted EZ-Intranet service takes all the hassle out of putting people and information together - Now you can make it happen instantly, for anyone! Try it FREE at

http://www.ezintranets.com

 

[TheDoubleB]

Have you tried building you SQL statement using IF statements? If you query is based on data entered on the form then you can use IF request.form(&quot;name&quot <> &quot;&quot; Then ... and add a add a line to the SQL statement sql=sql & &quot;other criteria&quot;, building it as you go depending on what was entered.