By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,080 Members | 1,276 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,080 IT Pros & Developers. It's quick & easy.

Query calculations when some records don't exist

doma23
100+
P: 107
Would it be possible later to do query calculations between two records even when one of the two records doesn't exist?

Example:

COMPANY____PERIOD___SALES

Company1___1H2012___125
Company1___1H2011___100
*Company1___1H2010____? (*this record doesn't exist at all)

Company2___1H2012___240
Company2___1H2011___220
Company2___1H2010___200


So I want to compare these records, to see the percentage increase in Sales over three periods, for each company.

I want to get something like this, period vs period comparison:

COMPANY____1H2011___1H2012
Company1___ n/a ____ 25%
Company2___ 10% ____ 20%
Nov 9 '11 #1

✓ answered by Rabbit

You would do an aggregate query and pivot on the period. The missing value will be blank.

Share this Question
Share on Google+
2 Replies


Rabbit
Expert Mod 10K+
P: 12,421
You would do an aggregate query and pivot on the period. The missing value will be blank.
Nov 9 '11 #2

doma23
100+
P: 107
How would I set up then the formula to show the percentage changes in the crosstab query (I suppose that's what you mean by pivot)?
There would only be one field in which I must write formula, and that's Period (which would be shown as column), what do I write to compare different periods?

Query view:



Result:



Edit: SOLVED
I solved it by making another query and bringing Crosstab query to it, which then showed periods "1H11" and "1Q11" as Fields.
It was quite simple after that.
Nov 9 '11 #3

Post your reply

Sign in to post your reply or Sign up for a free account.