Difference between Columns in Crosstab Query (1 Viewer)

[stryfe3003]

Hi, I have a list of 10 products, say A, B, C, D...... and I log the sales of each product every day.
I created a crosstab query such that products are in the rows and columns are two dates that I specify in the query.
I wanted to find out whats the difference in sales between these two dates for each product. Eg
22 Jul 15 Jul Difference
Product A 10 3 7
Product B 15 20 -5
Product C 12 4 8

How can I create the above automatically? I have already created the userform to input the 2 dates required.
Thank you!

 

[Ranman256]

user forms dont work well with crosstabs, since the fields can change.
you should view them in datasheet only.
but you can make a 'report' table that holds generic column , append the xtab to it , and the form can show the math on columns that wont change names.

 

[arnelgp]

you need some VBA to add the Difference column.

 

[stryfe3003]

you need some VBA to add the Difference column.

Thanks very much, how do I reference to the column headers which changes according crosstab query result? Thanks again

 

[Gasman]

Thanks very much, how do I reference to the column headers which changes according crosstab query result? Thanks again

Have you even looked at the db arnelgp uploaded ?

 

[Pat Hartman]

As Ranman suggested, using the crosstab to calculate the difference is the WRONG method. You need to use queries. I modified arnel's example. It doesn't require code to do the calculation so it will be easier for you to understand. It uses three queries.
query1 selects and summarizes the values for date1
query2 selects and summarizes the values for date2
query3 joins the two queries and calculates the difference. Since both query1 and query2 return the same rows, there won't be a problem with the inner join in query3. A more complicated method would use a full outer join.

 

[stryfe3003]

That is very very helpful guys, much appreciated. Thank you!