This question is more academic right now than anything, but I'm curious if this possible or how to best do it. I have a crosstab query like the below, which returns the maximum doc stage actual date for a given system for each document type. The question is this query returns a blank field if there is no DocStageActual date OR if there is no specific Doc Type for that system -- how can I tell which it is?
For example with the output:
SysGroup SysNumber DocType1 DocType2 DocType3
1 1 10/12 10/15 10/16
1 2 10/12 10/16
I don't know if system 2 has a DocType2 record that just doesn't have a date entered yet, or if there is no document of that type for system 2. Is there a way to derive the difference? I guess I could create a function that would let me know for a given system/doc type combination if any records are returned or not but that seems like it would really slow the query down.
TRANSFORM Max([DocStageActual]) AS DocStageDate
SELECT tbl_SysGroups.SysGroupNumber, tbl_Systems.SysNumber FROM (tbl_SysGroups INNER JOIN tbl_Systems ON tbl_SysGroups.SysGroupID = tbl_Systems.SysGroupNumber) INNER JOIN (tbl_Groups INNER JOIN ((tbl_DocTypes INNER JOIN tbl_Documents ON tbl_DocTypes.DocTypeID = tbl_Documents.DocTypeNumber) INNER JOIN (tbl_DocRevisions INNER JOIN tbl_DocStages ON tbl_DocRevisions.DocRevID = tbl_DocStages.DocStageDocNumber) ON tbl_Documents.DocID = tbl_DocRevisions.DocNumber) ON tbl_Groups.GroupID = tbl_Documents.DocResponsible) ON tbl_Systems.SysID = tbl_Documents.DocSystemNumber
GROUP BY tbl_SysGroups.SysGroupNumber, tbl_Systems.SysNumber, tbl_Documents.DocNumber, tbl_DocRevisions.DocRev