Report with records in columns and field data in rows (1 Viewer)

[jharding08]

Hello,
I am looking to create a report that has the records in columns and the fields with values in rows. The columns can grow based on number of records that meet filter criteria.

The data is as follows:

1. A Job is a record
2. Each job has 5 phases with associated phase level data (revenue, hours)
3. There are 13 milestones. A phase can have 1 or more milestones. It is static as to which milestone goes with which phase. each milestone has a duration as its value
   1. Phase 1 - Milestones 1 and 2
   2. Phase 2 - Milestones 3
   3. Phase 3 - Milestones 4 and 5
   4. Phase 4 - Milestone 6
   5. Phase 5 - Milestones 7-13

The report I am looking for is a flattened version of each job (one job per column) with each data point per job/phase/milestone.

Having to flatten out the data in MS Access, I have created a query for each data point and tried joining the queries together to create my linear job record. Problem is, once I get to Phase 5, I get a Query is Too Complex error, I am guessing its too many joins. I've tried creating queries at the phase level to give me the data I need (but still use the milestone queries in the phase query) but I get the same error.

So really its two questions:

1. How to create the linear/single record for each job
2. How to create a report/view that shows jobs as columns and field data in rows

Thank you for your help

 

[Pat Hartman]

Here's a picture of a form like this that I made. The report is similar. It is done with subforms and cannot grow. Pick a fixed number or export the data to excel and build the report there.

 

[jharding08]

I guess if I can get a query that shows all jobs on one line, I can export to Excel and transpose the data.

Just wondering the best way to join all the data together to get everything on one line

 

[Pat Hartman]

You will need a separate query for each column you want to pivot. In the query, you can use the concat function (search here, I don't have a link handy).

OR, you can write your own and build a separate string for each field to concatenate the values. write each field to the correct row and increment the column to move to the next set of data.