Creating a dashboard report

[vicky464]

I would like to create a report that is currently being done in Excel. It takes data from multiple, unrelated sources and displays the monthly results for each. So for example, here is a scaled down version:
Jan Feb Mar
Goal 1 3988 8922 4919
Goal 2 250 201 132

Each goal is based on the result of data that is queried from the database, but those goals/queries are not necessarily related to one another. For example, in the above, it could be Goal 1 = number of widgets sold, Goal 2 = number of new customers.

There are approx 10 goals and all the months are displayed as well as totals, and a trend indicator (arrow, stoplight, or something along those lines)

I have created a couple of the queries, for example, one of them tells me the number of new UNIQUE customers per month. (thanks Adam) So that if a customer appears in Feb and Apr, they only count in Feb, when they were new. This is working fine, but the result displays like this:

Month New Customers
Jan 250
Feb 201
Mar 132

To get these values to display in my dashboard report, I used DLOOKUP's for each value result. This method is very slow, and I am afraid I am using too much memory/resources.

QUESTION 1 -- Is there any other way I can get the data to display horizontally instead of vertically without using Dlookup? Not sure if crosstabs would work.

BTW - There is also a significant amount of conditional formatting on these values, and I think that is slowing it down a little. Takes over 4 minutes to run the report for just ONE goal.

QUESTION 2 - Would I be better off running queries, sending them to Excel, and creating the dashboard in Excel?



Thanks a bunch.

[Stingaway]

Curious to find out what your solution was - Your questions were spot on with something that I have been dealing with. Sometimes the solution is in Access as a cross tab query, sometimes I send to Excel and pivot, depending on the report.

[vicky464]

Hi,
I tried looking back to the original database to see how I solved this. For this client I ended up using Excel Pivot Tables to get the report they needed. Sorry I couldnt be of more help.
Vicky