Grouping, whether by report or query

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)?
 
A

Allen Browne

Create a query to get the most recent revision per plan:

1. Create a query based on tblRev.
Output R_PlanID and RevDate.
Depress the Totals button on the toolbar.
Accept Group By under R_PlanID.
Choose Max under RevDate.
Save the query as (say) qryRevDateMax

2. Create a new query using tblProcessPlan and the query you just saved,
joined on the PlanID.
Add tblRev. Join it to qryRevDateMax on R_PlanID and RevDate.

The result will let you output tblRev.RevID for the most recent date.

To get this working correctly, you probably want to open tblRev in design
view, create a unique index on the combination of R_PlanID + RevDate, and
mark both fields as Required. Otherwise the concept of 'most recent' is not
fully defined.

By using stacked queries like this instead of a subquery, you avoid the
'multi-level group by not allowed' error when you create the report based on
this.
 
B

BruceM

Thanks. That made for a much tidier query, and as you said I was able to
avoid the things that aren't allowed when a subquery is used. Now that I
know a little about how to make subqueries I need to learn more about when
to use them. I wonder if I made the right choice in another subquery
situation. I learned how to do that from your web site, by the way.
Anyhow, the other one works, so I'll leave it for now. I expect there will
be some changes once the database is in use for a while, so I expect I will
have a chance to revisit some of my choices.

Turns out the grouping problem (in the listing of Process Plans that apply
to each PN) I was having was because I had joined on the wrong field. The
PlanNumber was in a subreport that needed to be related to the main report
record by way of the PartID field, not the RevID, as I was doing.
 
A

Allen Browne

Yes, that sounds familiar, Bruce.

Subqueries are both very useful and very frustrating.
The other one that's working fine sounds like an appropriate choice.

All the best
 

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

Top