Multiple field reporting

[XXLERATE]

I hope I've posted this to the correct forum.

I have inherited a database. It is a contact manager. The main table contains People. There is a related table to show contacts (any activity related to that person - phone call, email, meetings).

Each activity record has five subject fields - Subject 1, Subject 2, etc... These fields are populated with codes. We have a third unrelated table of Codes which contains the Code and a description.

So, an activity can have up to 5 codes - one per subject field.

I would like to generate a report that summarizes the codes that have been used during a certain date range, or by a certain staff member (each activity is tagged with staff initials).

I simply want to list the codes used and how many times each was used.

I am stumped because the codes are placed in the 5 different fields.

Thanks for your help.

[XXLERATE]

I have thought about setting up a calculated text field in the Activities table which gathers up the codes and separates them with a carriage return. If I go to my Codes table is there a way to use pattern count for each code to count the number of times it appears in a set of records?

Am I going in the right direction?

Thanks!

[Fenton]

You begin to see why Field1, Field2,... is not good design. If these multiple subjects were a little child (join) table of Activity, then you could do a report there, with Subsummary by SubjectCode.

Your current data could be converted to that structure with 5 Imports into that new table.

But, other than that, you're going in the right direction by concatenating the 5 fields in one, with carriage returns. Since you've then got multiple values in one field, it's difficult to do any normal kind of report. You could use PatternCount(). But then you'd need a separate calculation field for each possible Code value; no fun, depends on the number of Codes (which we don't know).

I'd do the "report" from the Code table. I'd use global fields for the date range and/or staff, using Søren's method of ValueListItems() in case the Staff criteria was blank (actually I'd use List(), but you can't with 7), so you could see the count for all staff in that case.

If you created a stripped-down sample/example, of only the relevant tables and fields and posted it here, then I (or someone here) could show you.

[Søren Dyhr]

I would like to generate a report that summarizes the codes that have been used during a certain date range, or by a certain staff member (each activity is tagged with staff initials).

Let's keep it here - your wish to utilize calc'fields are wrong!

View this:

--sd

[Søren Dyhr]

using Søren's method of ValueListItems() in case the Staff criteria was blank

Huh???

--sd

[XXLERATE]

Well it looks as though it will be much easier to create a child table for ActivityCodes and run reports from that table. You are correct - 5 imports will populate the table. Thank you for your input. No sense making things more complex than necessary.

[Fenton]

The Activity Codes will replace the 5 codes fields you have now (you probably knew that), and will be enterable in a portal; could be presented either vertically (default) or horizontally.

You might want to auto-enter the Staff ID and Date also, to make the report faster; though you could just use the ones in the Activities table.

Søren, I was referring to the method used in your PopGlobal file (at least the name I have for it).

Case ( IsEmpty ( g.Brand );ValueListItems ( Get ( FileName ) ; "Brands" );g.Brand)

[Søren Dyhr]

PopGlobal

Ah! that one...

--sd