"Merging" Yes/No Fields in a query (MSAccess 2007)

[InOverMyHead]

Hello,

I have a question about a database that tracks rights for television
programs sold in specific territories. Each contract has a listing of
15 or so different rights and what I'd like to do is have the user
pick a territory, then display all the rights that are already sold in
it.

I've gotten this far - A user can pick a territory and the following
(very simplified) gets displayed:


Program Title RightA RightB
RightC RightD
=========================================================
FirstProgram Y N
N N
FirstProgram Y Y
N N
SecondProgram N Y
N N

For the territory report, I want the output to be this:

FirstProgram Y
Y N N
SecondProgram N Y
N N

So in other words, I'm looking for a way to merge the results of the
yes/no fields.

I'm sure there's more information that I need to provide, please feel
free to ask. Thanks in advance for any suggestions.

 

[John W. Vinson]

Hello,

I have a question about a database that tracks rights for television
programs sold in specific territories. Each contract has a listing of
15 or so different rights and what I'd like to do is have the user
pick a territory, then display all the rights that are already sold in
it.

I've gotten this far - A user can pick a territory and the following
(very simplified) gets displayed:


Program Title RightA RightB
RightC RightD
=========================================================
FirstProgram Y N
N N
FirstProgram Y Y
N N
SecondProgram N Y
N N

For the territory report, I want the output to be this:

FirstProgram Y
Y N N
SecondProgram N Y
N N

So in other words, I'm looking for a way to merge the results of the
yes/no fields.

Wordwrap and ambiguity made this awfully hard to answer. What do you mean by
"merge"? In the first list you have the first program with two sets of four
yes/no values, YNNN and YYNN; what is the logic which combines these to
produce YYNN?

More fundamentally, you're apparently "committing spreadsheet". You have 15
*OR SO* rights, each stored with a field for each right. What will you do when
the "or so* becomes 17 rights? Redesign your table, rewrite all your queries,
revise all your forms and reports? OUCH! If you have a many (programs) to many
(rights) relationship, the correct relational structure has three tables: a
table of Programs; a table of Rights (currently a 15 row table, but it can be
expanded); and a "resolver table" with a field for the ProgramID and a field
for the RightID. In this table there would be 15 rows for FirstProgram, then
another 15 for SecondProgram and so on. I'm guessing that the merging process
will be much easier with this normalized design.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[InOverMyHead]

Hi John,

Thanks for taking the time to reply. Sorry about the word-wrap issue,
let me clear it and the ambiguity up:

The database has these tables: Contract, Customer/Broadcaster,
Programs and a Contract/Territory table.

When the client wants to record a sale of a particular program to a
Broadcaster, the rights granted to the Broadcaster are detailed in the
Contract table (15 Yes/No fields), along with the terms of the sale.
Each sale (line on the Contract table) is for one Program, but can be
in many territories.

So what I'd like the client to be able to do is select for instance
"Italy" and have it return what show and rights are available to be
sold (or the converse, which shows and rights have been sold if this
is easier).

Right now I can create a query that will show each sale in a specific
territory, along with what rights were given in that sale. However,
this can result in a long report - when what I'm looking for is
something that a salesperson can take a quick glance at.

For example: (RA, etc stands for RightA)

Program RA RB RC RD RE

TitleA Y Y N N N
TitleA Y N N N Y

I can get this report right now, but what I'd like it to look like is:

TitleA Y Y N N Y

The logic behind it being that the salesperson now knows that
television show "TitleA" has been sold to Italy in the past, but that
Right "C" and "D" are still available to be sold.

Thanks again.

 

[John W. Vinson]

Hi John,

Thanks for taking the time to reply. Sorry about the word-wrap issue,
let me clear it and the ambiguity up:

The database has these tables: Contract, Customer/Broadcaster,
Programs and a Contract/Territory table.

When the client wants to record a sale of a particular program to a
Broadcaster, the rights granted to the Broadcaster are detailed in the
Contract table (15 Yes/No fields), along with the terms of the sale.
Each sale (line on the Contract table) is for one Program, but can be
in many territories.

So what I'd like the client to be able to do is select for instance
"Italy" and have it return what show and rights are available to be
sold (or the converse, which shows and rights have been sold if this
is easier).

Right now I can create a query that will show each sale in a specific
territory, along with what rights were given in that sale. However,
this can result in a long report - when what I'm looking for is
something that a salesperson can take a quick glance at.

For example: (RA, etc stands for RightA)

Program RA RB RC RD RE

TitleA Y Y N N N
TitleA Y N N N Y

I can get this report right now, but what I'd like it to look like is:

TitleA Y Y N N Y

The logic behind it being that the salesperson now knows that
television show "TitleA" has been sold to Italy in the past, but that
Right "C" and "D" are still available to be sold.

Thanks again.

I'd still strongly suggest normalizing your table structure: it is currently
WRONG and will be a major pain down the road. Every time you need to add or
change a Right, you will need to change the structure of your Contracts table,
all your forms, all your reports, all your queries that involve it. Major
hassle!!!

That said... you can take advantage of the fact that a Yes/No field is stored
as a number. 0 is FALSE; -1, or in fact ANY nonzero value, is TRUE. So in the
footer of your Report you can put Checkbox controls with a Control Source like
=Sum([RA])

This will be checked if any of the Titles have a TRUE value in that field; it
will be unchecked only if none of them do.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[InOverMyHead]

Thanks very much for all your help on this John: and I will be
suggesting to the client that they normalize the tables (its a legacy
database that I've been asked to take a look at).

Just in case anyone's searching this for a similar issue: I followed
your suggestion and created a new Group header/footer in the report,
placed the show title in the header, placed the check-boxes in the
footer and all is well.

Thanks again, you're an all-star (if not an MVP).