Crosstab Query: How to Know if Record does not Exist

Michael Vasilevsky
Michael Vasilevsky used Ask the Experts™
on
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.
Any input?
Thx,

MV
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
WHERE tbl_DocStages.DocStageStageNumber=5 
GROUP BY tbl_SysGroups.SysGroupNumber, tbl_Systems.SysNumber, tbl_Documents.DocNumber, tbl_DocRevisions.DocRev
PIVOT tbl_DocTypes.DocType;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior Software Architect
Commented:
add an isnull check to the max() function... now u will be able to differentiate the null rows and row does not exists...

try this query below... the dates with 01/01/1900 date will be rows with null values.. empty values will be the rows which do not exist..
TRANSFORM Max(isNull([DocStageActual],'1900-01-01') 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 
WHERE tbl_DocStages.DocStageStageNumber=5  
GROUP BY tbl_SysGroups.SysGroupNumber, tbl_Systems.SysNumber, tbl_Documents.DocNumber, tbl_DocRevisions.DocRev 
PIVOT tbl_DocTypes.DocType;

Open in new window

In Access you use NZ() instead if IsNull()
Michael VasilevskySolutions Architect

Author

Commented:
Yep that works :-) Thanks didn't think it was so simple!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial