Trying to get a count report from multiple tables

[bellczar]

Happy Fourth, all! My request is not so urgent that grilling needs to be delayed.

I am trying to generate a count report from a multi-table Access database. It is a recording database with a songs in one table, recordings in a second table, and a third table that links the two. I am trying to generate a report of songs with the most numerous recordings in the database. For this operation, I don't need the recordings table. The main question is: For each title in the songs table, how many records are there in the links table with key number <50000? More specifically, for the report, I only care about the ones with a count >= 10.

Ideally, my report would look something like this:



65 Moon River
58 White Christmas
43 Autumn Leaves
42 Way You Look Tonight, The
etc.

As a corollary to this, how would I add a current, dynamic count to the form I use to access the database. Whenever a song title is pulled up, a field would show the current count of records in the links table with key number <50000.

[June7]

If you want a count of recordings then try an aggregate query, something like:

SELECT SongID, SongName, Count(*) AS CountRecords FROM thirdtable INNER JOIN Songs ON Songs.SongID = thirdtable.SongID WHERE key < 50000 GROUP BY SongID HAVING Count(*) >=10;

Use the query builder to construct the SQL statement.

For the form, could have a subform in for the third table with aggregate calc in subform footer. The textbox will show in Continuous view. If in Datasheet view, can use the Totals button from ribbon to open a totals row in the subform or a textbox on main form can reference the textbox in subform footer.

Otherwise, use a DCount domain aggregate function