Moving Data from Rows to Columns in Query - How to do this?

[warren0127]

Hi all,

I know there was a similar post out there, but my situation is a bit different. The good news is, I don't have to work with too many levels of data here.

So, basically what I need to do is setup a query that displays an ID (Task Name) and the Fiscal Year Amounts. So, each ID should have Fiscal Year Amounts for FY15 through FY21 which should be extracted from the ROW data (FiscalYear and Amount). The Amount is next to each FY row - I just need to put the amounts in columns now for each Fiscal Year.

I have built a table for the funding which has the three columns mentioned above (ID, FY, Amount). Instead of creating multiple columns for each fiscal year, I just created a "Fiscal Year" column that allows a user to pick a FY with a dropdown of FY15 through FY21.

The query will be using the attached table below. I've also attached the query screenshot of both datasheet view and design view.

What I need to do:

Ultimately, I want to turn the results of this query into a report. So, somehow, I need to display each Task Name (ASTMIS ID) with the funding amounts of each fiscal year, so it would look like this:

ASTMIS_ID	FY16 Amount	FY17 Amount	FY18 Amount
Energy Informed Operations	XXXX	XXXX	XXXX
Mission Command on the Move Technologies	XXXX	XXXX	XXXX

I need to have the query understand the row data - IE: FY15 amounts all displayed in one column for each task name, FY16 amounts all displayed in second column, etc. I've attached a query screenshot of what I am trying to do (right now, I have the fields "FiscalYear" and "Amount" combined or concatencated because I was hoping I could figure it out that way, or thought that was a starting direction.. but turns out, I am still stumped.)

It should basically summarize each fiscal year and the dollar amounts that are associated with those fiscal years to be in their own COLUMNS. So, how would I take all of those FiscalYear rows with their amounts, and put them into their own columns to show amounts for each year?

Any help is appreciated - I'm not an Access expert, nor SQL - but I am trying to learn as much as I can!



Thanks!!

[Demerit]

What you want to do can be done using a crosstab query.
Simply use the wizard and choose crosstab query.

[June7]

Review http://allenbrowne.com/ser-67.html

[warren0127]

Thanks for showing me the CrossTab option - I had no clue that was a simple solution to my problem, and worked fantastic! Thank you!