Query-Report problem

I know the answer to this must be a simple one, but I can't figure it out and I need to quickly.

I have 4 tables tied together.
Job Function is the parent table. The child tables are General Requirements, Local Requirements, and Job Elements. They are tied together by the Job Function field which is the PK or part of the PK on each table.

I have a query that is selecting the Job Function from the Job Function table, General Requirements from General Requirements table, Requirements from the Local Requirements table, and Elements from the Job Elements table. The query results in each row having the Job Function (1 in this case), General Requirements (5), Requirements (6), and Elements (1). So there are 30 records because one is created for each General Requirement-Local Requirements combination.

The bigger problem then arises when running the report. The 6 Local Requirements print out for each of the General Requirements.

The report should merely list the 5 General Requirements and the 6 Local Requirements and that is it.

I have tried adding the fields to Sorting and Grouping, hiding duplicates, etc., but I cannot get the report to read the way I want.

What it should say:

Gen Req Loc Req

1 A
2 B
3 C
4 D
5 E
F

(The F is lined up on the report, I just can't get it to post properly)

What I'm currently getting

Gen Req Loc Req

1 A
1 B
1 C
1 D
1 E
1 F

2 A
2 B
2 C
2 D
2 E
2 F

and so on (I can get Gen Req to print only if I have hide duplicates in the field properties, but that doesn't resolve the problem).

HELP!!! Thanks in advance!

You are not going to be able to resolve you problem in the query portion of this report (at lease not without a very complex query, that I don't know how to create). The layout of your DB won't want allow.

What you want is:
- three individual queries for each child item
- three individual subreports for each child query
- One main report where you embbed (drag and drop) each subreport on to which will give you something like

Function
- Function Info
Elements
- Element Info
General Requirements
- General Req 1
- General Req 2
- General Req 3
- General Req 4
- General Req 5
Local Requirments
- Local Req 1
- Local Req 2
- Local Req 3
- Local Req 4
- Local Req 5
- Local Req 6



The closest I could get you to what you wanted was with the following UNION query (it listed 12 records - not 6)

SELECT Function.FunctionKey, Elements.ElementKey, "" AS GeneralKey, "" AS LocalKey
FROM Function INNER JOIN Elements ON Function.FunctionKey = Elements.FunctionKey
union
SELECT Function.FunctionKey, "" AS ElementKey,General.GeneralKey, "" AS LocalKey
FROM Function INNER JOIN [General] ON Function.FunctionKey = General.FunctionKey
UNION
SELECT Function.FunctionKey, "" AS ElementKey, "" AS GeneralKey, Local.LocalKey
FROM Function INNER JOIN [Local] ON Function.FunctionKey = Local.FunctionKey;

With the following ouput:

FunctionKey,ElementKey,GeneralKey,LocalKey
1,,,1
1,,,2
1,,,3
1,,,4
1,,,5
1,,,6
1,,1,
1,,2,
1,,3,
1,,4,
1,,5,
1,1,,


Good-Luck
S-

Thanks S! I had tried some individual queries and some subreports, but not for all 3 items at once. I kept thinking there was some way to get it in a different format because I thought I had done it in the past.

Thanks again!

B