How would you handle this reporting problem?

[dmusicant]

I have a table with a bit over 600 records and one of the reports lists records alphabetically on one of the fields. The issue I have here is that roughly a dozen of entries in the field that determines the order of records displayed contain two entities. The report as constituted has those records in accordance with the first of the entities, the other is effectively ignored. I'd like to produce a better report that includes the secondary entity on a separate line. Thus, Shostakovich/Berg would have two lines, one for Shostakovich, the other for Berg.

I'm thinking of adding a character field called SECOND or similar, that has the secondary entity name. I could create a function that executes a SQL select command on the table that pulls records from only those that have non-empty SECOND records and joins those records with the report I have going now.

I think this will work, but wonder if I'm getting unnecessarily complicated or if there's a cleaner, leaner, smarter way to do this. I have the feeling I'm missing something obvious here.

 

[TamarGranor]

I haven't read the thread carefully, so I may be missing something. But if you really want to keep all the names in one field rather than normalizing, and if you can guarantee a maximum number of names in that field, then to solve your problem, add something like this to your fieldlist:

GetWordNum(SonyRight,1,'/') AS SRight1, ;
GetWordNum(SonyRight,2,'/') AS SRight2, ;
GetWordNum(SonyRight,3,'/') AS SRight3, ;

Now you can run a query on the results of your first query, where you use UNION to assemble all the records. First query in the UNION uses SRight1, second uses SRight2 and includes only records where that's not empty, third uses SRight3 and includes only records where that's not empty. You use ORDER BY at the end to order the whole set.

Tamar

 

[dmusicant]

Thank you, Tamar. Thing is I figure I don't always care about that second artist. For example, Berg/Schoenberg/Krenek, I only have one record that includes Krenek, I haven't listened to the Krenek on it, maybe someday I'll have several Krenek recordings and want to see what I have in that report, but for now, no. So, I like having a second usually-empty field where I will put an alternative artist that will appear on a separate line in the printed report, but on a case-by-case basis.

Tamar, I downloaded your Building Queries In Visual FoxPro yesterday and spent some time today reading it, more carefully in some places, skimming it in others. I also received yesterday a copy of The Hacker's Guide to Visual FoxPro 7.0, in part because of your recommendation. Now, I've had the 6.0 edition since it came out. I was very surprised that the 7.0 edition is about 1/3 the size of the 6.0 edition. May I ask why? Is the ebook as big as the 6.0's ebook? I haven't downloaded the ebook for 7.0 yet, will maybe have to ask Whil, as I did for my 11 other Henzenworke editions. Whil sent those to me a couple weeks ago or so.

 

[TamarGranor]

The printed version of HackFox 7 doesn't contain the reference section; that's only in the eBook.

As for your probably, you could take the result of the first query I'm suggesting and count how many times each composer appears and include in the final query only those over some threshold.

Tamar