Converting this Access SQL statement for MS SQL server (1 Viewer)

Kellen · Aug 29, 2014

This statement works on Access, however it turns out that I also need to replicate this line for a query on MS SQL server. DCount isn't a function on SQL server and the Count function only supports one argument.

How can I convert this line into MS SQL server?

Code:

Mfg_Combined" & DCount("*", "tblRecords", "ID <= " &  [ID] & " AND [Part_No] = '" & [Part_No] & "'") AS Mfg_Header

I'm trying to create a horizontal data query in SQL. If I get this to work I must make a cross tab query in SQL.

Thanks in advance.

pbaldy · Aug 29, 2014

This is for a single field in a larger query? The equivalent would be a sub query, which would also work in Access. Along the lines of:

SELECT Field1, (SELECT Count(*) FROM tblRecords AS Alias WHERE Alias.ID <= TableName.[ID] AND Alias.[Part_No] = TableName.[Part_No]) As NewField
FROM TableName

Kellen · Aug 29, 2014

Thanks so much.

pbaldy · Aug 29, 2014

No problem. A DCount() is essentially a subquery anyway. Both can slow down the main query considerably.

Kellen · Aug 29, 2014

I'm also having trouble creating a crosstab query. Every time I attempt to use the pivot function SQL server declares my alias columns to be invalid.

My code is:
SELECT
PartNumber,
manufacturerPartNumber + ': ' + manufacturerName AS [Mfg_Combined],
(SELECT Count(*) FROM dbo.ManufacturerPartNumbers AS MfgSubQuery WHERE MfgSubQuery.mpnAutoId <= dbo.ManufacturerPartNumbers.[mpnAutoId] AND MfgSubQuery.[PartNumber] = dbo.ManufacturerPartNumbers.[PartNumber]) As Mfg_Header
FROM
dbo.ManufacturerPartNumbers

Converting this Access SQL statement for MS SQL server (1 Viewer)

Kellen

Registered User.

pbaldy

Wino Moderator

Kellen

Registered User.

pbaldy

Wino Moderator

Kellen

Registered User.

Similar threads

Users who are viewing this thread