Including field names in a Report/Query

[sfreeland01]

Hi there,

I have a CONTRACT Table as follows:

Contract # (autonumber - primary key)
TV (Y/N)
Satellite (Y/N)
Radio (Y/N)
etc etc

There are around 10 different Yes/No format fields in the table.

What I would like to do is create a query in a report, within which any
field which is Y would be displayed...

For example my report (query?) would pull from the following Record:

Contract# 4000
TV Y
Satellite N
Radio Y
CCTV Y
PPV N

resulting in:

TV
Radio
CCTV

Thanks in advance for any help you can provide.

 

[Duane Hookom]

Normalize your table by using a union query:

SELECT ContractNum, "TV" as Item
FROM tblContract
WHERE TV=True
UNION ALL
SELECT ContractNum, "Satellite"
FROM tblContract
WHERE Satellite=True
UNION ALL
SELECT ContractNum, "Radio"
FROM tblContract
WHERE Radio=True
UNION ALL
--- etc ---;

Then use a subreport based on the union query to display "Items" for each
ContractNum.

 

[Marshall Barton]

I have a CONTRACT Table as follows:

Contract # (autonumber - primary key)
TV (Y/N)
Satellite (Y/N)
Radio (Y/N)
etc etc

There are around 10 different Yes/No format fields in the table.

What I would like to do is create a query in a report, within which any
field which is Y would be displayed...

For example my report (query?) would pull from the following Record:

Contract# 4000
TV Y
Satellite N
Radio Y
CCTV Y
PPV N

resulting in:

TV
Radio
CCTV

The quick and dirty way to do this is to have a text box for
each field using an expression like:
=IIf(TV, "TV", Null)
and set both the text box's and the section's CanShrink
property to Yes.

The best way to deal with this is to normalize the table and
put this kind of data in a separate table so the data will
not exist unless it you would have set it to Y. The table
would look like:
table ContractServices
fields ContractID and ServiceID