I have a data table on which I'm trying to create a new report. I already have three related reports, but none is sufficient. I'm not sure, though, if it's possible.

The relevant data looks like this:

Date
clientid
CommunityDistrict (abbreviated CD#)

These rows are not unique.

Report #1 (already working) shows me the number of client contacts for each date. At the end of the report, it shows the total.

This is fairly simple to do: The report is sorted by date, and two counters count the rows. Counter one is shown at the end of each date, and then reset to 0 while counter two keeps accumulating.

Report #2 Shows me what #1 does, but also adds a break out of the number for each CD#. This is done similarly to the above. For this one, I sort on CD# within the date, and I have 3 counter, the third one being used to show the number contacts within the CD for that date. The date shows, then on each line the amount of contacts for one CD#, then a total for the date. This repeats for each date. The report is not bad looking, and it's quite flexible, because it will work no matter how many CD#s are being served, and which ones were served in this time period.

Report #3 Shows that same data, but in a grid like format. On each line, there is the date, the total contacts for the data, and then the contacts for each CD#. We have a preset number, and set of CD# that can be reported on, and there is always a number for each CD# - even if the number is 0. For this one, I have a counter for the date, then a counter for each of the CD#s and each one is shown for each row. The set of counters is hard coded. This is the main disadvantage of this report. It's much more compact than #2, and (according to my boss) visually appealing. The problem is that it's rigid. If there is ever a change in the CDs I have to change the report. It's not that big a deal to make the change, but it's not something that an end-user (even a reasonably well trained one) could do by himself. This is a real concern, because we've already had such changes, and it's an ongoing issue.

What I am looking to do is create a report with the layout of #3 but the flexibility of #2. Is there any way to get the data into a form where this is possible?

that's not exactly an SQL question, is it

it's an application design problem

this forum is for non-proprietary SQL

your SQL is straightforward, even if your design problem (to show any number of CDs in a crosstab fashion) isn't

Well, that's what I was wondering about. I was hoping that it might be possible to use sql to get the data into a format that makes this a straightforward application design issue.

I knew that it was a long shot. I figured that either I was overlooking something so obvious that I'd kick myself, or it couldn't be done. So, you're telling me it's the latter.

<sigh> It wasn't what I WANTED to hear, but it was certainly helpful, since it will keep me from wasitng any more time barking up that tree.

Thanks