Two issues

In Access 2000, I created a report that I'm having two issues with, and could use your help.

First, the report is designed to show when "Cover Files are posted to the FTP site" and "Text Files are posted to the FTP site". I added these two fields to the tables and appear on two seperate forms as a checkbox. When the user has completed the "Cover file", they check the box that the "File has been posted to the FTP site", and when they finish the text, they also check the box. These are two seperate functions, in two separate forms. There can be several entries for one "Job Number", which is causing one problem.

For example: Job Number 2000 might have three separate entries, and the final entry the user checks the box that the file has been posted to the FTP site. I don't want all of the entries to appear on the report, only the one with the checkbox checked.

The second issue is that I want the Job Number to remain on the report until both the text and cover files have been posted to the FTP site, (both check boxes checked). If only one of them has been posted, then I would like a "True" value to appear on the report. When both "Text" and "Cover" files have been posted, then I would like the Job Number to be taken off of the report.

Please see the below Stored Procedure for further clarificatin of what I have done so far:

Create Procedure "FilesPostedToFTP" As

SELECT MAX(DISTINCT BookAll.JobNum) AS JobNum,

BookAll.PrintCode, BookAll.Author, BookAll.Title, BookAll.ISBN,

BookAll.PublisherID,

[Cover Prepress Detail Entry].FilesPostedToFTP,

ProofLog.FilesPostedToFTP AS Expr1, BookAll.DateIn,

Publisher.PublisherCode, Publisher.Publisher,

[Scheduling - AD].dtShipped, tblHoldJobs.Hold

FROM dbo.BookAll LEFT OUTER JOIN

dbo.tblHoldJobs ON

dbo.BookAll.JobNum = dbo.tblHoldJobs.JobNumber LEFT OUTER

JOIN

dbo.[Scheduling - AD] ON

dbo.BookAll.JobNum = dbo.[Scheduling - AD].JobNum LEFT OUTER

JOIN

dbo.ProofLog ON

dbo.[Scheduling - AD].JobNum = dbo.ProofLog.JobNum AND

dbo.BookAll.JobNum = dbo.ProofLog.JobNum LEFT OUTER JOIN

dbo.Publisher ON

dbo.BookAll.PublisherID = dbo.Publisher.PublisherId LEFT OUTER

JOIN

dbo.[Cover Prepress Detail Entry] ON

dbo.BookAll.JobNum = dbo.[Cover Prepress Detail Entry].JobNum

GROUP BY dbo.BookAll.PrintCode, dbo.BookAll.Author,

dbo.BookAll.Title, dbo.BookAll.ISBN, dbo.BookAll.PublisherID,

dbo.[Cover Prepress Detail Entry].FilesPostedToFTP,

dbo.ProofLog.FilesPostedToFTP, dbo.BookAll.DateIn,

dbo.Publisher.PublisherCode, dbo.Publisher.Publisher,

dbo.[Scheduling - AD].dtShipped, dbo.tblHoldJobs.Hold

HAVING (dbo.BookAll.DateIn > CONVERT(DATETIME,

'2003-01-01 00:00:00', 102)) AND

(dbo.BookAll.PublisherID = 7) AND

(dbo.[Scheduling - AD].dtShipped IS NULL) AND

(dbo.BookAll.PrintCode = N'10') AND

(MAX(DISTINCT dbo.BookAll.JobNum) <> 80477)