Adding "Count" Totals to Report

[Ludovik]

Hey All,


I'm glad I stumbled upon this site, lots of useful stuff. Anyhow, I'm fairly new to Access and two separate yet related issues. I can't upload the DB since it's hosted online but I'll try to be as descriptive as possible.


Case One

I am trying to make a query that shows how many artists have songs with the word "love" in the title and then make a report that shows a count of the result. This is my query design:



What happens when I run the query though is that each track is counted separately; the results look like:



When what I'm looking for is a total of the count. I've tried for about two/three hours to figure out how to get it to work but I'm obviously missing something obvious.

Any help from the community would be more than appreciated.

[ItsMe]

You do not have an artist id in your track table. This is why you see a count of 1

Build another query. Place dbo_Album and dbo_Track in the query. Make a relationship. Edit the properties of the relationship to show "All of the records in Album". Your goal is to create a SELECT query.

Now place the new query in your Tracks_With_Word_Love_Query. You will have the Artist table and the new query. Create the relationship to Artist ID "One to Many" and try to get your count then.

[Ludovik]

Is there a way to accomplish this by using only one query? For example, adding tables/relationships to my original query such that each artist only shows once? If I understand you correctly, the problem is that in the "dbo_Track" table there is no key to uniquely identify each artist. I don't mean to seem dense or anything.

[ItsMe]

If you want to count the number of tracks associated with a specific artist, you will need to create a relationship between tracks and artists. You can do this in a single SQL statement. You can create this statement by starting with a separate query of Albums and tracks. This separate query will have the Artist ID you will need to create the single SQL statement.

[Dal Jeanis]

Ludovik - Don't GROUP BY the track name. Your count will always be 1 unless the artist has the same track name on two different albums.

Itsme - The visual query design screen fooled you. (I prefer reading the SQL too.) According to that design view image, it's already a three-table join, so the relationship between track and artist is already established via inner joins, track to album and album to artist. Ludovik just hasn't asked for any album fields in the query result, which makes it unclear that the dbo_album table is in the join.

[ItsMe]

@ Dal

So remove the fields and just include "Name" from the Artist table twice? Once for display and once to count?

[Ludovik]

Solution was simpler than all of that; after tinkering for about another hour all I had to do was change one value (see attached image).

[Dal Jeanis]

Yep. He gots it.