My table, which I’ll call Data, has 14 records which are comprised of two
important fields: Req ID and Parent ID, if it exists, and looks like:
Req ID Parent ID, if applicable
1
2 11
3
4 1
5
6 5
7 5
8
9 6
10 1
11
12 6
13
14
My query SQL code is:
SELECT [Data].[Req ID], [Data_1].[Req ID], [Data_2].[Req ID]
FROM ([Data] LEFT JOIN [Data] AS [Data_1] ON [Data].[Req ID] =
[Data_1].[Parent ID]) LEFT JOIN [Data] AS [Data_2] ON [Data_1].[Req ID] =
[Data_2].[Parent ID]
GROUP BY [Data].[Req ID], [Data_1].[Req ID], [Data_2].[Req ID]
ORDER BY [Data].[Req ID], [Data_1].[Req ID], [Data_2].[Req ID]
My resulting query result is (a little different than I described before
with the addition of Rec9 and Rec10): [I’ve added Rec identifiers to
represent the query record number, but are not part of the query output.]
Col 1 Col 2 Col 3
Rec1 1 4
Rec2 1 10
Rec3 2
Rec4 3
Rec5 4
Rec6 5 6 9
Rec7 5 6 12
Rec8 5 7
Rec9 6 9
Rec10 6 12
Rec11 7
Rec12 8
Rec13 9
Rec14 10
Rec15 11
Rec16 11 2
Rec17 13
Rec18 14
For my example, I want to exclude Rec3, Rec5, Rec9, Rec10, Rec11, Rec13,
Rec14 and Rec16 in the query (really in my report) since their Col 1 number
appear somewhere in the Col 2 or Col 3.
Hope this helps clarify the problem.
KARL DEWEY said:
Post an example of your raw data with field names and your query SQL statement.
In query design view click on menu VIEW - SQL View, highlight all, copy, and
paste in a post.
Paputxi said:
Tried it but still no luck. Perhaps my example query output was confusing,
which reflects my desired query output. But what my query actually provides
is shown below. Same as before, but it now contains additional records for 2,
4, 6, 7, 9, 10 and 12, which are the ones I need to exclude from the query or
report since they already appear as children in other records (i.e., in
columns 2 and 3).
Actual query output:
1 4
1 10
2
3
4
5 6 9
5 6 12
5 7
6
7
8
9
10
11 2
12
13
14
Any additional thoughts would be greatly appreciated. And, thanks in
advance for trying to help me on this.
:
Just follow what I gave you in my last post and in the report change the Hide
Duplicates property to Yes for the ID.
:
Karl,
My table has two important fields: Requirement ID and Parent Requirement ID,
if it exists. Using an example, my table looks like:
Req ID Parent ID, if applicable
1
2 11
3
4 1
5
6 5
7 5
8
9 6
10 1
11
12 6
13
14
My resulting query result would be,
1 4
1 10
3
5 6 9
5 6 12
5 7
8
11 2
13
14
My report would be structured like (by printed line),
1
4
10
3
5
6
9
12
7
8
11
2
13
14
Hope this helps. I look forward to your reply.
:
Better would be to show me how the data is in your table.
Here is one I did for someone else that is similar --
Table Alex --
ID Part NHL
1 30 20
2 40 30
3 50 30
4 60 20
5 20 10
SELECT Alex.Part, Alex.NHL, Alex_1.NHL AS NHL1, Alex_2.NHL AS NHL2
FROM (Alex LEFT JOIN Alex AS Alex_1 ON Alex.NHL = Alex_1.Part) LEFT JOIN
Alex AS Alex_2 ON Alex_1.NHL = Alex_2.Part
GROUP BY Alex.Part, Alex.NHL, Alex_1.NHL, Alex_2.NHL;
Results of query --
Part NHL NHL1 NHL2
20 10
30 20 10
40 30 20 10
50 30 20 10
60 20 10
:
Thanks for your reply, Karl. My example may have not been clear as it
indicates how the report looks, not the query output used to generate the
report. (Sorry about that.) So I'm providing some addtional information. I
have a self-join select query that is used as input to the "report" I showed
in my example. (Please ignore Line 12, which is incorrect.) Below is the
query output that would used for that report. Perhaps this will better show
the problem.
R1 R4 R7
R2 R6
R3
R4 (don't want it displayed since it is part of the first record)
R5
R6 (don't want it displayed since it is part of the second record)
R7 (don't want it displayed since it is part of the first record)
R8
Any thoughts on how to exclude the individual records (as indicated above)
for R4, R6 and R7, i.e., for any Rs appearing in fields 2 and 3 anywhere in
the output list (not just in those records preceeding it), from the query or
report?
Thanks.
:
Create a select query with output field like this --
Low Levels: [Lvl B] & [Lvl C]
In the query for the report left join the Lv A field of the table to the Low
Levels field of the query and use Is Null as criteria for the Low Levels
field.
:
I don't know if this belongs in the Query or Reports area. I have a list of
requirements, say R1 through R8 (see example below), where several of them
are children of other requirements, in this example, R1, R4, R6 and R7 are
children to other requirements. I already have a query (and report) that
lists/displays all the requirements and the children as shown below.
However, I want the query (actually the report) to NOT list/display the
children requirements at Lvl A too. That is, since R1, R4, R6 and R7 appear
in Lvl B or Lvl C, I don't want them to appear at Lvl A. Any thoughts on
what I need to do to NOT have lines 1, 7, 9 and 10 show up in the query or
the report? Thanks in advance.
Lvl A Lvl B Lvl C
Line 1 R1 (don't want this line; child to R8)
Line 2 R4
Line 3 R7
Line 4 R2
Line 5 R6
Line 6 R3
Line 7 R4 (don't want this line; child to R1)
Line 8 R5
Line 9 R6 (don't want this line; child to R2)
Line 10 R7 (don't want this line; child to R4)
Line 11 R8
Line 12 R1
