Financial Data comparison in Access - Can I specify columns in a report?

[Vaslo]

Let's say I do a query that gives me a customer, their sales and the Month and Year of those sales. I created two different queries - one for Aug 2013 and one for Aug 2014. I'm having a hard time creating a report that allows me to put the sales side by side. I tried to join the two queries in one, which looks like I was successful. However, I don't know how to specify each column correctly. In short I'm looking to do:



Name Sales in FY13 Sales in FY14

I can do with two seperate queries or use the joined one. I envision copying my year text box twice and then somehow specifying the year that should be listed in each column.

As a follow up, can this solution be implemented to calculate a variance in the report?

Thanks.

[ranman256]

You want to query Q1, on both years in a single query then make a CROSSTAB query based on Q1. (use the qry wizard to build)
It will put each year as a column for comparison.

[Vaslo]

hah so simple for you, and now i can write a formula to calculate the variance. thanks ranman.

[Vaslo]

I spoke too quickly. The cross looks nice but how to I calculate the variance? I would have expected two different columns that I could have just subtracted but in the Crosstab it has the comparison as a "Column Value" and I don't know how to do that within one column.

I'm itching to return to Excel...