Query 'loses' information....

[DeadPixel81]

Hello,

I have received help on developing a query on these forums. However, now I have encountered an issue.

I have a parent query that performs a Transform & Pivot on a sub query. The sub query has the data I am looking for in it. However, the parent query has lost the data.

First, here is the sub-query:

QRY_PERCENTCOMPLETE_SUB
 
SELECT 
tbl_FileStatus.[File Reference ID], 
tbl_Stages.[Stage Name], 
Sum(tbl_SubStages.[SubStage stage Weight]) 
 
AS Per
 
FROM tbl_Stages 
 
RIGHT JOIN 
 (
  tbl_SubStages 
   RIGHT JOIN 
   tbl_FileStatus 
   ON 
   tbl_SubStages.[SubStage ID] = tbl_FileStatus.[SubStage Reference ID]
 ) 
ON 
tbl_Stages.[Stage ID] = tbl_SubStages.[Stage Reference ID]
 
WHERE 
((
(tbl_FileStatus.SubActivityStatus)=1 Or    (tbl_FileStatus.SubActivityStatus)=3
))

This is the result of the query (I have framed the data that goes missing):


This is the parent query:

QRY_PERCENTCOMPLETE
 
TRANSFORM 
Sum(qry_PercentComplete_sub.Per) 
 
AS SumOfPer
 
SELECT 
tbl_Files.[File ID], 
tbl_Files.[Buyer Reference ID], 
tbl_Files.[Date Opened], 
tbl_Files.[COS File Number], 
tbl_Files.[Direct or Advertised], 
tbl_Files.[File Category Reference ID], 
tbl_Files.[RFx Type Reference ID], 
tbl_Files.[Project Lead], 
tbl_Files.[RFx Name], 
tbl_Files.[Stage 1 Comments], 
tbl_Files.[Stage 1 Comments Date], 
tbl_Files.[Stage 2 Comments], 
tbl_Files.[Stage 2 Comments Date], 
tbl_Files.[Stage 3 Comments], 
tbl_Files.[Stage 3 Comments Date], 
tbl_Files.[Stage 4 Comments], 
tbl_Files.[Stage 4 Comments Date], 
tbl_Files.[Stage 5 Comments], 
tbl_Files.[Stage 5 Comments Date], 
tbl_Files.[Stage 6 Comments], 
tbl_Files.[Stage 6 Comments Date], 
tbl_Files.[Stage 7 Comments], 
tbl_Files.[Stage 7 Comments Date]
 
FROM tbl_Files 
 
LEFT JOIN 
qry_PercentComplete_sub ON tbl_Files.[File ID] = qry_PercentComplete_sub.[File Reference ID]
 
WHERE 
(((tbl_Files.Closed)=False))
 
GROUP BY 
tbl_Files.[File ID], 
tbl_Files.[Buyer Reference ID], 
tbl_Files.[Date Opened], 
tbl_Files.[COS File Number], 
tbl_Files.[Direct or Advertised], 
tbl_Files.[File Category Reference ID], 
tbl_Files.[RFx Type Reference ID], 
tbl_Files.[Project Lead], 
tbl_Files.[RFx Name], 
tbl_Files.[Stage 1 Comments], 
tbl_Files.[Stage 1 Comments Date], 
tbl_Files.[Stage 2 Comments], 
tbl_Files.[Stage 2 Comments Date], 
tbl_Files.[Stage 3 Comments], 
tbl_Files.[Stage 3 Comments Date], 
tbl_Files.[Stage 4 Comments], 
tbl_Files.[Stage 4 Comments Date], 
tbl_Files.[Stage 5 Comments], 
tbl_Files.[Stage 5 Comments Date], 
tbl_Files.[Stage 6 Comments], 
tbl_Files.[Stage 6 Comments Date], 
tbl_Files.[Stage 7 Comments], 
tbl_Files.[Stage 7 Comments Date]
 
PIVOT 
qry_PercentComplete_sub.[Stage Name];

This is the result of the query (I have framed the data that is missing):


As you can see, in the sub query there are 5 stages with values. Four of the stages are 100% complete. One of the stages is 67% complete. When this data is provided by the parent query, it appears that only two of the stages have data indicating they are 100% complete.

I have discovered that deleting the contents of tbl_Files.[Stage 4 Comments] for the file number 82, then the percentages all show up. Is there some sort of limitation that I am not aware of? Here is the contents of that cell:

09.15.14 n/a as non-solicitation - information file only

Any help would be greatly appreciated.

Cheers,
DeadPixel

 

[DeadPixel81]

After some research I have discovered that my issue is likely with using GROUP BY on a memo field (The comment fields I have are memo types). I guess it has something to do with how memos are stored in memory.

I have not yet figured out how to deal with this. If anyone has some knowledge in this area help would be appreciated.

 

[namliam]

Why do a transform over so many rowheaders?

If you do need the rowheaders the key lays into transforming on the key value only, then in a subselect join back to your source table to fetch any and all related data.