It is necessary to display a list of excursions indicating the number of visited halls.

The data scheme is as follows: enter image description here

It is impossible to do so that I think correctly, i.e. if one hall was visited twice, then only one was considered.

Update

SELECT Экскурсии.[Π’Π΅ΠΌΠ° экскурсии], COUNT([Π—Π°Π»Ρ‹ Π³Π°Π»Π΅Ρ€Π΅ΠΈ].[НомСр Π·Π°Π»Π°]) AS [Count-НомСр Π·Π°Π»Π°] FROM [Π—Π°Π»Ρ‹ Π³Π°Π»Π΅Ρ€Π΅ΠΈ] INNER JOIN (ΠšΠ°Ρ€Ρ‚ΠΈΠ½Ρ‹ INNER JOIN (Экскурсии INNER JOIN [Π‘ΠΎΠ΄Π΅Ρ€ΠΆΠ°Π½ΠΈΠ΅ экскурсий] ON Экскурсии.[Код экскурсии] = [Π‘ΠΎΠ΄Π΅Ρ€ΠΆΠ°Π½ΠΈΠ΅ экскурсий].[Код экскурсии]) ON ΠšΠ°Ρ€Ρ‚ΠΈΠ½Ρ‹.[Код ΠΊΠ°Ρ€Ρ‚ΠΈΠ½Ρ‹] = [Π‘ΠΎΠ΄Π΅Ρ€ΠΆΠ°Π½ΠΈΠ΅ экскурсий].[Код ΠΊΠ°Ρ€Ρ‚ΠΈΠ½Ρ‹]) ON [Π—Π°Π»Ρ‹ Π³Π°Π»Π΅Ρ€Π΅ΠΈ].[НомСр Π·Π°Π»Π°] = ΠšΠ°Ρ€Ρ‚ΠΈΠ½Ρ‹.[НомСр Π·Π°Π»Π°] GROUP BY Экскурсии.[Π’Π΅ΠΌΠ° экскурсии];
  • whether your request would be written or something ... I suspect that it will be necessary to replace the usual count with count(distinct Π·Π°Π») - Mike
  • And what, really count (distinct Nazal) did not give the desired result? - Mike
  • @Mike, I could be wrong, but access does not seem to support count(distinct ...) . - i-one
  • @Arrsenal, it's better to add your code to the question, not to the comment. - i-one
  • @ i-one Yes, I see. It did not occur to me that something SQL compatible could not support this. recommend the creepy perversion ... stackoverflow.com/questions/11880199/… - Mike

1 answer 1

 SELECT [Π’Π΅ΠΌΠ° экскурсии], COUNT([НомСр Π·Π°Π»Π°]) AS [Count-НомСр Π·Π°Π»Π°] FROM( SELECT DISTINCT Экскурсии.[Π’Π΅ΠΌΠ° экскурсии], [Π—Π°Π»Ρ‹ Π³Π°Π»Π΅Ρ€Π΅ΠΈ].[НомСр Π·Π°Π»Π°] FROM [Π—Π°Π»Ρ‹ Π³Π°Π»Π΅Ρ€Π΅ΠΈ] INNER JOIN (ΠšΠ°Ρ€Ρ‚ΠΈΠ½Ρ‹ INNER JOIN (Экскурсии INNER JOIN [Π‘ΠΎΠ΄Π΅Ρ€ΠΆΠ°Π½ΠΈΠ΅ экскурсий] ON Экскурсии.[Код экскурсии] = [Π‘ΠΎΠ΄Π΅Ρ€ΠΆΠ°Π½ΠΈΠ΅ экскурсий].[Код экскурсии]) ON ΠšΠ°Ρ€Ρ‚ΠΈΠ½Ρ‹.[Код ΠΊΠ°Ρ€Ρ‚ΠΈΠ½Ρ‹] = [Π‘ΠΎΠ΄Π΅Ρ€ΠΆΠ°Π½ΠΈΠ΅ экскурсий].[Код ΠΊΠ°Ρ€Ρ‚ΠΈΠ½Ρ‹]) ON [Π—Π°Π»Ρ‹ Π³Π°Π»Π΅Ρ€Π΅ΠΈ].[НомСр Π·Π°Π»Π°] = ΠšΠ°Ρ€Ρ‚ΠΈΠ½Ρ‹.[НомСр Π·Π°Π»Π°] )as T GROUP BY [Π’Π΅ΠΌΠ° экскурсии]