Show what's NOT there

  • Thread starter Thread starter John Vinson
  • Start date Start date
J

John Vinson

I'm pretty good at queries, I can say without false modesty. But this
one has me stumped!

I have a Report I'm trying to generate. Each page of the report covers
a Project; each Project has multiple Reviews, with any of several
ReviewTypes. There's a table of Reviews with a ProjectID and a
ReviewType (linked to a ReviewTypes table).

The customer wants to see what ReviewTypes were done - easy - but also
wants a list of which reviews were NOT done.

I can do an "unmatched" frustrated outer join query to see what review
types have not been done for any project - but this Report might have
several pages, each for a different project. On each page I need to
see the ReviewTypes that are not included in *that particular
project's* Reviews.

I tried setting a Subreport's Recordsource in the report section's
format event, but get an error that it's too late - you can't the
recordsource property at that point (since the subreport has already
been populated). Similarly, I can't use the Report's txtProjectID as a
criterion (I get no rows shown).

Suggestions?
 
Try create a cartesian query of
SELECT tblProjects.*, ReviewType
FROM tblProjects, tblReviewTypes;
Then, create a query with this query rather than tblProjects. Use a LEFT or
RIGHT join to the reviews table.
 
See Below:


John Vinson said:
I'm pretty good at queries, I can say without false modesty. But this
one has me stumped!

I have a Report I'm trying to generate. Each page of the report covers
a Project; each Project has multiple Reviews, with any of several
ReviewTypes. There's a table of Reviews with a ProjectID and a
ReviewType (linked to a ReviewTypes table).

The customer wants to see what ReviewTypes were done - easy - but also
wants a list of which reviews were NOT done.

I can do an "unmatched" frustrated outer join query to see what review
types have not been done for any project - but this Report might have
several pages, each for a different project. On each page I need to
see the ReviewTypes that are not included in *that particular
project's* Reviews.

I tried setting a Subreport's Recordsource in the report section's
format event, but get an error that it's too late - you can't the
recordsource property at that point (since the subreport has already
been populated). Similarly, I can't use the Report's txtProjectID as a
criterion (I get no rows shown).
Click to expand...


I use Access 2000 SR-3, JET SP-6.


CREATE TABLE Projects
(ProjectID INTEGER
,ProjectName TEXT(48)
,CONSTRAINT pk_Projects PRIMARY KEY (ProjectID)
)

Sample Data

1, Airport Traffic Control
2, IRS New Tax System


CREATE TABLE ReviewTypes
(ReviewType TEXT(24)
,CONSTRAINT pk_ReviewTypes PRIMARY KEY (ReviewType)
)

Sample Data

Desk
Unit
Integration
Systems


CREATE TABLE ProjectReviews
(ProjectID INTEGER
,ReviewType TEXT(24)
,CONSTRAINT pk_ProjectReviews PRIMARY KEY (ProjectID, ReviewType)
,CONSTRAINT fk_ProjectReviews_Projects FOREIGN KEY (ProjectID)
REFERENCES Projects
(ProjectID)
,CONSTRAINT fk_ProjectReviews_ProjectReviewTypes FOREIGN KEY (ReviewType)
REFERENCES ReviewTypes
(ReviewType)
)

Sample Data

1, Desk
1, Unit
2, Desk
2, Unit
2, Integration


CartesianJoin_Projects_ReviewTypes AS PRT1
SELECT P1.ProjectID
,P1.ProjectName
,RT1.ReviewType
FROM Projects AS P1
,ReviewTypes AS RT1;


ProjectsWithoutCompletedReviews
SELECT PRT1.ProjectID
,PRT1.ProjectName
,PRT1.ReviewType
FROM CartesianJoin_Projects_ReviewTypes AS PRT1
WHERE NOT EXISTS
(SELECT PR01.ReviewType
FROM ProjectReviews AS PR01
WHERE (PR01.ProjectID = PRT1.ProjectID)
AND (PR01.ReviewType = PRT1.ReviewType))

OUTPUT
Airport Traffic Control, Systems
Airport Traffic Control, Integration
IRS New Tax System, Systems

Which is the expected output, a list of Projects and the Reviews that
haven't been completed.


ProjectsCompletedReviews
SELECT P1.ProjectID
,P1.ProjectName
,PR1.ReviewType
FROM Projects AS P1
INNER JOIN
ProjectReviews AS PR1
ON P1.ProjectID = PR1.ProjectID


Create a Report based on ProjectsCompletedReviews, and force page breaks
on ProjectName.

Create a ProjectName Footer.

Drop a Subreport onto ProjectName Footer based on
ProjectsWithoutCompletedReviews (ProjectID, ReviewType). Modify the
Subreport (created by the Report Wizard) to make the label and text box for
ProjectID Visible = No.

Otherwise modify the Report to suit. I modified several labels for
spaces, etc.

In my completely test Report, I'm getting, roughly:


Project Name: Airport Traffic Control
Completed Review Types:
Desk
Unit
Uncompleted Review Types:
Integration
Systems


Project Name: Airport Traffic Control
Completed Review Types:
Desk
Integration
Unit
Uncompleted Review Types:
Systems


The Review Types don't appear in order (Desk, Unit, Integration, Systems),
but a Sequence Number field in the ReveiwTypes Table and including that
column in the queries, with appropriate sorting in the Report, would fix
that.


Sincerely,

Chris O.
 
I use Access 2000 SR-3, JET SP-6.


CREATE TABLE Projects
(ProjectID INTEGER
,ProjectName TEXT(48)
,CONSTRAINT pk_Projects PRIMARY KEY (ProjectID)
)
Click to expand...
.....

WOW. Ask and you shall receive!

Thank you Chris, you spent FAR more time on this than I would have had
the shoe been on the other foot.

The Cartesian join with a "frustrated outer join" (logically
equivalent to a Not Exists) in fact did give me the desired result.
 
Try create a cartesian query of
SELECT tblProjects.*, ReviewType
FROM tblProjects, tblReviewTypes;
Then, create a query with this query rather than tblProjects. Use a LEFT or
RIGHT join to the reviews table.
Click to expand...

Thanks, Duane. It took some fiddling with the outer join; I needed to
left join by both ProjectID and ReviewType but it did give the correct
result. Report sent off to the customer about 1am today... :-{/
 
John Vinson said:
....

WOW. Ask and you shall receive!
Click to expand...

You are welcome. :)
Thank you Chris, you spent FAR more time on this than I would have had
the shoe been on the other foot.
Click to expand...

It was about two hours, but only because I'm weak on Access Reports. I
had to wrestle to remember how to create groups and force page breaks, and
where to move the controls the wizard splatted on the form so it would
actually work correctly.

I learned a great deal myself, too, so I was hardly without benefit.

The Cartesian join with a "frustrated outer join" (logically
equivalent to a Not Exists) in fact did give me the desired result.
Click to expand...

Oh, sorry. Yes, the FOJ is probably a lot more popular than the NOT
EXISTS, but that's how I learned the Difference operation, so I write it out
without thinking.
 
Back
Top