May 4 2012, 01:49 AM
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 | email@example.com | Video 2
Joe Smith | firstname.lastname@example.org | Video 3
Joe Smith | email@example.com | Video 4
Joe Smith | firstname.lastname@example.org | 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.
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.
Using RJD's demo you could get the same result with a crosstab query.
TRANSFORM IIf(Count(MyTable.Email)>0,"Yes","") AS CountOfEmail
GROUP BY MyTable.PersonName
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.
May 4 2012, 07:47 AM
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.
May 7 2012, 06:14 AM
Simply brilliant RJD - thanks.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here