Null values

G

Gary Nelson

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)
 
Top