access 2010 report

knvbhk · April 28th, 2014, 02:23 AM

Hi gurus!

I have created a report in Access2010 and seems like the data alignment in report view is not what I want. My current view is like the pix in the following URL. Is it possible to have those tasks shown just below the headers (months)?

I tried to merge those cells for display or even those criteria like Does Not Equal blank but both are not what I want. Or should I better modify my query? Thanks in advance!!!

http://i.stack.imgur.com/XEM3u.jpg

<table border=1 witdh=500>
<tr>
<td width=100>Oct-2013</td>
<td width=100>Nov-2013</td>
<td width=100>Dec-2013</td>
<td width=100>Jan-2014</td>
<td width=100>Feb-2014</td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td>01-BR/SR</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td>02-URS</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td>03-URS Signoff</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td>06-FRS</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td>07-Sys DEV</td>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>

<td> </td>
<td>08-PreTest</td>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>

<td> </td>
<td>09-UAT</td>
<td> </td>
<td> </td>
</tr>
<tr>
<td> </td>
<td> </td>
<td> </td>
<td> </td>
<td>12-Implementation</td>
</tr>
</table>

malcolmdixon · May 7th, 2014, 02:10 PM

Hi,

Is this the sort of layout you envisage:

Code:

Oct-2013		Nov-2013	Dec-2013	Jan-2014	Feb-2014
01-BR/SR	 			08-PreTest	 		12-Implementation
02-URS		 			09-UAT	 	 
03-URS Signoff	 	 	 	 
06-FRS	 	 	 	 
07-Sys DEV

I'm sure it's possible but I'd need an understanding of your table structure in order to guide with the SQL to extract the data correctly. You'll need to use a crosstab query that I'm sure of, as well as a key of the task number within that month. For example all first tasks are reported on first line etc. So, you'll need a query to generate this task position for each task. Once you let me know your table structures, I'll try to replicate what you're trying to achieve. HTH.

knvbhk · May 9th, 2014, 04:43 AM

Hi Malcolm,

The layout is really what I want. There are two tables for such layout.

tables involved:
Tasks: TaskTitle, ProjectID, StartDate, DueDate
Projects: ProjectName, ProjectID
key: ProjectID (number)

For the query I am using, it is a bit "weird" and I wonder that is the main reason I can't get those tasks aligned to top. However, some of the tasks may have StartDate and/or DueDate empty.

Quote:

TRANSFORM [Tasks].[TaskTitle] AS 值
SELECT [Projects].[ProjectName]
FROM Projects LEFT JOIN Tasks ON [Projects].[ID] =[Tasks].[ProjectID]
WHERE [Projects].[ProjectName] = 'CTI Phase 2'
GROUP BY [Projects].[ProjectName], [Tasks].[TaskTitle]
ORDER BY [Projects].[ProjectName]
PIVOT Format ([Tasks].[StartDate], "MMM-YYYY");

Note: 值 is the character for value as I am using a Chinese version.

A big thank you in advance for your help since I seldom work with MS Access!!!!!