table fields in list as criterion

[Guest]

I have a marketing database comprised of names and addresses for clients and
potential clients. The columns of the database are set up like:

/ NAME /TITLE / ORGANIZATION / ADDRESS /...... / CLIENT / HOLIDAY CARD /
NEWSLETTER /, etc.

All non address fields in the table are True/False.

I am wondering if it would be possible to design a query that could return
the names and addresses for the people who had a True value for a given
column by selecting which column to focus on as a criterion. To get a list of
names for holiday cards the user would select HOLIDAY CARD from a list of
column headings.

Currently there are over ten queries in the database each one using a
different True/False column as a criteria to produce address lists. I would
like to get that down to one if possible.

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Change the design of your db. You should have 2 tables: 1 for the
person info (Name, Title, etc.); the other for the what was sent to the
person (Holiday Card, NewsLetter, etc). There should be a 1 to many
relationship between the 2 tables. There should not be check boxes for
each category in table 2. The table designs should be like this:

CREATE TABLE Persons (
person_id counter , -- AutoNumber column
[name] varchar(30) not null primary key ,
title varchar(30) not null ,
organization varchar(30) null ,
address varchar(50) null,
... etc. ...
)

CREATE UNIQUE INDEX idx_person_id ON Persons (person_id)

The different types of items that can be sent to a person. When you
want to add a new item type you won't have to add a new column to the
Persons table, just add a new row (record) to the ItemTypes table.

CREATE TABLE ItemTypes (
type_id counter , -- AutoNumber column
description varchar(30) not null primary key
)

CREATE UNIQUE INDEX idx_type_id ON ItemTypes (type_id)

What was sent to each person.

CREATE TABLE PersonItems (
person_id integer not null references Persons ,
type_id integer not null references ItemTypes ,
CONSTRAINT PK_Attributes PRIMARY KEY (person_id, type_id)
)

Then the query to get whichever attribute the user wants is easier to
write:

PARAMETERS [Which item?] TEXT;
SELECT P.[name], P.address, IT.description as Item
FROM (ItemTypes AS IT INNER JOIN PersonItems AS PI
ON IT.type_id = PI.type_id)
INNER JOIN Persons As P
ON PI.person_id = P.person_id
WHERE IT.description = [Which attribute?]

Read some articles/books on database design. I recommend _Database
Design for Mere Mortals_, by Hernandez. Also, read about parameter
queries in the Access Help for more information.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQkCdRoechKqOuFEgEQL6rgCg+6e6pc+972gzUZ92U2dJofTqRB4Ani4c
PEQgzOiemqqmTVVigQFr/5jg
=z1x3
-----END PGP SIGNATURE-----

 

[Guest]

Thanks a million. I understand where you are coming from, I just hope I can
explain the concept to the marketing staff. (non-access users)