K
kieranish
(I'm using filtering in a more generic sense than the access term)
By way of background, I'm building a database to track film productions
and it will only have one table from which reports are generated.
What I would like to implement is a report (and so a query/queries)
that generates a list of films for a particular region. The front end
works, so people can choose from a drop down menu the country they
want, and a report will be called up. But I'm struggling to define
precisely the query that will retrieve relevant data.
Currently entries have four fields for territory. One is locked and
autocompletes if another field is filled out. The next two fields are
enabled, but will also autocomplete if corresponding fields are filled
out, and the last field is not linked to any other field. The
autocomplete is necessary for a parallel feature that works well, but
it can mean for some co-productions that territory 1, 2, 3 and even 4
could be the same. So suppose I want to generate a report of Irish
films, the query would need to check the four fields, and if "Ireland"
was present in any of them it would return the data.
Now I could probably do that with four seperate queries for each
territory field and then combine them into a Union query. But the
problem is that, as stated, some records might have, for example,
Ireland, Ireland, Ireland, France. And so if 3 queries within the union
query turned up the same record(s), could I exclude the duplicates from
the query and so the report?
For the sake of clarity, an example record might be:
Title: Don Quixote
Terr 1: France (because there is a French sales agent)
Terr 2: France (Because a French production company we're interested in
is attached)
Terr 3: France (Because that company is co producing with another
company we're interested in)
Terr 4: Belgium (Because it's shot there)
The union query method seems the most obvious solution to me, but if
there's a more elgant solution involving a single query, I would be
happy to hear that too.
thanks,
kieran
By way of background, I'm building a database to track film productions
and it will only have one table from which reports are generated.
What I would like to implement is a report (and so a query/queries)
that generates a list of films for a particular region. The front end
works, so people can choose from a drop down menu the country they
want, and a report will be called up. But I'm struggling to define
precisely the query that will retrieve relevant data.
Currently entries have four fields for territory. One is locked and
autocompletes if another field is filled out. The next two fields are
enabled, but will also autocomplete if corresponding fields are filled
out, and the last field is not linked to any other field. The
autocomplete is necessary for a parallel feature that works well, but
it can mean for some co-productions that territory 1, 2, 3 and even 4
could be the same. So suppose I want to generate a report of Irish
films, the query would need to check the four fields, and if "Ireland"
was present in any of them it would return the data.
Now I could probably do that with four seperate queries for each
territory field and then combine them into a Union query. But the
problem is that, as stated, some records might have, for example,
Ireland, Ireland, Ireland, France. And so if 3 queries within the union
query turned up the same record(s), could I exclude the duplicates from
the query and so the report?
For the sake of clarity, an example record might be:
Title: Don Quixote
Terr 1: France (because there is a French sales agent)
Terr 2: France (Because a French production company we're interested in
is attached)
Terr 3: France (Because that company is co producing with another
company we're interested in)
Terr 4: Belgium (Because it's shot there)
The union query method seems the most obvious solution to me, but if
there's a more elgant solution involving a single query, I would be
happy to hear that too.
thanks,
kieran