A query or subquery?

[Hector Codoceo]

Hello, my name is Hector, and I have a problem with my
query, Please I need Help.

My Query question it's:
What Contractor takes this juornal out?

these are the fields:
Journal: journal name
Contractor: contractor name
Total pp: total of pages
Due: date when the journal was taken out for the contractor
Returned: date when the journal it's returned

here is the SQL view:
------------------------------------------
SELECT [All].Journal, [All].Contractor, [All].[Total pp],
[All].Due, [All].Returned
FROM [All]
WHERE ((([All].Journal)=[enter journal name]) AND
(([All].Returned) Is Null));
------------------------------
so, I have to write the constractor name, FOOD (only for
this example), so I get the following result, and that
it's perfect.

Journal Contractor Total pp Due Returned
--------------------------------------------------------
FOOD KD 138 9/30/2003
FOOD KD 46 10/3/2003
FOOD LK 81 10/6/2003

But Know the problem is:
How Can I show right below every contractor a list with
the rest of the journals that the constrator it's working
out too.
something like this:

Journal Contractor Total pp Due Returned
--------------------------------------------------------
FOOD KD 138 9/30/2003
FOOD KD 46 10/3/2003
FOOD LK 81 10/6/2003
-----------

KD FOOD 138 9/30/2003
FOOD 46 10/3/2003


LK FOOD 81 10/6/2003
AJCS 78 4/8/2003
HCRA 20 10/20/2003
AMPE 16 9/30/2003

In my example KD contractor only it's working with the
journal FOOD, so only show that, but LK contractor have
more journals including FOOD journal, and this is what I
need, to see all the others journals not only the one that
I ask for (of course that all these journal have in common
that the Returned IS NULL.

It's very confuse for me, please I need help.
Thanks,
Hector

 

[Duane Hookom]

Use a report and subreport.