Select statement - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select statement

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-06-08 : 07:12:26

Hi,

How can I convert the following access select statement to have same results if I am using sql server 2005..

SELECT tblDuplicateData.LoadRef, Format([tblDuplicateData].[LoadRef],"000") AS Ref, tblDuplicateData.In_ImpFile AS Imported, 
tblDuplicateData.FileDuplicates AS ImpDup, tblDuplicateData.Duplicates AS VERDup, IIf([PatCategory] Is Null,
"No_Category",[PatCategory]) AS Category, Count(tblProjectPatients.ProjectPatientID) AS Pats 
FROM tblDuplicateData LEFT JOIN tblProjectPatients ON tblDuplicateData.LoadRef = tblProjectPatients.LoadRef GROUP BY tblDuplicateData.LoadRef, 
Format([tblDuplicateData].[LoadRef],"000"), tblDuplicateData.In_ImpFile, tblDuplicateData.FileDuplicates, tblDuplicateData.Duplicates, 
IIf([PatCategory] Is Null,"No_Category",[PatCategory]), tblDuplicateData.ProjectID HAVING 
(((tblDuplicateData.ProjectID)=[forms]![frmProjectSETUP]![lstProjects])) ORDER BY tblDuplicateData.LoadRef DESC;

Thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-06-08 : 07:39:30

[code]
SELECT tblDuplicateData.LoadRef
, Format([tblDuplicateData].[LoadRef],'000') AS Ref
, tblDuplicateData.In_ImpFile AS Imported,
tblDuplicateData.FileDuplicates AS ImpDup
, tblDuplicateData.Duplicates AS VERDup
, ISNULL([PatCategory],'No_Category') AS Category
, Count(tblProjectPatients.ProjectPatientID) AS Pats
FROM tblDuplicateData
LEFT JOIN tblProjectPatients ON tblDuplicateData.LoadRef = tblProjectPatients.LoadRef
GROUP BY tblDuplicateData.LoadRef,
Format([tblDuplicateData].[LoadRef],'000')
, tblDuplicateData.In_ImpFile
, tblDuplicateData.FileDuplicates
, tblDuplicateData.Duplicates,
ISNULL([PatCategory],'No_Category') AS Category
, tblDuplicateData.ProjectID

HAVING
(((tblDuplicateData.ProjectID)=[forms]![frmProjectSETUP]![lstProjects]))

ORDER BY tblDuplicateData.LoadRef DESC;
[/code]

I'm not sure about the HAVING clause as SQL doesn't have forms.

Jim

Everyday I learn something that somebody else already knew

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-06-08 : 07:49:33

Thank you

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-06-08 : 08:35:24

This code below gave me an error;

Msg 156, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'AS'.

Any help please


SELECT tblDuplicateData.LoadRef AS Ref
    , tblDuplicateData.In_ImpFile AS Imported 
    , tblDuplicateDate.LoadDate
  , ISNULL([PatCategory],'No_Category') AS Category
, Count(tblProjectPatients.ProjectPatientID) AS Pats 
FROM       tblDuplicateData 
LEFT JOIN tblProjectPatients ON tblDuplicateData.LoadRef = tblProjectPatients.LoadRef 
GROUP BY tblDuplicateData.LoadRef
, tblDuplicateData.In_ImpFile 
, tblDuplicateData.ProjectID 
, ISNULL([PatCategory],'No_Category') AS Category
ORDER BY tblDuplicateData.LoadRef DESC;

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-06-08 : 08:38:13

Remove AS Category in the GROUP BY Clause

Madhivanan

Failing to plan is Planning to fail

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2010-06-08 : 08:43:57

Tried this and received the following error

Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "tblDuplicateDate.LoadDate" could not be bound.
Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "tblDuplicateDate.LoadDate" could not be bound.



SELECT tblDuplicateData.LoadRef AS Ref
    , tblDuplicateData.In_ImpFile AS Imported 
    , tblDuplicateDate.LoadDate As LoadDate
  , ISNULL([PatCategory],'No_Category') AS Category
, Count(tblProjectPatients.ProjectPatientID) AS Pats 
FROM       tblDuplicateData 
LEFT JOIN tblProjectPatients ON tblDuplicateData.LoadRef = tblProjectPatients.LoadRef 
GROUP BY tblDuplicateData.LoadRef
, tblDuplicateData.In_ImpFile
,tblDuplicateDate.LoadDate  
, tblDuplicateData.ProjectID 
, ISNULL([PatCategory],'No_Category') 
ORDER BY tblDuplicateData.LoadRef DESC;

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-06-08 : 08:57:47

Check your sql. You have used used "tblDuplicateDate" instead of "tblDuplicateData".
Also, Use alias instead of table names.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2010-06-08 : 09:00:21

[code]SELECT
A.LoadRef AS REF ,
A.In_ImpFile AS Imported ,
A.LoadDate AS LoadDate ,
ISNULL([PatCategory],'No_Category') AS Category ,
COUNT(B.ProjectPatientID) AS Pats
FROM
tblDuplicateData A
LEFT JOIN tblProjectPatients B
ON A.LoadRef = B.LoadRef
GROUP BY
A.LoadRef ,
A.In_ImpFile ,
A.LoadDate ,
A.ProjectID ,
ISNULL([PatCategory],'No_Category')
ORDER BY
A.LoadRef DESC;[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources