Quote:Hi -
I'm getting ready to convert another of my few remaining databases in shhhhh Access to Sesame.
Before I begin yet another project, I would appreciate some feedback.
Each CD will be entered on a form with basic information about the CD such as Name, Date Purchased, Slot # in CD Player, etc.
Additionally, for each track, I want to record the Track #, Track Title, Recording Artist, Track Length and Title.
Exactly the application Sesame was designed for. When we were young and had more disposable income, my wife and I collected LPs. Between the two of us we have a couple thousand. I have always dreamed of having the free time to put them all in a database. Not just the titles, but also who plays on which songs, and what instruments they play.
Quote:I have never used sub-forms and am wondering whether this is a situation where I chould consider it (rather than creating a single form with 40 tracks even though a CD may only have 6 or 11, etc.).
Comments on the ramifications of the two approaches - especially with regard to the impact on searches and reports - would be greatly appreciated.
Thanks!
I would recommend a naturally linked subform. It will prevent you from having a lot of empty fields. Even empty, fields take up storage and use up resources. The LEs for those empty fields also take up a lot of form space.
As far as searching is concerned, subforms win hands down, in that you can search any of "song sub-records" for a song and get back all of the parent records that have that song on any track. If you use a bunch of LEs, you will need to know which track the song is to do a search on it.
For reports, subforms can be a bit tricky. Ray has some good examples of working with subforms and reports (with and wihout subreports). But then dealing with a lot of repetitive empty fields in a report isn't much fun.
Since a set of songs cannot appear on more than one CD, remember that you will want to use natural (
not relational) linking. It probably won't be perfectly normalized - but in this type of application that shouldn't matter at all.