Hi Forum,

I see that this type of question comes up again and again but I can't find the specific answer to this:

How do I start with a query like this?:

Item Year Cost Revenue Margin
A 2010 10 15 5
A 2009 9 15 6
A 2011 20 30 10
B 2012 15 25 10
B 2009 6 12 6
B 2011 50 200 150

and finish with a query this?:

Item Cos_2009 Rev_2009 Mar_2009 Cos_2010 Rev_2010 Mar_2010 Cos_2011 Rev_2011 Mar_2011 Cos_2012 Rev_2012 Mar_2012
A 9 15 6 10 15 5 20 30 10
B 6 12 6 50 200 150 15 25 10

Please see tables in Example.mdb attached.

Notes:
As data for 2013 is available then 3 columns would be added to the query.


Thanks for you time and consideration.

Nick

In brief, your Table is constructed incorrectly. You should have one Field that either has the word, Cost, Revenue or Margin and one Field for the amount of each. Then you could do a Crosstab Query. Understand?

RAZMaddaz

Here is an example, no pun intended, with your database. I had to create a Select Query first, to combine the Year and the CRM Field (Cost, Revenue, Margin), then I created a Crosstab Query based on the select Query. Is this what you wanted?

Hi,

yes, thank you, that is exactly what I wanted, I just couldn't see how to get there!
The queries make perfect sense now that I can see them!!

But I can't see how you converted the data from my table "Start" to your table "newStart".
Could I bother you again to explain how you achieved that?

(The "Start" table may not be in the correct form to complete the query but its all I have to start with) :-(

Thank you for you time and interest.

Kind regards ... Nick

Nick,

I redesigned the "newStart" Table by exporting the "Start" Table into Excel and doing a lot of copying and pasting, which took no more than five minutes. Then I imported the new Table into Access. Is that what you wanted to know?

Often you need to reconfigure your data before you import your data into Access, otherwise it might be very difficult to work with. And it's work the time too!!!

BTW: If the Margin Field is a Calculation, then you might want to think about doing that in Access as oppose to manually entering the value.

RAZMaddaz

Hi,
I've a similar problem with cross qry.
the original post start here,
Nobody reply me :-(..
Into this thread you wrote about same problem, I hope here will be some solution for me too.
thank
Fabrizio

Solved.

Now I export into xls with acCmdPivotTableExportToExcel, one file xls will generate with 2 sheets:
sheet1 = pivot-table
sheet2 = all records

with VBA I copy all the sheet1- cell and paste those into a sheet3, I delete sheet1 and 2, and I've the report!!!! (some formatting cell is appropriate)

Thank
Fabrizio