B
BruceM
I don't know if this is a query question or a report question, so I am
cross-posting.
I have a database for keeping track of Process Plans, which are the
instructions for processing a machine part. A Process Plan is often
revised, and I want to list the most recent revision only, thus the subquery
in the SQL below. The list of parts that may be processed according to a
Process Plan may change from one revision to the next (one is added, another
removed, etc.), so the Parts list is related to the Revision rather than to
the Process Plan. There is a Parts table (tblPart), a Revision table
(tblRev), and a junction table (tblRevPart) between the two.
Here is the general structure:
tblProcessPlan
PlanID (autonumber PK)
PlanNumber (text)
tblRev
RevID (PK)
R_PlanID (FK to tblProcessPlan)
RevDate
tblRevPart
RevPartID (PK)
RP_RevID (FK to tblRev)
RP_PartID (FK to tblPart)
Here is the SQL for my report:
SELECT DISTINCT tblPart.PN, tblRev.RevID
FROM tblRev INNER JOIN (tblPart INNER JOIN tblRevPart ON tblPart.PartID =
tblRevPart.RP_PartID) ON tblRev.RevID = tblRevPart.RP_RevID
WHERE (((tblRev.RevID) In (SELECT TOP 1 RevID
FROM tblRev AS RevSub
WHERE RevSub.R_PlanID = tblRev.R_PlanID
ORDER BY RevSub.RevDate DESC)));
The thing that's missing from the SQL is the PlanNumber from tblProcessPlan.
A given PN (Part Number) may be processed according to two or more Process
Plans. PN 345678 may be processed according to PlanNumber 05-89 or 07-22.
In a listing of Parts I want something like this:
PN PlanNumber
123456 01-11
234567 04-26
345678 05-89
07-22
456789 08-21
My idea was to use a report for the PN, with a subreport for the PlanNumber.
This works up to a point. Here is what I get:
PN PlanNumber
123456 01-11
234567 04-26
345678 05-89
345678 07-22
456789 08-21
I tried grouping on PN, with PN in the group header and the subreport in the
Detail section. Then I get:
PN PlanNumber
123456
01-11
234567
04-26
345678
05-89
07-22
456789
08-21
So I tried placing the subreport in the group header rather than the Detail
section. However, I cannot see 07-22. There is only one PlanNumber for
each PN.
Any idea on how to approach this (other than trying to convince myself I
like something other than the first way I showed the listing)?
cross-posting.
I have a database for keeping track of Process Plans, which are the
instructions for processing a machine part. A Process Plan is often
revised, and I want to list the most recent revision only, thus the subquery
in the SQL below. The list of parts that may be processed according to a
Process Plan may change from one revision to the next (one is added, another
removed, etc.), so the Parts list is related to the Revision rather than to
the Process Plan. There is a Parts table (tblPart), a Revision table
(tblRev), and a junction table (tblRevPart) between the two.
Here is the general structure:
tblProcessPlan
PlanID (autonumber PK)
PlanNumber (text)
tblRev
RevID (PK)
R_PlanID (FK to tblProcessPlan)
RevDate
tblRevPart
RevPartID (PK)
RP_RevID (FK to tblRev)
RP_PartID (FK to tblPart)
Here is the SQL for my report:
SELECT DISTINCT tblPart.PN, tblRev.RevID
FROM tblRev INNER JOIN (tblPart INNER JOIN tblRevPart ON tblPart.PartID =
tblRevPart.RP_PartID) ON tblRev.RevID = tblRevPart.RP_RevID
WHERE (((tblRev.RevID) In (SELECT TOP 1 RevID
FROM tblRev AS RevSub
WHERE RevSub.R_PlanID = tblRev.R_PlanID
ORDER BY RevSub.RevDate DESC)));
The thing that's missing from the SQL is the PlanNumber from tblProcessPlan.
A given PN (Part Number) may be processed according to two or more Process
Plans. PN 345678 may be processed according to PlanNumber 05-89 or 07-22.
In a listing of Parts I want something like this:
PN PlanNumber
123456 01-11
234567 04-26
345678 05-89
07-22
456789 08-21
My idea was to use a report for the PN, with a subreport for the PlanNumber.
This works up to a point. Here is what I get:
PN PlanNumber
123456 01-11
234567 04-26
345678 05-89
345678 07-22
456789 08-21
I tried grouping on PN, with PN in the group header and the subreport in the
Detail section. Then I get:
PN PlanNumber
123456
01-11
234567
04-26
345678
05-89
07-22
456789
08-21
So I tried placing the subreport in the group header rather than the Detail
section. However, I cannot see 07-22. There is only one PlanNumber for
each PN.
Any idea on how to approach this (other than trying to convince myself I
like something other than the first way I showed the listing)?