Data Base Design

[Andrew]

Someone has presented me with a Census Data Base with,
among others, the following fields in the Main Table:
Fname Husband, LName Husband, Occupation Husband;
Fname Spouse/Partner, LName Spouse/Partner, Occupation
Spouse/Partner.
The Occupation fields consist of a Lookup to a Seperate
table containing all possible Occupations. This works
OK, but the problem is that when I generate a report or
Mailing Label for eg: all Accountants, I have to run two
seperate reports (A Parameter Query is designed for this-
no problem); one for the Husband and one for the
Spouse/Partner.
What do I do to fix the problem, so that I only have to
generate 1 report or 1 set of mailing labels.
Thanks indeed for any assistance

 

[Mike Painter]

Someone has presented me with a Census Data Base with,
among others, the following fields in the Main Table:
Fname Husband, LName Husband, Occupation Husband;
Fname Spouse/Partner, LName Spouse/Partner, Occupation
Spouse/Partner.
The Occupation fields consist of a Lookup to a Seperate
table containing all possible Occupations. This works
OK, but the problem is that when I generate a report or
Mailing Label for eg: all Accountants, I have to run two
seperate reports (A Parameter Query is designed for this-
no problem); one for the Husband and one for the
Spouse/Partner.
What do I do to fix the problem, so that I only have to
generate 1 report or 1 set of mailing labels.
Thanks indeed for any assistance

Use OR instead of AND.

 

[Andrew]

Thanks Mike
Have tried that by entering the criteria in the Query
Grid as a Parameter Query under Occupation Husband and
then the OR (next line down) under Occupation Spouse, but
when I run the query it lists the Husband or Spouses
occupation even though it is not a match. Where have I
gone wrong?
Thanks

 

[John Vinson]

Someone has presented me with a Census Data Base with,
among others, the following fields in the Main Table:
Fname Husband, LName Husband, Occupation Husband;
Fname Spouse/Partner, LName Spouse/Partner, Occupation
Spouse/Partner.
The Occupation fields consist of a Lookup to a Seperate
table containing all possible Occupations. This works
OK, but the problem is that when I generate a report or
Mailing Label for eg: all Accountants, I have to run two
seperate reports (A Parameter Query is designed for this-
no problem); one for the Husband and one for the
Spouse/Partner.
What do I do to fix the problem, so that I only have to
generate 1 report or 1 set of mailing labels.
Thanks indeed for any assistance

The table is inappropriately designed for this purpose: you're asking
a question about People, and in a properly normalized database you'ld
have a table of People (rather than having some people in the Husband
field and others in the Spouse/Partner field).

Try using a UNION query:

SELECT [Fname Husband] AS FName, [LName Husband] AS LName, [Occupation
Husband] AS Occuption
FROM [Census]
UNION ALL
SELECT [Fname Spouse/Partner], [LName Spouse/Partner], [Occupation
Spouse/Partner]
FROM [Census] WHERE [FName Spouse/Partner] IS NOT NULL

Base your query on this query instead of on the table.

John W. Vinson[MVP]