Report header from two fields

[kieranish]

Hi,

I have a query that checks two fields, Producer 1 and Producer 2 and
returns all records where either field contains a value.

The report that uses this query is grouped by country and then by
Producer. What I need is for records where there is a value for both
Producer 1 and Producer 2 to appear under both of those Producers.

So if a record was

TITLE: example
Prod1: Alphaville
Prod2: Betatown

then it would need to appear in both the Alphaville and Betatown
sections of the report. Is this something I need to implement in the
query, or can I use an expression in the Producer header to do this?

Also, supposing Alphaville was British and Betaville was Greek, and
primary grouping is by country, would it create problems?

 

[Duane Hookom]

You should normalize your table structure. If you can't change your tables,
you can use a union query to create a normalized result:

SELECT Title, Prod1 as Producer, 1 as ProducerNumber
FROM tblNoNameGiven
UNION ALL
SELECT Title, Prod2, 2
FROM tblNoNameGiven
WHERE Prod2 Is Not Null;

 

[kieranish]

Hi,

This worked brilliantly, and I now have a query that returns results
both where Prod1 or Prod 2 or both are completed.

The next stage of the problem is in the report. The report lists main
producers and what I would like is for any result returned by the first
head of the union query to be listed under that producer, and any
result returned by the second head of the query to be listed by that
producer. So the query will generate:

Title Prod1 Prod2
Omega Alpha Beta
Omega Alpha Beta

with the first instance occuring because the producer Alpha is listed,
and the second instance occuring because the producer Beta is listed.
In the report this title should thus appear under each producer's
header.

Currently the report sorts primarily by producer territory and then by
Prod1, which is obviously a problem, but I'm unsure of what to put in
its place.

thanks
kieran

 

[Duane Hookom]

I don't understand "main producers", "head of the union query", "second head
of the query",...

 

[kieranish]

Okay.

I have one table, Films, two fields of which are Main Producer1 and
Main Producer2. Unfortunately these cannot be combined into one field,
but they both contain values derived from the same table. These fields
are used so we can track films that are produced by major production
companies.

90% of the time a film is produced by one company in which we are
interested. But occasionally a coproduction will involve two of our
main producers; eg Alpha and Beta.

What I would like is for a report to list such coproductions under both
companies' respective headers.

Presently I have a union query that will select records 1. Where Main
Producer1 is not null (and various other values are present) and 2.
Where Main Producer2 is not null.

I have a report that derives its data from this query. Presently it has
headers for territory and then for Producer, which is currently sorting
by Main Producer1. This means that, for example, the section of the
report for Alpha will list Omega twice in a row, and it will not appear
under Beta at all.

What I would like is for the report to list Omega both under Alpha and
under Beta.

 

[Duane Hookom]

Did you use the union query that I suggested? What is the record source of
your report?