Access97 LEFT JOIN SELECT

Question

I'm stuck with some access97 sql,

SELECT Master.Text, Sub.Cnt
FROM Master
INNER JOIN [
SELECT Number, COUNT(*) As Cnt FROM SubTable GROUP BY Number]. AS Sub
ON Master.Number=Sub.Number
WHERE Master.Number=1;

Behaves as I would expect however Where I need to square bracket the 'SubTable' Name I recieve an error in FROM clause highlighting GROUP

SELECT Master.Text, Sub.Cnt
FROM Master
INNER JOIN [
SELECT Number, COUNT(*) As Cnt FROM [Sub Table] GROUP BY Number]. AS Sub
ON Master.Number=Sub.Number
WHERE Master.Number=1;

Can anyone point me in the right direction?

Thanks

Gordon Linoff · Answer 1 · 2014-07-17T13:52:59.730

0

Square brackets look really strange in the query. Try using regular parentheses for the subquery:

SELECT Master.Text, Sub.Cnt
FROM Master INNER JOIN 
     (SELECT Number, COUNT(*) As Cnt
      FROM [Sub Table]
      GROUP BY Number
     ) AS Sub
     ON Master.Number = Sub.Number
WHERE Master.Number = 1;

EDIT:

You can rewrite this query as:

SELECT Master.Text, COUNT(*) as Cnt
FROM Master INNER JOIN 
     [Sub Table] as sub
     ON Master.Number = Sub.Number
WHERE Master.Number = 1
GROUP BY Number;

edited Jul 17 '14 at 13:52

answered Jul 17 '14 at 13:37

Gordon Linoff

1,242,037
58
646
786

The square brackets are a weird thing that the Access query designer does. – Taryn Jul 17 '14 at 13:42
@Gordon Square brackets are the correct notation for a97, the initial query functions correctly, it is in situations where there is a space in the tablename the problem occurs – robpws Jul 17 '14 at 13:43
@robpws . . . Yucch. Why are you using software that was out of date over a decade ago? – Gordon Linoff Jul 17 '14 at 13:53
@GordonLinoff It's an old commercial product which apparently doesn't warrant the time to update – robpws Jul 17 '14 at 13:55

Access97 LEFT JOIN SELECT

1 Answers1