Omitting Duplicates

[dwalsh77]

I have 4 tables: (1) Company (2) Employees (3) Products and (4) Transactions.
On a yearly basis, each company has mulitple employees attend seminars from
which they purchase some form of product from whoever is hosting the event.
(i.e. Company A sends Employee C to an event in which he/she purchases
products 1 and 2.
At the end of the event, a summary is made which summarizes the total amount
of products purchased, which in the past has served the purpose of indicating
how many employees attend the event. BUT, now some employees from Company A
that purchase products 1 & 2 should only be counted as 1 instead of 2 in the
summary. I still need a summary of the products, but now is there a way to
omit duplicates if Employee B from Company A has more than 1 product
purchased. (to get a representation of both the total products and total # of
employees at the event).

Thanks,

 

[Tim Ferguson]

At the end of the event, a summary is made which summarizes the total
amount of products purchased, which in the past has served the purpose
of indicating how many employees attend the event.

This has to be one of the best examples of why relational databases are
better than hierarchical ones that I have come across for a long time...
and it's not (apparently) even a made up one!

If Eric from Acme doesn't actually purchase anything, does that mean that
Acme was not represented at the seminar at all?

BUT, now some
employees from Company A that purchase products 1 & 2 should only be
counted as 1 instead of 2 in the summary.

SELECT DISTINCT Employees.CompanyID,
Employees.FullName,
Transactions.HostedBy
FROM Transactions LEFT JOIN Employees
ON Transactions.EmployeeID = Employees.EmpID
WHERE Transactions.HostedBy = "National Widgets"


Hope that helps


Tim F