Viewing data side-by-side for different criteria

[Swatkins]

Hi,

I got some great help here on a related issue that really dramatically improved the efficiency of some queries I was running. But now I'm running into a new problem on the same project.

For background: what I'm trying to do is compare profitability on a given set of products between two budget cycles. So I've got Product A and a list of profitability variables for that product (sales per year, revenue per sale, etc.) and the final query has the same data for two different budget periods (the budget period completed two years ago and the budget period completed last year).

What I'd like to do is compare all the profitability variables for each product for each year side-by-side.

I tried looking at a cross-tab query, but I don't think this will work because it only allows up to 3 row headers and can only summarize data for 1 field, whereas I'd like to see the summary for all of the profitability drivers. (I also keep getting an error about using a TempVar as a column header; I suspect this is because I'm using a couple TempVars as criteria for the budget period column in one of the supporting queries, but I don't understand why that's not fully resolved by the time the cross-tab query has run.)

My purpose is to be able to compare profitability from one budget period to the next and figure out what changes impacted profitability.

Any ideas on how to do this? What I tried initially so far hasn't worked at all.

 

[boblarson]

Okay, so this is how you would do it.

First off - create a query with ONLY the profitabilty variables. I assume that they are in a lookup table so you can select them as rows.

Then you create one query with just the data for the current budget cycle and another with just the data for the previous.

Then you create a new query with all three of those queries together and the link would be set to the profit variablity ID that you created in query one. But make sure that the links go from that first query separately to each of the other two tables and change the links from an INNER JOIN to a LEFT OUTER JOIN (double click on the link and set it to be "Select all records from first query where only records that match from the other query" - not the correct wording but you should get the picture).

 

[Swatkins]

Of course. I should've thought of that. Thanks!

It seems to be working pretty well, so far. I still have to sift through the data to make sure it's reporting as I expected, but so far so good.