Query Does Not Count "2" Records

[antmorano]

Good Evening Everyone...

I have a bit of a logical puzzle that I need to solve. I have various
queries which have various criteria for them. One query involves
retirees or spouses being under the age of 65 and the other involves
retirees or spouses being over the age of 65. Both the retiree and
spouse are listen in the same records and on one line of the master
table and queries. The situation that I am running into is that I can
have a retiree who is over 65 and a spouse under 65 and the record
comes up twice in the two different queries b/c it meets the criteria,
which is perfect. What the issue is, is when I do a total count for
the amount of people eligible (meaning under and over 65) I am short
the records where they appear twice in two different queries. The
reason for this being is the fact that all the data is on the same
line. How would I tell my totals query (which I make my report from)
to figure out that the totals have to be doubled for some of the
records. Might sound confusing... but any suggestions would be
helpful.

-AM
Pension Fund Intern

 

[Dale Fye]

Without seeing your SQL or your data structure, I cannot tell you precisely.
However, I think I would create a union query that looks something like:

Select ID, "Husband" as SpouseType, HusbandName as SpouseName, HusbandDOB as
SpouseDOB
FROM yourTable
WHERE HusbandName IS NOT NULL
UNION ALL
SELECT ID, "Wife" as SpouseType, WifeName, WifeDOB
FROM yourTable
WHERE WifeName IS NOT NULL

You might even want to include a field in each part of the union query to
compute the individuals age. This query would give you a list of Names, DOBs
and ages of each partner. Then you could use this as a sub-query to count
the number where SpouseAge meets certain criteria.

HTH
Dale