Grouping Multiple Records

[Max]

This is rather lengthy. I have a query that pulls in
records under [Bid Package]. One problem with table
design is that in [Submittals] table, the [Bid Package]
can be a multiple entry (5/6/10/20)The query can seperate
the [Submittal] and assign it to its respective [Bid
Package. This works very well. I also have a field for
[Responsible CE]. The report then groups the records under
each CE. The problem is that under the report I am
getting repeating records if the [Submittal] has multiple
[Bid Packages]. Is there a way to filter out records if
all control sources in the record are the same?
(Basically if a record as more than one Bid Package the
report prints it out for each Bid Package. Below is the
code to seperate grouped [Bid Packages]. Hope I explained
my problem.


SELECT Submittals.*, Submittals.[Bid Package], [OCPAC Bid
Packages].*, Submittals.[Spec Section #], [Spec Section
Description].SECTION_NA, [OCPAC Bid Packages].[Bid
Package], Submittals.[Contr Due Date], Submittals.[Contr
Received], Submittals.Revw, ((IIf(IsNull([Contr Fcst]),Now
()-[Contr Due Date],Now()-[Contr Fcst]))*(5/7)) AS Due
FROM [OCPAC Bid Packages], [Spec Section Description]
INNER JOIN Submittals ON [Spec Section Description].[Spec
Section #] = Submittals.[Spec Section #]
WHERE (((Submittals.[Bid Package])=[OCPAC Bid Packages].
[Bid Package] Or (Submittals.[Bid Package]) Like [OCPAC
Bid Packages].[Bid Package] & "*/*" Or (Submittals.[Bid
Package]) Like "*/*" & [OCPAC Bid Packages].[Bid Package]
& "/*" Or (Submittals.[Bid Package]) Like "*/*" & [OCPAC
Bid Packages].[Bid Package]) AND ((Submittals.[Contr Due
Date]) Is Not Null) AND ((Submittals.[Contr Received]) Is
Null) AND ((Submittals.Revw)="X") AND ((((IIf(IsNull
([Contr Fcst]),Now()-[Contr Due Date],Now()-[Contr Fcst]))*
(5/7)))>0));

 

[Marshall Barton]

This is rather lengthy. I have a query that pulls in
records under [Bid Package]. One problem with table
design is that in [Submittals] table, the [Bid Package]
can be a multiple entry (5/6/10/20)The query can seperate
the [Submittal] and assign it to its respective [Bid
Package. This works very well. I also have a field for
[Responsible CE]. The report then groups the records under
each CE. The problem is that under the report I am
getting repeating records if the [Submittal] has multiple
[Bid Packages]. Is there a way to filter out records if
all control sources in the record are the same?
(Basically if a record as more than one Bid Package the
report prints it out for each Bid Package. Below is the
code to seperate grouped [Bid Packages]. Hope I explained
my problem.


SELECT Submittals.*, Submittals.[Bid Package], [OCPAC Bid
Packages].*, Submittals.[Spec Section #], [Spec Section
Description].SECTION_NA, [OCPAC Bid Packages].[Bid
Package], Submittals.[Contr Due Date], Submittals.[Contr
Received], Submittals.Revw, ((IIf(IsNull([Contr Fcst]),Now
()-[Contr Due Date],Now()-[Contr Fcst]))*(5/7)) AS Due
FROM [OCPAC Bid Packages], [Spec Section Description]
INNER JOIN Submittals ON [Spec Section Description].[Spec
Section #] = Submittals.[Spec Section #]
WHERE (((Submittals.[Bid Package])=[OCPAC Bid Packages].
[Bid Package] Or (Submittals.[Bid Package]) Like [OCPAC
Bid Packages].[Bid Package] & "*/*" Or (Submittals.[Bid
Package]) Like "*/*" & [OCPAC Bid Packages].[Bid Package]
& "/*" Or (Submittals.[Bid Package]) Like "*/*" & [OCPAC
Bid Packages].[Bid Package]) AND ((Submittals.[Contr Due
Date]) Is Not Null) AND ((Submittals.[Contr Received]) Is
Null) AND ((Submittals.Revw)="X") AND ((((IIf(IsNull
([Contr Fcst]),Now()-[Contr Due Date],Now()-[Contr Fcst]))*
(5/7)))>0));

I can't pretend to follow all that, but don't you want an
Inner Join from [OCPAC Bid Packages] to [Spec Section
Description]?

If that's an irrellevant remark, you might want to try using
DISTINCT to suppress duplicate records in the query's result
set.