1

I've got a homework that includes SQL with MS Access I've been trying hard to figure it out but could'nt.. Im just getting an error message.. My code is for a view..

This is the code:

(SELECT Rum, COUNT(DISTINCT(Larare)) AS antal FROM Kurstillfalle
GROUP BY Rum)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
Mikael Yousif
  • 23
  • 2
  • Take a look [here](https://stackoverflow.com/questions/11880199/how-do-i-count-unique-items-in-field-in-access-query) – Maciej Los Mar 24 '19 at 22:00

1 Answers1

1

Access does not support COUNT(DISTINCT columnname) but you can do this:

SELECT 
  t.Rum, 
  COUNT(t.Larare)) AS antal 
FROM (SELECT DISTINCT Rum, Larare FROM Kurstillfalle) AS t 
GROUP BY t.Rum
forpas
  • 160,666
  • 10
  • 38
  • 76
  • Thanks alot, worked!! Can you see anything wrong with the code below? I'm not getting any result in the column student? It should show me the students from any date in year 2010? SELECT DISTINCT student FROM Deltagande WHERE startdatum LIKE '2010%' – Mikael Yousif Mar 24 '19 at 22:13
  • replace `%` with `*` – forpas Mar 24 '19 at 22:16
  • Is there any problem with using intersect in access? Or is the trouble somewhere else? – Mikael Yousif Mar 24 '19 at 23:07
  • There is no intersect in access as far as I know. – forpas Mar 24 '19 at 23:09
  • SELECT L.personnummer, P.namn, L.tjänsterum, P.telefon FROM Person P, Lärare L, Kurstillfälle KT, Rum R WHERE P.personnummer = L.personnummer AND L.personnummer = KT.lärare AND KT.rum = R.id AND R.namn = 'Jupiter' INTERSECT SELECT L.personnummer, P.namn, L.tjänsterum, P.telefon FROM Person P, Lärare L, Kurstillfälle KT, Rum R WHERE P.personnummer = L.personnummer AND L.personnummer = KT.lärare AND KT.rum = R.id AND R.namn = 'Sirius' --------------------------------------------> What's the problem here? I appreciate your help, thank's alot! =) – Mikael Yousif Mar 24 '19 at 23:17
  • As I said if this is code for MS Access, INTERSECT is not supported. – forpas Mar 24 '19 at 23:20