connecting SQL code for a Query

[Huddle]

I need to add to this code becuase not only do I want to look up the Fiscal Year for the "Last Signature Date", I also want to calculate when the "Next FSA Due" is due by taking the "Last Signature Date" and times it by a number of years depending on the entry in another field. Then I want to look up the Fiscal Year for the "Next FSA Due".

Can you do it all in one Query or do you have to have Sub Queries? If you can do it in one Query how do you connect the coding?



SELECT Tbl_Building_Information.Bldg_Number, Tbl_Building_Information.FSL, Tbl_Building_Information.Last_FSA, Tbl_Building_Information.Last_Signature_Date, Tbl_Building_Information.Bldg_Name, Tbl_Building_Information.Address, Tbl_Building_Information.City, Tbl_Building_Information.State, Tbl_Building_Information.Zip, Tbl_Building_Information.Commander, Tbl_Building_Information.Inspector_First_Name, Tbl_Building_Information.Inspector_Last_Name, Tbl_Building_Information.On_Schedule, Tbl_Building_Information.District, Tbl_Building_Information.Delegated, Tbl_Building_Information.Population, Tbl_Building_Information.Owned_Leased, Tbl_Building_Information.Lease_Exp_Date, Tbl_Building_Information.Sq_Feet, Tbl_Building_Information.GSA_Contact, Tbl_Building_Information.Notes, Tbl_Building_Information.In_Ramp, Tbl_Building_Information.In_EIS, Tbl_Building_Information.FPS_Responsible, Tbl_Building_Information.Review_Date, Lookup_Tbl_Fiscal_Year.FY
FROM Tbl_Building_Information LEFT JOIN Lookup_Tbl_Fiscal_Year ON Tbl_Building_Information.[Last_Signature_Date]>=Lookup_Tbl_Fiscal_Year.Start_Date AND Tbl_Building_Information.[Last_Signature_Date]<=Lookup_Tbl_Fiscal_Year.End_Date

Could I not do this as a calculated control in the form itself? Would the expression be =[Tbl_Building_Information].[Last_Signature_Date]>=[Lookup_Tbl_Fiscal_Year].[Start_Date] And [Tbl_Building_Information].[Last_Signature_Date]<=[Lookup_Tbl_Fiscal_Year].[End_Date]

[admessing]

Your SQL:

 SELECT 
Tbl_Building_Information.Bldg_Number, 
Tbl_Building_Information.FSL, 
Tbl_Building_Information.Last_FSA, 
Tbl_Building_Information.Last_Signature_Date, 
Tbl_Building_Information.Bldg_Name, 
Tbl_Building_Information.Address, 
Tbl_Building_Information.City, 
Tbl_Building_Information.State, 
Tbl_Building_Information.Zip, 
Tbl_Building_Information.Commander, 
Tbl_Building_Information.Inspector_First_Name, 
Tbl_Building_Information.Inspector_Last_Name, 
Tbl_Building_Information.On_Schedule, 
Tbl_Building_Information.District, 
Tbl_Building_Information.Delegated, 
Tbl_Building_Information.Population, 
Tbl_Building_Information.Owned_Leased, 
Tbl_Building_Information.Lease_Exp_Date, 
Tbl_Building_Information.Sq_Feet, 
Tbl_Building_Information.GSA_Contact, 
Tbl_Building_Information.Notes, 
Tbl_Building_Information.In_Ramp, 
Tbl_Building_Information.In_EIS, 
Tbl_Building_Information.FPS_Responsible, 
Tbl_Building_Information.Review_Date, 
Lookup_Tbl_Fiscal_Year.FY
FROM Tbl_Building_Information LEFT JOIN Lookup_Tbl_Fiscal_Year 
ON Tbl_Building_Information.[Last_Signature_Date]
>=Lookup_Tbl_Fiscal_Year.Start_Date AND 
Tbl_Building_Information.[Last_Signature_Date]
<=Lookup_Tbl_Fiscal_Year.End_Date