Digital Music Inventory System Question

[mmcconnell]

I'm trying to create a simple inventory system of digital music (downloads) sales so that licenses can be properly tracked. We have to purchase a license for each song sold.

I'm having trouble with one part of the reporting.

I receive a CSV from the vendor that lists 2 types of transactions: single track (song 1, song 2, etc) and complete album (Album 1 which is made up of song 1, song 2, etc.)

I can successfully report totals on the single tracks using a Summary field in the table, but I'm unsure of the best methodology on how to include the songs sold when a complete album is reported.

I have 2 tables at this point: Digital Downloads which contains all the data imported from the vendor (and will be added to every couple of weeks). Important fields are the Artist, Album, Song, and Quantity Sold. Second table is Albums (will be updated as new albums are recorded). Fields are Album Name, Track Name, License Purchased.

The single report I have created has a header with the Artist, A sub-summary by song (trailing) with the Song and Quantity Sum fields, and a footer with the date. There is a simple script on the front end of the report that sorts the records by Artist and Song and opens the report layout.

When an album is sold, the CSV has an empty Song field, so I can certainly find albums by omitting records that have anything in the song field.

Is this a portal solution, where each instance an album displays the songs that it contains? If so, how do I add those results to the other summed songs?

Or is this a temp table solution where each time the report is run, the applicable songs are added depending on the album sold?