Query (or Report) Generation Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paputxi - 16 May 2006 18:52 GMT

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

Reply to this Message

KARL DEWEY - 16 May 2006 19:14 GMT

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
[quoted text clipped - 20 lines]
> Line 11 R8
> Line 12 R1

Reply to this Message

Paputxi - 18 May 2006 18:19 GMT

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]
[quoted text clipped - 27 lines]
> > Line 11 R8
> > Line 12 R1

Reply to this Message

KARL DEWEY - 19 May 2006 00:37 GMT

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
[quoted text clipped - 51 lines]
> > > Line 11 R8
> > > Line 12 R1

Reply to this Message

Paputxi - 23 May 2006 17:45 GMT

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.
>
[quoted text clipped - 75 lines]
> > > > Line 11 R8
> > > > Line 12 R1

Reply to this Message

KARL DEWEY - 23 May 2006 18:59 GMT

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,
>
[quoted text clipped - 126 lines]
> > > > > Line 11 R8
> > > > > Line 12 R1

Reply to this Message

Paputxi - 25 May 2006 20:12 GMT

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.
[quoted text clipped - 129 lines]
> > > > > > Line 11 R8
> > > > > > Line 12 R1

Reply to this Message

KARL DEWEY - 25 May 2006 22:18 GMT

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.

> 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
[quoted text clipped - 158 lines]
> > > > > > > Line 11 R8
> > > > > > > Line 12 R1

Reply to this Message

Paputxi - 26 May 2006 00:34 GMT

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.

> 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
[quoted text clipped - 162 lines]
> > > > > > > > Line 11 R8
> > > > > > > > Line 12 R1

Reply to this Message

Paputxi - 30 May 2006 19:52 GMT

I was able to figure out a multi-step solution, although it might not be the
best one. Using the full query I indicated in my example, I created two
Make-Table queries to get the numbers only in column two and then column
three. I set the Criteria to “Is Not Null” to eliminate any blank or null row
values in the respective column. I then created a Union query to combine the
two make-tables together. Luckily, this query automatically eliminated all
duplicate numbers. Lastly, I used the “Find Unmatched Query Wizard” to
create a query using my full query and the union query, which nicely
eliminated the rows I wanted to exclude. I would welcome any thoughts on how
to shorten the process.

> 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:
[quoted text clipped - 220 lines]
> > > > > > > > > Line 11 R8
> > > > > > > > > Line 12 R1

Reply to this Message