Distinct values

[Voyager221]

I have three related tables; the primary tblFilm contains info about each
produced film. The related tblPresenter contains info about the trainers in
the film. The related tblCode contains info about the viewing codes that are
placed in each film. A query that selects one film with 2 trainers and 4
codes returns 8 records because each trainer record returns with the 4 code
records. I am making a report in which I need to see the film info, the 2
trainer's info but I only need to count the number of codes in the film (4).
Is there a way?

Thank you,

 

[Sylvain Lafontaine]

You must use a Group By but this require that each non-grouped selected or
counted value be mentionned in the Group By (but you could also use the
First() or Last() function but these are not standard sql functions). For
example:

select a.*, count (B.IdA)
from (select 1 as IdA, 'Film1' as NameA union all select 2, 'Film2') as A
inner join

(select 1 as IdA, 'Hi' as CommentB
union all
select 1, 'Hello'
union all
select 2, 'Bye') as B on A.IdA = B.IdA

Group By A.IdA, A.NameA


Another way would be to put the Group By inside the subquery:

select A.*, B.Cnt
from (select 1 as IdA, 'Film1' as NameA union all select 2, 'Film2') as A
inner join

(Select C.IdA, Count (*) as Cnt from

(select 1 as IdA, 'Hi' as CommentB
union all
select 1, 'Hello'
union all
select 2, 'Bye') as C Group by C.IdA

) as B on A.IdA = B.IdA


Finally, a third method would be to put the counting in a subquery directly
in the Select part:

select a.*, (Select count(*) from (select 1 as IdA, 'Hi' as CommentB
union all
select 1, 'Hello'
union all
select 2, 'Bye') as B where B.IdA = A.IdA) as Cnt
from (select 1 as IdA, 'Film1' as NameA union all select 2, 'Film2') as A

I've made these examples with SQL-Server but they should run fine under
Access. In any case, you must adapt them to your own database.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)