Having a problem with a field - please help

G

Gary Nelson

I have a report that I am creating with Access2000 front-end and SQL server
backend. I am adding a field called "Plates Made".

Each order coming in receives an order number which is placed in a table
with other information. The order will go through multiple stages of
production. Each stage is entered into the table. There can be multiple
entries entered for the same order number. The final stage is Plates Made,
OrderStatusID = 20, which is the status I am looking for to appear on the
report. The issue that I am having, is that I am getting the previous
entries and not the final "Plates Made". If the plate is not made, I have
code written in the Stored Procedure which states: IF StatusID=20 then
Plates Made Else Plates not made. However, it's not working properly. Please
see the stored procedure below. Thanks in advance for the assistance.

Alter Procedure dbo.spBMCoverScheduleTEST
As
SELECT BookAll.JobNum, [Cover Print Process].CvrPrntProcessID,
[Cover Print Process].CvrPrntProcess, BookAll.DateIn,
[Scheduling - AD].dtPrintCvr2, [Scheduling - AD].dtPrintTxt2,
[Scheduling - AD].TxtPrfStatus, [Scheduling - AD].CvrPrfStatus,
BookAll.Author, Publisher.PublisherCode,
[Binding Style].BindStyleAbbr, BookAll.TotalWidth,
BookAll.AllCovers + BookAll.ExtraCvrs AS Quantity,
[Cover Colors].CvrColor, [Scheduling - AD].dtBNBInvRec,
[Scheduling - AD].dtBound, [Scheduling - AD].dtShipped,
[Scheduling - AD].dtBound2, [Scheduling - AD].BNBDelivery,
--[Proof Job Type].ProofCode,
[Scheduling-LoadedDates].PrintCoverDue, Hold, HotJob,
vPlatesMade.[End], vPlatesMade.StatusID,

'JobDueDate' =
CASE
WHEN [Hold] = 1 THEN '9/9/99'
WHEN ([DaysOnHold] Is Not Null AND [CustomerDelay] = 1 And [Take Off Hold] =
1)
THEN (dbo.BoundBook.BoundBook + [DaysOnHold])
ELSE (dbo.BoundBook.BoundBook)

END,

'CoverStatus' =
CASE
WHEN [CvrPrfStatus] = '28' THEN 'RTP, Needs Plates'
ELSE 'RTP'

END,

'Text Status' =
CASE
WHEN [dtPrintTxt2] is not null THEN 'Text Printed'
When [dtPrintTxt2] is null and [TxtPrfStatus] = '12' Then 'RTP'
ELSE 'Not RTP'

END,

'PlateStatus' =
CASE
WHEN ([StatusID] = 20) THEN 'Plate Made'
ELSE 'Plate Not Made'

END



FROM dbo.BookAll INNER JOIN
dbo.[Scheduling - AD] ON
dbo.BookAll.JobNum = dbo.[Scheduling - AD].JobNum INNER JOIN
dbo.[Job - ED] ON
dbo.BookAll.JobNum = dbo.[Job - ED].JobNum INNER JOIN
dbo.Publisher ON
dbo.BookAll.PublisherID = dbo.Publisher.PublisherId INNER JOIN
dbo.[Cover Print Process] ON
dbo.BookAll.CvrPrntProcessID = dbo.[Cover Print Process].CvrPrntProcessID
LEFT OUTER JOIN
--dbo.[Proof Job Type] ON
--dbo.[Scheduling - AD].CvrPrfStatus = dbo.[Proof Job Type].ProofCodeID
INNER JOIN
dbo.vPlatesMade ON
dbo.BookAll.JobNum = dbo.vPlatesMade.JobNum LEFT OUTER JOIN
dbo.[Scheduling-LoadedDates] ON
dbo.BookAll.JobNum = dbo.[Scheduling-LoadedDates].JobNum LEFT
OUTER JOIN
dbo.BoundBook ON
dbo.BookAll.JobNum = dbo.BoundBook.JobNum LEFT OUTER JOIN
dbo.[Hot Jobs] ON
dbo.BookAll.JobNum = dbo.[Hot Jobs].JobNum LEFT OUTER JOIN
dbo.[Cover Colors] ON
dbo.BookAll.CvrColorID = dbo.[Cover Colors].CvrColorID LEFT OUTER
JOIN
dbo.[Binding Style] ON
dbo.BookAll.BindStyleID = dbo.[Binding Style].BindStyleID LEFT
OUTER JOIN
dbo.tblHoldJobs ON
dbo.BookAll.JobNum = dbo.tblHoldJobs.JobNumber
WHERE (dbo.[Scheduling - AD].dtBNBInvRec IS NULL) AND
(dbo.[Scheduling - AD].dtBound IS NULL) AND
(dbo.[Scheduling - AD].dtShipped IS NULL) AND
(dbo.[Scheduling - AD].dtBound2 IS NULL) AND
(dbo.[Scheduling - AD].BNBDelivery IS NULL) AND
(dbo.BookAll.DateIn > CONVERT(DATETIME,
'2002-12-31 00:00:00', 102)) AND
(dbo.[Scheduling - AD].dtPrintCvr2 IS NULL) AND
(dbo.[Cover Print Process].CvrPrntProcessID = 1 OR
dbo.[Cover Print Process].CvrPrntProcessID = 2 OR
dbo.[Cover Print Process].CvrPrntProcessID = 3) AND
(dbo.[Scheduling - AD].TxtPrfStatus = 12) AND
(dbo.[Scheduling - AD].CvrPrfStatus = 12 OR
dbo.[Scheduling - AD].CvrPrfStatus = 20 OR
dbo.[Scheduling - AD].CvrPrfStatus = 26 OR
dbo.[Scheduling - AD].CvrPrfStatus = 28)AND
dbo.BookAll.PODScanOnly is null AND
(dbo.[Scheduling-LoadedDates].PrintCoverDue IS NOT NULL)
 
M

MGFoster

Gary said:
I have a report that I am creating with Access2000 front-end and SQL server
backend. I am adding a field called "Plates Made".

Each order coming in receives an order number which is placed in a table
with other information. The order will go through multiple stages of
production. Each stage is entered into the table. There can be multiple
entries entered for the same order number. The final stage is Plates Made,
OrderStatusID = 20, which is the status I am looking for to appear on the
report. The issue that I am having, is that I am getting the previous
entries and not the final "Plates Made". If the plate is not made, I have
code written in the Stored Procedure which states: IF StatusID=20 then
Plates Made Else Plates not made. However, it's not working properly. Please
see the stored procedure below. Thanks in advance for the assistance.

Alter Procedure dbo.spBMCoverScheduleTEST
As
SELECT BookAll.JobNum, [Cover Print Process].CvrPrntProcessID,
[Cover Print Process].CvrPrntProcess, BookAll.DateIn,
[Scheduling - AD].dtPrintCvr2, [Scheduling - AD].dtPrintTxt2,
[Scheduling - AD].TxtPrfStatus, [Scheduling - AD].CvrPrfStatus,
BookAll.Author, Publisher.PublisherCode,
[Binding Style].BindStyleAbbr, BookAll.TotalWidth,
BookAll.AllCovers + BookAll.ExtraCvrs AS Quantity,
[Cover Colors].CvrColor, [Scheduling - AD].dtBNBInvRec,
[Scheduling - AD].dtBound, [Scheduling - AD].dtShipped,
[Scheduling - AD].dtBound2, [Scheduling - AD].BNBDelivery,
--[Proof Job Type].ProofCode,
[Scheduling-LoadedDates].PrintCoverDue, Hold, HotJob,
vPlatesMade.[End], vPlatesMade.StatusID,

'JobDueDate' =
CASE
WHEN [Hold] = 1 THEN '9/9/99'
WHEN ([DaysOnHold] Is Not Null AND [CustomerDelay] = 1 And [Take Off Hold] =
1)
THEN (dbo.BoundBook.BoundBook + [DaysOnHold])
ELSE (dbo.BoundBook.BoundBook)

END,

