Convert query from Access to SQL

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

SQL Server 2005 Forums

Transact-SQL (2005)

Convert query from Access to SQL

Author

Topic

nonito
Starting Member

8 Posts

Posted - 2008-08-26 : 07:51:14

I have this query in access and would like to use it in SQL Server. Can someone please guide me in the right direction?

Thanks guys.

SELECT qryCaseSelectionsbyRegion.RO, qryCaseSelectionsbyRegion.[SAMPLE DATE], qryCaseSelectionsbyRegion.Selected, IIf(qrycasecompletionsbyregion!COMPLETED,qrycasecompletionsbyregion!COMPLETED,0) AS COMPLETED, IIf(qrycaseexclusionsbyregion!EXCLUDED,qrycaseexclusionsbyregion!EXCLUDED,0) AS EXCLUDED, [SELECTED]-[COMPLETED]-[EXCLUDED] AS PENDING
FROM (qryCaseSelectionsbyRegion LEFT JOIN qryCaseCompletionsbyRegion ON (qryCaseSelectionsbyRegion.[SAMPLE DATE] = qryCaseCompletionsbyRegion.[SAMPLE DATE]) AND (qryCaseSelectionsbyRegion.RO = qryCaseCompletionsbyRegion.RO)) LEFT JOIN qryCaseExclusionsbyRegion ON (qryCaseCompletionsbyRegion.[SAMPLE DATE] = qryCaseExclusionsbyRegion.[SAMPLE DATE]) AND (qryCaseCompletionsbyRegion.RO = qryCaseExclusionsbyRegion.RO)
GROUP BY qryCaseSelectionsbyRegion.RO, qryCaseSelectionsbyRegion.[SAMPLE DATE], qryCaseSelectionsbyRegion.Selected, IIf(qrycasecompletionsbyregion!COMPLETED,qrycasecompletionsbyregion!COMPLETED,0), IIf(qrycaseexclusionsbyregion!EXCLUDED,qrycaseexclusionsbyregion!EXCLUDED,0), [SELECTED]-[COMPLETED]-[EXCLUDED]
HAVING (((qryCaseSelectionsbyRegion.RO)=[Pleae Enter the 2 digit Region Number:]));

Note: In SQL database SAMPLE DATE is sampledate. I'm also using ColdFusion.

Thanks guys

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-08-26 : 08:47:24

[code]IIf(expr, truepart, falsepart)[/code]changes to

[code]CASE WHEN expr THEN truepart ELSE falsepart END[/code]is there anything else you need to know?

Bjoern

(54224 row(s) affected)

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

nonito
Starting Member

8 Posts

Posted - 2008-08-26 : 08:55:40

CASE WHEN qrycasecompletionsbyregion!COMPLETED THEN qrycasecompletionsbyregion!COMPLETED ELSE 0 END

This should work?

bjoerns
Posting Yak Master

154 Posts

Posted - 2008-08-26 : 08:57:35

Oh I'm sorry. Didn't pay enough attention.

nonito
Starting Member

8 Posts

Posted - 2008-08-26 : 09:01:38

I tried that and it didn't work.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-26 : 09:53:32

[code]SELECT DISTINCT s.RO,
s.[Sample Date],
s.Selected,
COALESCE(c.Completed, 0) AS Completed,
COALESCE(e.Excluded, 0) AS Excluded,
s.Selected - COALESCE(c.Completed, 0) - COALESCE(e.Excluded, 0) AS PENDING
FROM qryCaseSelectionsbyRegion AS s
LEFT JOIN qryCaseCompletionsbyRegion AS c ON c.[SAMPLE DATE] = s.[SAMPLE DATE]
AND c.RO = s.RO
LEFT JOIN qryCaseExclusionsbyRegion AS e ON e.[SAMPLE DATE] = c.[SAMPLE DATE]
AND e.RO = c.RO
WHERE s.RO = @ro[/code]However, I am very convinced this can be made even simpler.
I have a hunch that the three queries are built on the same base table(s).

E 12°55'05.25"
N 56°04'39.16"

nonito
Starting Member

8 Posts

Posted - 2008-08-26 : 10:04:50

I get this error:
Encountered "COALESCE. Incorrect Select List, Incorrect select column,

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-26 : 11:57:56

Are you converting to MICROSOFT SQL Server 2005?

E 12°55'05.25"
N 56°04'39.16"

nonito
Starting Member

8 Posts

Posted - 2008-08-26 : 12:04:02

Yes

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-27 : 01:18:55

And from where are you running the query?

E 12°55'05.25"
N 56°04'39.16"

nonito
Starting Member

8 Posts

Posted - 2008-08-27 : 07:48:31

ColdFusion, Thanks but I just split the query in three and got it to work.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-27 : 08:39:41

quote:
Originally posted by nonito

ColdFusion, Thanks but I just split the query in three and got it to work.

Show us the modified query

Madhivanan

Failing to plan is Planning to fail

nonito
Starting Member

8 Posts

Posted - 2008-08-27 : 09:10:19

Here you go:

<cfquery name="getSelected" datasource="#dsn#">
SELECT ro, sampledate, COUNT(ssn) AS Selected
FROM tblNonreciptData
WHERE ro = '01'
GROUP BY ro, sampleDate
</cfquery>

<cfquery name="getCompleted" datasource="#dsn#">
SELECT ro, sampledate, COUNT(ssn) AS Completed
FROM tblNonreciptData
WHERE compdate IS NOT NULL AND sampledate='#getSelected.sampledate#' AND ro='#getSelected.RO#'
GROUP BY ro, sampledate
</cfquery>

<cfquery name="getExcluded" datasource="#dsn#">
SELECT ro, sampledate, COUNT(ssn) AS Excluded
FROM tblNonreciptData
WHERE ex IS Not Null AND compdate Is Not Null AND sampleDate='#getSelected.sampledate#' AND ro='#getSelected.RO#'
GROUP BY ro, sampleDate
<cfquery>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources