find oldest person living at an address

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that lists name, address,city, state, zip, phone, date of
birth, etc.

I want to send a mailing to the oldest person at each address.

I have tried numerous group bys etc and am not sure it is working properly.

Help?

Thanks.
 
It sounds like you want to group by the address fields and get the Min() of
the Date Of Birth.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Yes, I tried that. I guess I have not explained the result I am seeking as
well as I could. I want the name and address of the oldest person at each
address to be displayed so I can mail something to them. When I group by the
address I get the first name listed at the address, the address, then the min
date of birth. But sometimes the first person listed does not have the
earliest date of birth. So then I get the name and address of the first
person and the oldest one's birth date.

I did sort by date of birth in the table and then ran the group by address
and it seemed to work ok, I just wonder if there is a better way that I am
more confident is working properly.

Thanks for your input.
 
Not meaning to presume I know for sure
what Jeff meant, but I *think* he was referring
to one technique where you first create a prequery
(say "qryMinBirthDate")

SELECT
Address,
City,
State,
Zip,
Min([Date of Birth]) As MinDOB
FROM
yurtable
GROUP BY
Address,
City,
State,
Zip;

Then, join the above query with your
table on address, city, state, zip, and
DOB to get "Name" (which is reserved
Access word btw).

SELECT
T.[Name] As OldestOccupant,
T.Address,
T.City,
T.State,
T.Zip,
T.[Date of Birth] As DOB
FROM
yurtable As T
INNER JOIN
qryMinBirthDate As Q
ON
T.Address = Q.Address
AND
T.City = Q.City
AND
T.State = Q.State
AND
T.Zip = Q.Zip
AND
T.[Date of Birth] = Q.MinDOB;

Of course, you'll have to decide what to
do if more than one name has same minDOB
at a specific address (twins come to mind)...

good luck,

gary
 
Gary

?!were you looking over my shoulder?! What am I thinking right now?!?

(right on the mark)

Regards

Jeff Boyce
Microsoft Office/Access MVP


Gary Walter said:
Not meaning to presume I know for sure
what Jeff meant, but I *think* he was referring
to one technique where you first create a prequery
(say "qryMinBirthDate")

SELECT
Address,
City,
State,
Zip,
Min([Date of Birth]) As MinDOB
FROM
yurtable
GROUP BY
Address,
City,
State,
Zip;

Then, join the above query with your
table on address, city, state, zip, and
DOB to get "Name" (which is reserved
Access word btw).

SELECT
T.[Name] As OldestOccupant,
T.Address,
T.City,
T.State,
T.Zip,
T.[Date of Birth] As DOB
FROM
yurtable As T
INNER JOIN
qryMinBirthDate As Q
ON
T.Address = Q.Address
AND
T.City = Q.City
AND
T.State = Q.State
AND
T.Zip = Q.Zip
AND
T.[Date of Birth] = Q.MinDOB;

Of course, you'll have to decide what to
do if more than one name has same minDOB
at a specific address (twins come to mind)...

good luck,

gary

saelwood said:
Yes, I tried that. I guess I have not explained the result I am seeking
as
well as I could. I want the name and address of the oldest person at
each
address to be displayed so I can mail something to them. When I group by
the
address I get the first name listed at the address, the address, then the
min
date of birth. But sometimes the first person listed does not have the
earliest date of birth. So then I get the name and address of the first
person and the oldest one's birth date.

I did sort by date of birth in the table and then ran the group by
address
and it seemed to work ok, I just wonder if there is a better way that I
am
more confident is working properly.

Thanks for your input.
Click to expand...
Click to expand...
 
Thank you very much Jeff and Gary. Works great. Yes, I do know that 'name'
is reserved, my database actually has first name, last name.

Jeff Boyce said:
Gary

?!were you looking over my shoulder?! What am I thinking right now?!?

(right on the mark)

Regards

Jeff Boyce
Microsoft Office/Access MVP


Gary Walter said:
Not meaning to presume I know for sure
what Jeff meant, but I *think* he was referring
to one technique where you first create a prequery
(say "qryMinBirthDate")

SELECT
Address,
City,
State,
Zip,
Min([Date of Birth]) As MinDOB
FROM
yurtable
GROUP BY
Address,
City,
State,
Zip;

Then, join the above query with your
table on address, city, state, zip, and
DOB to get "Name" (which is reserved
Access word btw).

SELECT
T.[Name] As OldestOccupant,
T.Address,
T.City,
T.State,
T.Zip,
T.[Date of Birth] As DOB
FROM
yurtable As T
INNER JOIN
qryMinBirthDate As Q
ON
T.Address = Q.Address
AND
T.City = Q.City
AND
T.State = Q.State
AND
T.Zip = Q.Zip
AND
T.[Date of Birth] = Q.MinDOB;

Of course, you'll have to decide what to
do if more than one name has same minDOB
at a specific address (twins come to mind)...

good luck,

gary

saelwood said:
Yes, I tried that. I guess I have not explained the result I am seeking
as
well as I could. I want the name and address of the oldest person at
each
address to be displayed so I can mail something to them. When I group by
the
address I get the first name listed at the address, the address, then the
min
date of birth. But sometimes the first person listed does not have the
earliest date of birth. So then I get the name and address of the first
person and the oldest one's birth date.

I did sort by date of birth in the table and then ran the group by
address
and it seemed to work ok, I just wonder if there is a better way that I
am
more confident is working properly.

Thanks for your input.



:

It sounds like you want to group by the address fields and get the Min()
of
the Date Of Birth.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table that lists name, address,city, state, zip, phone, date
of
birth, etc.

I want to send a mailing to the oldest person at each address.

I have tried numerous group bys etc and am not sure it is working
properly.

Help?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
If your database has the literal fieldname of "first name" (and "last
name"), also be aware that spaces in fieldnames can cause problems.


Regards

Jeff Boyce
Microsoft Office/Access MVP

saelwood said:
Thank you very much Jeff and Gary. Works great. Yes, I do know that
'name'
is reserved, my database actually has first name, last name.

Jeff Boyce said:
Gary

?!were you looking over my shoulder?! What am I thinking right now?!?

(right on the mark)

Regards

Jeff Boyce
Microsoft Office/Access MVP


Gary Walter said:
Not meaning to presume I know for sure
what Jeff meant, but I *think* he was referring
to one technique where you first create a prequery
(say "qryMinBirthDate")

SELECT
Address,
City,
State,
Zip,
Min([Date of Birth]) As MinDOB
FROM
yurtable
GROUP BY
Address,
City,
State,
Zip;

Then, join the above query with your
table on address, city, state, zip, and
DOB to get "Name" (which is reserved
Access word btw).

SELECT
T.[Name] As OldestOccupant,
T.Address,
T.City,
T.State,
T.Zip,
T.[Date of Birth] As DOB
FROM
yurtable As T
INNER JOIN
qryMinBirthDate As Q
ON
T.Address = Q.Address
AND
T.City = Q.City
AND
T.State = Q.State
AND
T.Zip = Q.Zip
AND
T.[Date of Birth] = Q.MinDOB;

Of course, you'll have to decide what to
do if more than one name has same minDOB
at a specific address (twins come to mind)...

good luck,

gary

:
Yes, I tried that. I guess I have not explained the result I am
seeking
as
well as I could. I want the name and address of the oldest person at
each
address to be displayed so I can mail something to them. When I group
by
the
address I get the first name listed at the address, the address, then
the
min
date of birth. But sometimes the first person listed does not have
the
earliest date of birth. So then I get the name and address of the
first
person and the oldest one's birth date.

I did sort by date of birth in the table and then ran the group by
address
and it seemed to work ok, I just wonder if there is a better way that
I
am
more confident is working properly.

Thanks for your input.



:

It sounds like you want to group by the address fields and get the
Min()
of
the Date Of Birth.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table that lists name, address,city, state, zip, phone,
date
of
birth, etc.

I want to send a mailing to the oldest person at each address.

I have tried numerous group bys etc and am not sure it is working
properly.

Help?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Hi Jeff,

You are thinking...

You are thinking how you can help Gary
anticipate problems when half our company
finally goes to Office 2003 Pro hitting 2000-format
back ends while the other half either has no Office
(or will go to non-Pro Office 2003) but will
still use 2000 runtime against those same
backends?

No...I didn't really believe you were... 8-)

gary


Jeff Boyce said:
Gary

?!were you looking over my shoulder?! What am I thinking right now?!?

(right on the mark)

Regards

Jeff Boyce
Microsoft Office/Access MVP


Gary Walter said:
Not meaning to presume I know for sure
what Jeff meant, but I *think* he was referring
to one technique where you first create a prequery
(say "qryMinBirthDate")

SELECT
Address,
City,
State,
Zip,
Min([Date of Birth]) As MinDOB
FROM
yurtable
GROUP BY
Address,
City,
State,
Zip;

Then, join the above query with your
table on address, city, state, zip, and
DOB to get "Name" (which is reserved
Access word btw).

SELECT
T.[Name] As OldestOccupant,
T.Address,
T.City,
T.State,
T.Zip,
T.[Date of Birth] As DOB
FROM
yurtable As T
INNER JOIN
qryMinBirthDate As Q
ON
T.Address = Q.Address
AND
T.City = Q.City
AND
T.State = Q.State
AND
T.Zip = Q.Zip
AND
T.[Date of Birth] = Q.MinDOB;

Of course, you'll have to decide what to
do if more than one name has same minDOB
at a specific address (twins come to mind)...

good luck,

gary

saelwood said:
Yes, I tried that. I guess I have not explained the result I am seeking
as
well as I could. I want the name and address of the oldest person at
each
address to be displayed so I can mail something to them. When I group
by the
address I get the first name listed at the address, the address, then
the min
date of birth. But sometimes the first person listed does not have the
earliest date of birth. So then I get the name and address of the first
person and the oldest one's birth date.

I did sort by date of birth in the table and then ran the group by
address
and it seemed to work ok, I just wonder if there is a better way that I
am
more confident is working properly.

Thanks for your input.



:

It sounds like you want to group by the address fields and get the
Min() of
the Date Of Birth.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table that lists name, address,city, state, zip, phone, date
of
birth, etc.

I want to send a mailing to the oldest person at each address.

I have tried numerous group bys etc and am not sure it is working
properly.

Help?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
?!is there an echo in here?!

Jeff

Gary Walter said:
Hi Jeff,

You are thinking...

You are thinking how you can help Gary
anticipate problems when half our company
finally goes to Office 2003 Pro hitting 2000-format
back ends while the other half either has no Office
(or will go to non-Pro Office 2003) but will
still use 2000 runtime against those same
backends?

No...I didn't really believe you were... 8-)

gary


Jeff Boyce said:
Gary

?!were you looking over my shoulder?! What am I thinking right now?!?

(right on the mark)

Regards

Jeff Boyce
Microsoft Office/Access MVP


Gary Walter said:
Not meaning to presume I know for sure
what Jeff meant, but I *think* he was referring
to one technique where you first create a prequery
(say "qryMinBirthDate")

SELECT
Address,
City,
State,
Zip,
Min([Date of Birth]) As MinDOB
FROM
yurtable
GROUP BY
Address,
City,
State,
Zip;

Then, join the above query with your
table on address, city, state, zip, and
DOB to get "Name" (which is reserved
Access word btw).

SELECT
T.[Name] As OldestOccupant,
T.Address,
T.City,
T.State,
T.Zip,
T.[Date of Birth] As DOB
FROM
yurtable As T
INNER JOIN
qryMinBirthDate As Q
ON
T.Address = Q.Address
AND
T.City = Q.City
AND
T.State = Q.State
AND
T.Zip = Q.Zip
AND
T.[Date of Birth] = Q.MinDOB;

Of course, you'll have to decide what to
do if more than one name has same minDOB
at a specific address (twins come to mind)...

good luck,

gary

:
Yes, I tried that. I guess I have not explained the result I am
seeking as
well as I could. I want the name and address of the oldest person at
each
address to be displayed so I can mail something to them. When I group
by the
address I get the first name listed at the address, the address, then
the min
date of birth. But sometimes the first person listed does not have the
earliest date of birth. So then I get the name and address of the
first
person and the oldest one's birth date.

I did sort by date of birth in the table and then ran the group by
address
and it seemed to work ok, I just wonder if there is a better way that I
am
more confident is working properly.

Thanks for your input.



:

It sounds like you want to group by the address fields and get the
Min() of
the Date Of Birth.

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a table that lists name, address,city, state, zip, phone,
date of
birth, etc.

I want to send a mailing to the oldest person at each address.

I have tried numerous group bys etc and am not sure it is working
properly.

Help?

Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top