I am in the process of switching my back end of my database to an SQL Server. There are a few queries that are not working. I have narrowed it down to the LEFT JOIN statement, but I have no idea how to fix it. If I take out the LEFT JOIN, the query runs fine, but when I put it back, I get a bunch of the above errors.
Here is the SQL code: -
SELECT Group.gBRET,
-
Group.gName,
-
Group.gID,
-
Assessment.aReqReceived,
-
Assessment.aPEO,
-
Assessment.aEffectiveDate,
-
Assessment.aBRETTier,
-
qCurrentPl.cpID,
-
qCurrentPl.cpName,
-
qCurrentPl.cpPharmacyTier,
-
qCurrentPl.cpHDHP,
-
qCurrentPl.cpOutOfNetwork,
-
qCurrentPl.cpGatekeeper,
-
qCurrentPl.cpInPatientCopay,
-
qCurrentPl.cpValueNetwork,
-
qCurrentPl.cpHMO,
-
qCurrentPl.cpValuePlan,
-
qCurrentPl.cpInPatientCopayAmt,
-
qCurrentPl.cpInPatientCopayOut,
-
qCurrentPl.cpIPCoPayLimits,
-
qCurrentPl.cpDedCoinsuranceRx,
-
qCurrentPl.cpSingleDedIn,
-
qCurrentPl.cpFamilyDedIn,
-
qCurrentPl.cpSingleDedOut,
-
qCurrentPl.cpFamilyDedOut,
-
qCurrentPl.cpCoinsuranceIn,
-
qCurrentPl.cpCoinsuranceOut,
-
qCurrentPl.cpSingleOOPMaxIn,
-
qCurrentPl.cpFamilyOOPMaxIn,
-
qCurrentPl.cpSingleOOPMaxOut,
-
qCurrentPl.cpFamilyOOPMaxOut,
-
qCurrentPl.cpPhysOfficeVisitIn,
-
qCurrentPl.cpSpecOfficeVisitIn,
-
qCurrentPl.cpPhysOfficeVisitOut,
-
qCurrentPl.cpInPatientHospIn,
-
qCurrentPl.cpInPatientInDetailed,
-
qCurrentPl.cpInPatientHospOut,
-
qCurrentPl.cpDiagnosticsIn,
-
qCurrentPl.cpDiagnosticsOut,
-
qCurrentPl.cpOutPatientFacIn,
-
qCurrentPl.cpOutPatientFacOut,
-
qCurrentPl.cpUrgentCare,
-
qCurrentPl.cpEmergencyRoom,
-
qCurrentPl.cpTier1Rx,
-
qCurrentPl.cpTier2Rx,
-
qCurrentPl.cpTier3Rx,
-
qCurrentPl.cpTier4Rx,
-
qCurrentPl.cpSubsEE,
-
qCurrentPl.cpSubsES,
-
qCurrentPl.cpSubsEC,
-
qCurrentPl.cpSubsEF,
-
qCurrentPl.cpSubsFC,
-
qCurrentPl.cptID,
-
qCurrentPl.cparID,
-
qCurrentPl.cpSingle,
-
qCurrentPl.cpDouble,
-
qCurrentPl.cpChildren,
-
qCurrentPl.cpFamily,
-
qCurrentPl.cpFamilyCont,
-
qCurrentPl.cpMonthlyTotal,
-
qCurrentPl.cpRenewalDate,
-
qCurrentPl.cpIncrease,
-
qCurrentPl.AuditTrail,
-
qCurrentPl.cpCarrierRenewal,
-
qCurrentPl.cpNotes,
-
qNewPl.*,
-
[cpsinglededin]-[psinglededin] AS SingleDedInC,
-
[cpphysofficevisitin]-[pphysofficevisitin] AS PCPCoPayC,
-
[cpspecofficevisitin]-[pspecofficevisitin] AS SpecCoPayC,
-
[pcoinsurancein]-[cpcoinsurancein] AS CoinsuranceC,
-
([cpsingleoopinwoded]-[psingleoopinwoded]) AS OOPMaxC,
-
[cpInPatientcopayamt]-[pInPatientcopayamt] AS InPatientHospC,
-
[cpTier1Rx]-[pTier1Rx] AS Tier1RxC,
-
[cpTier2Rx]-[pTier2Rx] AS Tier2RxC,
-
[cpTier3Rx]-[pTier3Rx] AS Tier3RxC,
-
IIf([cpPharmacyTier]=[ppharmacyTier],"No","Yes") AS Tier4RxC,
-
IIf([cpHDHP]=[phdhp],"No","Yes") AS HDHPC, IIf([cpHMO]=[pHMO],"No","Yes") AS HMONetC,
-
IIf([cpOutOfNetwork]=[pOutOfNetwork],"No","Yes") AS OutOfNetC,
-
IIf([cpGatekeeper]=[pGatekeeper],"No","Yes") AS GatekeeperC,
-
IIf([cpDedCoinsuranceRx]=[pDedCoinsuranceRx],"No","Yes") AS DedCoinsRxC,
-
DateAdd("m",0,DateSerial(Year([aeffectivedate]),Month([aeffectivedate]),1))-1 AS ADPPerEndDate,
-
DateAdd("yyyy",+1,[adpperenddate]) AS CompEndDate, DateDiff("m",[aeffectivedate],[cPRenewalDate]) AS MosatCurr,
-
12-[mosatcurr] AS MosAtRen, DateDiff("m",[aeffectivedate],[PRenewalDate]) AS ADPMosatCurr,
-
12-[adpmosatcurr] AS ADPMosAtRen, 1+[cpincrease] AS CurCarIncr,
-
DateDiff("m",[aeffectivedate],[cpRenewalDAte]) AS MosatCurrBl,
-
12-[Mosatcurrbl] AS MosAtRenBl,
-
[mosatcurrbl]+[mosatrenbl] AS TotalMos,
-
(([rsingle]*[npSubsEE])+([rdouble]*[npSubsES])+([rchildren]*[npSubsEC])+([rfamily]*[npSubsEF])+([rfamilycont]*[npSubsFC]))*12 AS ADPRates,
-
IIf([cpmonthlytotal]>0,[cpmonthlytotal]*12,(([cpsingle]*[cpSubsEE])+([cpdouble]*[cpSubsES])+([cpchildren]*[cpsubsEC])+([cpfamily]*[cpSubsEF])+([cpfamilycont]*[cpsubsFC]))*12) AS CurrentRates,
-
IIf([cpmonthlytotal]>0,[cpmonthlytotal]*12,(([cpsingle]*[npSubsEE])+([cpdouble]*[npSubsES])+([cpchildren]*[npsubsEC])+([cpfamily]*[npSubsEF])+([cpfamilycont]*[npsubsFC]))*12) AS CurrentRates1,
-
IIf([cpmonthlytotal]>0,[cpSubsEE]+[cpSubsES]+[cpSubsEC]+[cpSubsEF]+[cpSubsFC],0) AS CurSubsTot,
-
[cpsubsee]+[cpsubses]+[cpsubsec]+[cpsubsef]+[cpsubsfc] AS cSubsTotal, [npSubsEE]+[npSubsES]+[npSubsEC]+[npSubsEF]+[npSubsFC] AS npSubsTotal,
-
[cpMemEE]+[cpMemES]+[cpMemEC]+[cpMemEF] AS npMemTotal,
-
Assessment.aID, 1+[oAssumedRenewal] AS ADPRenewalRate, qCurrentPl.ccName, qCurrentPl.cpMemEE, qCurrentPl.cpMemES,
-
qCurrentPl.cpMemEC,
-
qCurrentPl.cpMemEF
-
FROM [Group] INNER JOIN ((Assessment INNER JOIN qCurrentPl ON Assessment.aID = qCurrentPl.cpaID)
-
-
LEFT JOIN qNewPl ON qCurrentPl.cpID = qNewPl.ocpID) ON (Group.gID = Assessment.gaID) AND (Group.gBRET = Assessment.agBRET);
-
Can someone please tell me what I am missing here? Thank you so much for your help.
Kelly
3 3858
You don't have an alias called "carrier"
Happy Coding!!!
~~ CK
I'm sorry...I have no idea what that means!
Kelly
Do you have a table called Carrier anywhere on your TSQL?
Good Luck!!!
~~ CK
Sign in to post your reply or Sign up for a free account.
Similar topics
by: CSDunn |
last post by:
Hello,
I have a combo box (Combo7) that needs to call a function during the
After Update event of the combo box. The function resides in an Access
2000 ADP Module called MMAnswerData_code.
The...
|
by: CSDunn |
last post by:
Hello,
I have a situation in which I need to address three SQL Server 2000
Stored Procedure parameters in the OnClick event of an Option Group.
The Option Group lives on an Access 2000 ADP form.
...
|
by: CSDunn |
last post by:
Hello,
I have an MS Access 2003 Project form in which I need to set the value
of a textbox named 'TestGrade' to the value present after the update
of a combo box currently named 'Combo6'. The data...
|
by: John Kirksey |
last post by:
I have a page that uses an in-place editable DataGrid that supports sorting and paging. EnableViewState is turned ON. At the top of the page are several search fields that allow the user to filter...
|
by: jeguillo |
last post by:
I am trying to retrieve the text within each cell in a datagrid in
order to change the color of each cell, depending on the value within
that cell.
This works fine on the cells that are bound...
|
by: Dabbler |
last post by:
I'm using an ObjectDataSource with a stored procedure and am getting the
following error when trying to update (ExecuteNonQuery):
System.Data.SqlClient.SqlException: Procedure or Function...
|
by: oshiko |
last post by:
Hi All,
Below is my SQL Statement,
Select Text, Value, Status from Tables
I want to mark as a checked when the Status is true, how do I achieve this
during databinding?
Thank you very...
|
by: shumaker |
last post by:
I have a combobox that is very much like the one found in the RSS
project here:
http://msdn.microsoft.com/vstudio/express/visualCSharp/learning/
My projectNameComboBox basically is filled with a...
|
by: Peter Newman |
last post by:
vb.net 2003 Sql 2005
Ive been banging my head against a brick wall for the last day trying to
figure out what im doing wrong
I have a Dataset and a Form with text boxes that are bound to the...
|
by: Kavya |
last post by:
int main (){
int a={{1,2,3},{4,5,6}};
int (*ptr)=a;
/* This should be fine and give 3 as output*/
printf("%d\n",(*ptr));
++ptr;
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |