G
Guest
I have the following query to run a report of parts for production. If a
product requires 2 doors the part appears twice - same [UniqueIDInJob],
[Width], [Length], [Depth] but the [PartID] is unique and sequential. If
[Hinging] = "0" I need a field on my report to show "L" for the first door
and "R" for the second. Seem to have hit a wall in my brain to figure this
out. Here is SQL for report:
SELECT [qry BUYOUTSUBF].JobName, [qry BUYOUTSUBF].PartDesc, [qry
BUYOUTSUBF].PartName, [qry BUYOUTSUBF].UniqueIdInJob, [qry
Crawford1107].PartNumber, [qry BUYOUTSUBF].PartID, [qry
Crawford1107].Description, [qry BUYOUTSUBF].Width, [qry BUYOUTSUBF].Length,
[qry BUYOUTSUBF].Depth, [qry BUYOUTSUBF].Quantity, [qry BUYOUTSUBF].Level1,
[qry BUYOUTSUBF].Level2, [qry BUYOUTSUBF].Level3, [qry
BUYOUTSUBF].ProductDesc, [qry BUYOUTSUBF].ProductName, [qry
BUYOUTSUBF].Hinging, [qry BUYOUTSUBF].SequenceSuffix, [qry
BUYOUTSUBF].RptWidth, [qry BUYOUTSUBF].RptLength, [qry BUYOUTSUBF].RptDepth,
[qry BUYOUTSUBF].ProgramName,
IIf([hinging]=1,"R",IIf([hinging]=2,"L",IIf([hinging]=0 And [PartDesc] Like
"*Door*","L & R",""))) AS LR
FROM [qry Crawford1107] RIGHT JOIN [qry BUYOUTSUBF] ON [qry
Crawford1107].JoinPurch = [qry BUYOUTSUBF].JoinPSI
ORDER BY [qry BUYOUTSUBF].UniqueIdInJob, [qry Crawford1107].PartNumber, [qry
BUYOUTSUBF].PartID;
I thinks it should be simple but can't get there. Any help appreciated.
product requires 2 doors the part appears twice - same [UniqueIDInJob],
[Width], [Length], [Depth] but the [PartID] is unique and sequential. If
[Hinging] = "0" I need a field on my report to show "L" for the first door
and "R" for the second. Seem to have hit a wall in my brain to figure this
out. Here is SQL for report:
SELECT [qry BUYOUTSUBF].JobName, [qry BUYOUTSUBF].PartDesc, [qry
BUYOUTSUBF].PartName, [qry BUYOUTSUBF].UniqueIdInJob, [qry
Crawford1107].PartNumber, [qry BUYOUTSUBF].PartID, [qry
Crawford1107].Description, [qry BUYOUTSUBF].Width, [qry BUYOUTSUBF].Length,
[qry BUYOUTSUBF].Depth, [qry BUYOUTSUBF].Quantity, [qry BUYOUTSUBF].Level1,
[qry BUYOUTSUBF].Level2, [qry BUYOUTSUBF].Level3, [qry
BUYOUTSUBF].ProductDesc, [qry BUYOUTSUBF].ProductName, [qry
BUYOUTSUBF].Hinging, [qry BUYOUTSUBF].SequenceSuffix, [qry
BUYOUTSUBF].RptWidth, [qry BUYOUTSUBF].RptLength, [qry BUYOUTSUBF].RptDepth,
[qry BUYOUTSUBF].ProgramName,
IIf([hinging]=1,"R",IIf([hinging]=2,"L",IIf([hinging]=0 And [PartDesc] Like
"*Door*","L & R",""))) AS LR
FROM [qry Crawford1107] RIGHT JOIN [qry BUYOUTSUBF] ON [qry
Crawford1107].JoinPurch = [qry BUYOUTSUBF].JoinPSI
ORDER BY [qry BUYOUTSUBF].UniqueIdInJob, [qry Crawford1107].PartNumber, [qry
BUYOUTSUBF].PartID;
I thinks it should be simple but can't get there. Any help appreciated.