Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Need help converting query from access 2000 to SQL...long statement

Status
Not open for further replies.
QJR

QJR

IS-IT--Management
Jan 18, 2006
1
US
Long story short just got a huge project dumped onto me and i have never coded SQL. Got a couple of the queries done but got to this one and dont know what to do . any help would be greatly appreciated.

Code: 
SELECT ProjectSub.PS_ID, ProjectSub.PS_Name, ProjectSub_Proposal.PSP_ProposalDate, ProjectSub.[PS_#Lots], ProjectSub.PS_SiteArea, ProjectSub.PS_SiteAreaUnits, ProjectSub_Work.PSWk_WOC_ID, ProjectSub_Work.PSWk_Cost, ProjectSub_ProposalDetails.PSP_TaskCode, ProjectSub_ProposalDetails.PSPD_Amount, ProjectSub_ProposalDetails.PSPD_Rate, [Forms]![Switchboard]![Surveying_Reports].[Form]![Type21] AS Expr1, ProjectSub.PS_SFD, ProjectSub.PS_SFA, ProjectSub.PS_MFRental, ProjectSub.PS_MFCondo, ProjectSub.PS_ResOther, ProjectSub.PS_Industrial, ProjectSub.PS_Retail, ProjectSub.PS_Office, ProjectSub.PS_CommOther
FROM ((ProjectSub INNER JOIN ProjectSub_Proposal ON ProjectSub.PS_ID = ProjectSub_Proposal.PSP_PS_ID) INNER JOIN ProjectSub_Work ON ProjectSub.PS_ID = ProjectSub_Work.PSWk_PS_ID) INNER JOIN ProjectSub_ProposalDetails ON ProjectSub_Proposal.PSP_ID = ProjectSub_ProposalDetails.PSPD_PSP_ID
WHERE (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND ((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And (ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="SFD") AND ((ProjectSub.PS_SFD)=Yes) AND ((ProjectSub.PS_SFA)=No) AND ((ProjectSub.PS_MFRental)=No) AND ((ProjectSub.PS_MFCondo)=No) AND ((ProjectSub.PS_ResOther)=No) AND ((ProjectSub.PS_Industrial)=No) AND ((ProjectSub.PS_Retail)=No) AND ((ProjectSub.PS_Office)=No) AND ((ProjectSub.PS_CommOther)=No)) OR (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND ((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And (ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="SFA") AND ((ProjectSub.PS_SFD)=No) AND ((ProjectSub.PS_SFA)=Yes) AND ((ProjectSub.PS_MFRental)=No) AND ((ProjectSub.PS_MFCondo)=No) AND ((ProjectSub.PS_ResOther)=No) AND ((ProjectSub.PS_Industrial)=No) AND ((ProjectSub.PS_Retail)=No) AND ((ProjectSub.PS_Office)=No) AND ((ProjectSub.PS_CommOther)=No)) OR (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND ((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And (ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="MFRental") AND ((ProjectSub.PS_SFD)=No) AND ((ProjectSub.PS_SFA)=No) AND ((ProjectSub.PS_MFRental)=Yes) AND ((ProjectSub.PS_MFCondo)=No) AND ((ProjectSub.PS_ResOther)=No) AND ((ProjectSub.PS_Industrial)=No) AND ((ProjectSub.PS_Retail)=No) AND ((ProjectSub.PS_Office)=No) AND ((ProjectSub.PS_CommOther)=No)) OR (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND ((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And (ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="MFCondo") AND ((ProjectSub.PS_SFD)=No) AND ((ProjectSub.PS_SFA)=No) AND ((ProjectSub.PS_MFRental)=No) AND ((ProjectSub.PS_MFCondo)=Yes) AND ((ProjectSub.PS_ResOther)=No) AND ((ProjectSub.PS_Industrial)=No) AND ((ProjectSub.PS_Retail)=No) AND ((ProjectSub.PS_Office)=No) AND ((ProjectSub.PS_CommOther)=No)) OR (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND ((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And (ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="ResOther") AND ((ProjectSub.PS_SFD)=No) AND ((ProjectSub.PS_SFA)=No) AND ((ProjectSub.PS_MFRental)=No) AND ((ProjectSub.PS_MFCondo)=No) AND ((ProjectSub.PS_ResOther)=Yes) AND ((ProjectSub.PS_Industrial)=No) AND ((ProjectSub.PS_Retail)=No) AND ((ProjectSub.PS_Office)=No) AND ((ProjectSub.PS_CommOther)=No)) OR (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND ((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And (ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="Industrial") AND ((ProjectSub.PS_SFD)=No) AND ((ProjectSub.PS_SFA)=No) AND ((ProjectSub.PS_MFRental)=No) AND ((ProjectSub.PS_MFCondo)=No) AND ((ProjectSub.PS_ResOther)=No) AND ((ProjectSub.PS_Industrial)=Yes) AND ((ProjectSub.PS_Retail)=No) AND ((ProjectSub.PS_Office)=No) AND ((ProjectSub.PS_CommOther)=No)) OR (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND ((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And (ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="Retail") AND ((ProjectSub.PS_SFD)=No) AND ((ProjectSub.PS_SFA)=No) AND ((ProjectSub.PS_MFRental)=No) AND ((ProjectSub.PS_MFCondo)=No) AND ((ProjectSub.PS_ResOther)=No) AND ((ProjectSub.PS_Industrial)=No) AND ((ProjectSub.PS_Retail)=Yes) AND ((ProjectSub.PS_Office)=No) AND ((ProjectSub.PS_CommOther)=No)) OR (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND ((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And (ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="Office") AND ((ProjectSub.PS_SFD)=No) AND ((ProjectSub.PS_SFA)=No) AND ((ProjectSub.PS_MFRental)=No) AND ((ProjectSub.PS_MFCondo)=No) AND ((ProjectSub.PS_ResOther)=No) AND ((ProjectSub.PS_Industrial)=No) AND ((ProjectSub.PS_Retail)=No) AND ((ProjectSub.PS_Office)=Yes) AND ((ProjectSub.PS_CommOther)=No)) OR (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND ((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And (ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="CommOther") AND ((ProjectSub.PS_SFD)=No) AND ((ProjectSub.PS_SFA)=No) AND ((ProjectSub.PS_MFRental)=No) AND ((ProjectSub.PS_MFCondo)=No) AND ((ProjectSub.PS_ResOther)=No) AND ((ProjectSub.PS_Industrial)=No) AND ((ProjectSub.PS_Retail)=No) AND ((ProjectSub.PS_Office)=No) AND ((ProjectSub.PS_CommOther)=Yes)) OR (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND ((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And (ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="All"))
ORDER BY ProjectSub.PS_ID, ProjectSub.PS_Name;

errors i get from query analyzer

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '!'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ProjectSub_Work'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ProjectSub_Work'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ProjectSub_Work'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ProjectSub_Work'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ProjectSub_Work'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ProjectSub_Work'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ProjectSub_Work'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ProjectSub_Work'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ProjectSub_Work'.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near 'ProjectSub_Work'.
 
Sort by date Sort by votes
First off, since all of the errors are coming from the same line, I would split the query up over multiple lines to help pin-point the errors. I have already done this for you, just copy and paste the below code into the query analyzer and run the query, this will give you a better look at where the errors are coming from.

Code: 
SELECT ProjectSub.PS_ID, ProjectSub.PS_Name, ProjectSub_Proposal.PSP_ProposalDate, ProjectSub.[PS_#Lots], 
ProjectSub.PS_SiteArea, ProjectSub.PS_SiteAreaUnits, ProjectSub_Work.PSWk_WOC_ID, ProjectSub_Work.PSWk_Cost, 
ProjectSub_ProposalDetails.PSP_TaskCode, ProjectSub_ProposalDetails.PSPD_Amount, ProjectSub_ProposalDetails.PSPD_Rate, 
[Forms]![Switchboard]![Surveying_Reports].[Form]![Type21] AS Expr1, ProjectSub.PS_SFD, ProjectSub.PS_SFA, 
ProjectSub.PS_MFRental, ProjectSub.PS_MFCondo, ProjectSub.PS_ResOther, ProjectSub.PS_Industrial, ProjectSub.PS_Retail, 
ProjectSub.PS_Office, ProjectSub.PS_CommOther
FROM ((ProjectSub INNER JOIN 
ProjectSub_Proposal ON ProjectSub.PS_ID = ProjectSub_Proposal.PSP_PS_ID) INNER JOIN 
ProjectSub_Work ON ProjectSub.PS_ID = ProjectSub_Work.PSWk_PS_ID) INNER JOIN 
ProjectSub_ProposalDetails ON ProjectSub_Proposal.PSP_ID = ProjectSub_ProposalDetails.PSPD_PSP_ID
WHERE (((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND 
((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And 
(ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND 
(([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="SFD") AND 
((ProjectSub.PS_SFD)=Yes) AND 
((ProjectSub.PS_SFA)=No) AND 
((ProjectSub.PS_MFRental)=No) AND 
((ProjectSub.PS_MFCondo)=No) AND 
((ProjectSub.PS_ResOther)=No) AND 
((ProjectSub.PS_Industrial)=No) AND 
((ProjectSub.PS_Retail)=No) AND 
((ProjectSub.PS_Office)=No) AND 
((ProjectSub.PS_CommOther)=No)) OR 
(((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND 
((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And 
(ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND 
(([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="SFA") AND 
((ProjectSub.PS_SFD)=No) AND 
((ProjectSub.PS_SFA)=Yes) AND 
((ProjectSub.PS_MFRental)=No) AND 
((ProjectSub.PS_MFCondo)=No) AND 
((ProjectSub.PS_ResOther)=No) AND 
((ProjectSub.PS_Industrial)=No) AND 
((ProjectSub.PS_Retail)=No) AND 
((ProjectSub.PS_Office)=No) AND 
((ProjectSub.PS_CommOther)=No)) OR 
(((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND 
((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And 
(ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND (
([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="MFRental") AND 
((ProjectSub.PS_SFD)=No) AND 
((ProjectSub.PS_SFA)=No) AND 
((ProjectSub.PS_MFRental)=Yes) AND 
((ProjectSub.PS_MFCondo)=No) AND 
((ProjectSub.PS_ResOther)=No) AND 
((ProjectSub.PS_Industrial)=No) AND 
((ProjectSub.PS_Retail)=No) AND 
((ProjectSub.PS_Office)=No) AND 
((ProjectSub.PS_CommOther)=No)) OR 
(((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND 
((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And 
(ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND 
(([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="MFCondo") AND 
((ProjectSub.PS_SFD)=No) AND 
((ProjectSub.PS_SFA)=No) AND 
((ProjectSub.PS_MFRental)=No) AND 
((ProjectSub.PS_MFCondo)=Yes) AND 
((ProjectSub.PS_ResOther)=No) AND 
((ProjectSub.PS_Industrial)=No) AND 
((ProjectSub.PS_Retail)=No) AND 
((ProjectSub.PS_Office)=No) AND 
((ProjectSub.PS_CommOther)=No)) OR 
(((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND 
((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And 
(ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND 
(([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="ResOther") AND 
((ProjectSub.PS_SFD)=No) AND 
((ProjectSub.PS_SFA)=No) AND 
((ProjectSub.PS_MFRental)=No) AND 
((ProjectSub.PS_MFCondo)=No) AND 
((ProjectSub.PS_ResOther)=Yes) AND 
((ProjectSub.PS_Industrial)=No) AND 
((ProjectSub.PS_Retail)=No) AND 
((ProjectSub.PS_Office)=No) AND 
((ProjectSub.PS_CommOther)=No)) OR 
(((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND 
((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And 
(ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND 
(([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="Industrial") AND 
((ProjectSub.PS_SFD)=No) AND 
((ProjectSub.PS_SFA)=No) AND 
((ProjectSub.PS_MFRental)=No) AND 
((ProjectSub.PS_MFCondo)=No) AND 
((ProjectSub.PS_ResOther)=No) AND 
((ProjectSub.PS_Industrial)=Yes) AND 
((ProjectSub.PS_Retail)=No) AND 
((ProjectSub.PS_Office)=No) AND 
((ProjectSub.PS_CommOther)=No)) OR 
(((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND 
((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And 
(ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND 
(([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="Retail") AND 
((ProjectSub.PS_SFD)=No) AND 
((ProjectSub.PS_SFA)=No) AND 
((ProjectSub.PS_MFRental)=No) AND 
((ProjectSub.PS_MFCondo)=No) AND 
((ProjectSub.PS_ResOther)=No) AND 
((ProjectSub.PS_Industrial)=No) AND 
((ProjectSub.PS_Retail)=Yes) AND 
((ProjectSub.PS_Office)=No) AND 
((ProjectSub.PS_CommOther)=No)) OR 
(((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND 
((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And 
(ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND 
(([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="Office") AND 
((ProjectSub.PS_SFD)=No) AND 
((ProjectSub.PS_SFA)=No) AND 
((ProjectSub.PS_MFRental)=No) AND 
((ProjectSub.PS_MFCondo)=No) AND 
((ProjectSub.PS_ResOther)=No) AND 
((ProjectSub.PS_Industrial)=No) AND 
((ProjectSub.PS_Retail)=No) AND 
((ProjectSub.PS_Office)=Yes) AND 
((ProjectSub.PS_CommOther)=No)) OR 
(((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND 
((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And 
(ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND 
(([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="CommOther") AND 
((ProjectSub.PS_SFD)=No) AND 
((ProjectSub.PS_SFA)=No) AND 
((ProjectSub.PS_MFRental)=No) AND 
((ProjectSub.PS_MFCondo)=No) AND 
((ProjectSub.PS_ResOther)=No) AND 
((ProjectSub.PS_Industrial)=No) AND 
((ProjectSub.PS_Retail)=No) AND 
((ProjectSub.PS_Office)=No) AND 
((ProjectSub.PS_CommOther)=Yes)) OR 
(((ProjectSub_Work.PSWk_WOC_ID)=[PSP_TaskCode]) AND 
((ProjectSub_ProposalDetails.PSP_TaskCode)=[PSWk_WOC_ID] And 
(ProjectSub_ProposalDetails.PSP_TaskCode)="21") AND 
(([Forms]![Switchboard]![Surveying_Reports].[Form]![Type21])="All"))
ORDER BY ProjectSub.PS_ID, ProjectSub.PS_Name;
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

tekpr00
  • Locked
  • Question
Help with concatenation of BIG integers
Replies
0
Views
163
tekpr00
tekpr00
ChewDoggie
  • Locked
  • Question
Help with Select statement Substring
Replies
2
Views
172
ChewDoggie
ChewDoggie
Rene1024
  • Locked
  • Question
Help with a Bulk insert statement
Replies
1
Views
204
drlex
drlex
petart
  • Locked
  • Question
Problem Date MS SQL Server
Replies
0
Views
165
petart
petart
ItHurtsWhenIThink
  • Locked
  • Question
Using Distinct and count in same query???
Replies
4
Views
205
gk53
gk53

Part and Inventory Search

Sponsor

Back
Top