Hello all,

I have a two tables one which contains personal data about people and another which has a list like this

Fred Bloggs | Fbloggs@email.com| Video 1
Fred Bloggs | Fbloggs@email.com| Video 2
Fred Bloggs | Fbloggs@email.com| Video 5
Fred Bloggs | Fbloggs@email.com| Video 6
Joe Smith | jsmith@email.com | Video 2
Joe Smith | jsmith@email.com | Video 3
Joe Smith | jsmith@email.com | Video 4
Joe Smith | jsmith@email.com | Video 6

And what I need is to join these two table together and produce a report that looks like this

Name | Video 1 | Video 2 | Video 3 | Video 4 | Video 5 | Video 6
Fred Bloggs Yes - - - Yes Yes
Joe Smith - Yes Yes Yes - Yes


Is this possible?

Thanks in advance for any help.
Ken

Hi Ken:



See the demo attached. You might want to include Access version in the future - that is usually helpful. I included an .mdb as well as an .accdb (A2007) in the zip file.

Given your table layout in your post I suspect you might have design issues, but since I don't know any more about your database I'll skip that. And I made up field names since they were not included.

See the query for a method to choose "Yes" if a video number exists and the use of the Totals query approach.

HTH
Joe

Using RJD's demo you could get the same result with a crosstab query.


TRANSFORM IIf(Count(MyTable.Email)>0,"Yes","") AS CountOfEmail
SELECT MyTable.PersonName
FROM MyTable
GROUP BY MyTable.PersonName
PIVOT MyTable.VideoNumber;

Hi Ace: If all the video numbers are present, the crosstab is certainly a good way to go. But if a video number is missing - say Video 5 - that will not appear in the crosstab. But it will appear using the Totals approach in my demo. I usually use the Totals approach when there is a limited number of options (video numbers in this case) and I want all options to show in the query even if there are no records for them.

Regards,
Joe

Thanks RJD for this excellent answer. I have attached the database as is without the company data - execpt for me!

Would your suggestion work here too. Sorry for such a dumb question.

Hi again Ken: See if this addition to your database does what you are looking for. See the query: VideosViewedByPersonName. You can, of course, attach any other data by linking the "Org chart" or other table(s).

Also please note that the "OpenText BPS storyline: streaming video" description has two spaces after BPS. I accommodated this in the query, but you might want to recheck how you want that written, and if you take out the space, change the query as well.

HTH
Joe

Simply brilliant RJD - thanks.