'CoverStatus' =
CASE
WHEN [CvrPrfStatus] = '28' THEN 'RTP, Needs Plates'
ELSE 'RTP'

END,

'Text Status' =
CASE
WHEN [dtPrintTxt2] is not null THEN 'Text Printed'
When [dtPrintTxt2] is null and [TxtPrfStatus] = '12' Then 'RTP'
ELSE 'Not RTP'

END,

'PlateStatus' =
CASE
WHEN ([StatusID] = 20) THEN 'Plate Made'
ELSE 'Plate Not Made'

END



FROM dbo.BookAll INNER JOIN
dbo.[Scheduling - AD] ON
dbo.BookAll.JobNum = dbo.[Scheduling - AD].JobNum INNER JOIN
dbo.[Job - ED] ON
dbo.BookAll.JobNum = dbo.[Job - ED].JobNum INNER JOIN
dbo.Publisher ON
dbo.BookAll.PublisherID = dbo.Publisher.PublisherId INNER JOIN
dbo.[Cover Print Process] ON
dbo.BookAll.CvrPrntProcessID = dbo.[Cover Print Process].CvrPrntProcessID
LEFT OUTER JOIN
--dbo.[Proof Job Type] ON
--dbo.[Scheduling - AD].CvrPrfStatus = dbo.[Proof Job Type].ProofCodeID
INNER JOIN
dbo.vPlatesMade ON
dbo.BookAll.JobNum = dbo.vPlatesMade.JobNum LEFT OUTER JOIN
dbo.[Scheduling-LoadedDates] ON
dbo.BookAll.JobNum = dbo.[Scheduling-LoadedDates].JobNum LEFT
OUTER JOIN
dbo.BoundBook ON
dbo.BookAll.JobNum = dbo.BoundBook.JobNum LEFT OUTER JOIN
dbo.[Hot Jobs] ON
dbo.BookAll.JobNum = dbo.[Hot Jobs].JobNum LEFT OUTER JOIN
dbo.[Cover Colors] ON
dbo.BookAll.CvrColorID = dbo.[Cover Colors].CvrColorID LEFT OUTER
JOIN
dbo.[Binding Style] ON
dbo.BookAll.BindStyleID = dbo.[Binding Style].BindStyleID LEFT
OUTER JOIN
dbo.tblHoldJobs ON
dbo.BookAll.JobNum = dbo.tblHoldJobs.JobNumber
WHERE (dbo.[Scheduling - AD].dtBNBInvRec IS NULL) AND
(dbo.[Scheduling - AD].dtBound IS NULL) AND
(dbo.[Scheduling - AD].dtShipped IS NULL) AND
(dbo.[Scheduling - AD].dtBound2 IS NULL) AND
(dbo.[Scheduling - AD].BNBDelivery IS NULL) AND
(dbo.BookAll.DateIn > CONVERT(DATETIME,
'2002-12-31 00:00:00', 102)) AND
(dbo.[Scheduling - AD].dtPrintCvr2 IS NULL) AND
(dbo.[Cover Print Process].CvrPrntProcessID = 1 OR
dbo.[Cover Print Process].CvrPrntProcessID = 2 OR
dbo.[Cover Print Process].CvrPrntProcessID = 3) AND
(dbo.[Scheduling - AD].TxtPrfStatus = 12) AND
(dbo.[Scheduling - AD].CvrPrfStatus = 12 OR
dbo.[Scheduling - AD].CvrPrfStatus = 20 OR
dbo.[Scheduling - AD].CvrPrfStatus = 26 OR
dbo.[Scheduling - AD].CvrPrfStatus = 28)AND
dbo.BookAll.PODScanOnly is null AND
(dbo.[Scheduling-LoadedDates].PrintCoverDue IS NOT NULL)
Click to expand...

It sounds like you want the report to only show records where StatusID =
20. If that's all you want then put that criteria in the WHERE clause.

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Grouping to get the Last entry made 0
form with two fields 0
Null values 0
Ambiguous Column Name 1
Summing 0
problem agian with outer joins, pls. Help 0
Subform not populating with records 0

Top