Previous Record Value

[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.

 

[Guest]

In your IIF you dont have the column names qualified. This may be a problem.
e.g. you have [hinging] and not [qry BUYOUTSUBF].Hinging

Also, I'd STRONGLY recommend not having embedded spaces in any name in Access.

Dorian

 

[Guest]

Thank you for the response. I resolved the problem simply by counting the
unique lines on the report and changing the L/R designation based on a count
of 1 or 2. I'll work on the spaces.
--
Lori

In your IIF you dont have the column names qualified. This may be a problem.
e.g. you have [hinging] and not [qry BUYOUTSUBF].Hinging

Also, I'd STRONGLY recommend not having embedded spaces in any name in Access.

Dorian

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.