The contents of your test1.cfm should look similar to the following:
SELECT tblStateProvince.State, tblStateProvince.Name, tblFacility.Name as FName, tblFacility.City, tblFacility.PostalCode, tblFacility.Address, tblFacility.OID,ContractNumber,ContractEffectiveDate
FROM tblFacility INNER JOIN tblStateProvince ON tblFacility.OIDStateProvince = tblStateProvince.OID
<cfreport template="testReport.cfr" query="qryReportData" format="pdf">
In your report, do not use a label control for non-static text. Use a field control and set the control's Expression property to query.FName and things will start to work as you expect.
Your second and third questions are probably negated by doing the above. If not, then please start a new thread with more detail of what you are trying to accomplish and what the result of your attempt is.
Thanks I'll do that!
Do not include ColdFusion syntax in the text of the query in the Query Builder.
The error message is telling you that MS Access does not recognize the text of your query.
Note that the example code I posted must be entirely contained in a .CFM file.