Display 0 count records in Jet SQL 1

[pyttviper]

i am running this query

SELECT Asset.Identifier, Asset.Status, Asset.Notes, Count(*) AS NumberO
FROM Asset INNER JOIN AssetCoordinate ON Asset.dwKey = AssetCoordinate.AssetKey
WHERE (((Int([AssetCoordinate].[AssetCoordinateFlags]/16)-2*Int([AssetCoordinate].[AssetCoordinateFlags]/32))=1) AND ((AssetCoordinate.DateTime)>Now()-28))
GROUP BY Asset.Identifier, Asset.Status, Asset.Notes;

the result set i get is
Indentifier Status Notes NumberO
123132 2 NRF 1
2342342 1 fef 2


what i want is to show 0 count records in Jet SQL
i know the records are there using T-SQL Group By All

 

[tlbroadbent]

Please explain what you mean. I don't understand what you mean by "show 0 count records in Jet SQL." Do you only want to show records with a count of zero? Is that your meaning? If so, what do you want to count?

The query you've created will never return a zero count. Zero count would indicate no record exists and if that is so then no record will be returned.

You say you "know the records are there using T-SQL Group By All." What do you mean by that?

Do you mean to say that you have a T-SQL query that works but the Jet-SQL query doesn't work? If this is so, could you post the T-SQL query? Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[pyttviper]

SELECT Identifier, Count(*) AS 'Number Of Occurrences', Notes, Status
FROM AssetCoordinate INNER JOIN Asset ON AssetCoordinate.AssetKey = Asset.dwKey
WHERE (AssetCoordinate.DateTime > DATEADD(day, - 28, GETDATE())) AND
(AssetCoordinate.Latitude > 0) AND
(((FLOOR(AssetCoordinate.AssetCoordinateFlags/16)-2*FLOOR(AssetCoordinate.AssetCoordinateFlags/32)) = 1) OR
(Asset.Status = 5)) And
((NOT(Notes LIKE '%N/C%')) OR (Notes IS NULL)) and
((NOT(Notes LIKE '%EOF%')) or (Notes IS NULL))

GROUP BY ALL Identifier, Notes, Status
ORDER BY 'Number Of Occurrences', Notes, Identifier ASC

This is the T-SQL query that I run.

It displays all records even the ones that have a 0 count of records in the linked table. THis is accomplished by the group by all.

Identifier Status Notes Number of Occurrences
123132 2 NRF 0
2342342 1 fef 0
2342342 1 fef 1
2342342 1 fef 1


dan0

 

[tlbroadbent]

Thanks for the information on GROUP BY ALL. I have never used that in T-SQL. I've not seen an equivalent in Access. Apparently, you've tried to find it. You should be able to get what you want with a combination of LEFT JOIN and a subquery.

SELECT
Asset.Identifier,
Asset.Status,
Asset.Notes,
Count(q.AssetKey) AS NumberO
FROM Asset
LEFT JOIN
(SELECT AssetKey From AssetCoordinate
WHERE Int([AssetCoordinate].[AssetCoordinateFlags]/16) -
2*Int([AssetCoordinate].[AssetCoordinateFlags]/32))=1
AND AssetCoordinate.DateTime>Now()-28) as q)
ON Asset.dwKey = q.AssetKey
GROUP BY Asset.Identifier, Asset.Status, Asset.Notes;

NOTE: Count the AssetKey rather than '*'. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[pyttviper]

I understand the logic but access doesn't like the from clause, although i think it is awesome. it give me a syntax error in the from clause and then points to the select word in the phrase???

i am trying to rewrite it currently

dan0

 

[tlbroadbent]

The query has an error - darn parentheses! I apologize for theat.

SELECT
Asset.Identifier,
Asset.Status,
Asset.Notes,
Count(q.AssetKey) AS NumberO
FROM Asset
LEFT JOIN
(SELECT AssetKey From AssetCoordinate
WHERE ((Int([AssetCoordinate].[AssetCoordinateFlags]/16) -
2*Int([AssetCoordinate].[AssetCoordinateFlags]/32)) = 1)
AND (AssetCoordinate.DateTime>Now()-28)) as q
ON Asset.dwKey = q.AssetKey
GROUP BY Asset.Identifier, Asset.Status, Asset.Notes;
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[pyttviper]

Darn.....

it still gives the Select error in the from clause. could it be that Access 97 is the problem?



dan0

 

[tlbroadbent]

You were correct. Access 97 didn't like the query. I found these problems in Access 97.

1) Access doesn't like the brackets around the table and column names in the subquery.
2) It required brackets around the subquery with a period after the query before the 'AS q' clause.
3) It doesn't like the INNER JOIN syntax with the subquery. I reverted to the older syntax.

I also added an alias for the Asset table. I don't think that was needed, though.

SELECT
a.Identifier, a.Status, a.Notes,
Count(q.AssetKey) AS NumberO
FROM Asset AS a,
[SELECT AssetKey FROM AssetCoordinate
WHERE Int(AssetCoordinate.AssetCoordinateFlags/16) -
2*Int(AssetCoordinate.AssetCoordinateFlags/32) = 1
AND AssetCoordinate.DateTime>Now()-28]. AS q
WHERE ((([a].[dwKey])=[q].[AssetKey]))
GROUP BY a.Identifier, a.Status, a.Notes; Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.