Calculate age as of date in other table

[Guest]

I have a DOB field in table1 and an Age field in table1 as well.

In table2, I have a field that contains a date that respondents answered
specific questionnaires.

For historical purposes, I would like to capture the age of the person at
the time one questionnaire in particular was answered in the Age field of
table1.

I'm not sure how to go about this, especially since I need the questionnaire
date to be for a specific questionnaire, i.e. questionnaireID 05 specifically.

I have searched high and low and can't seem to find anything like this. Any
help would be greatly appreciated!

 

[Douglas J. Steele]

You haven't really given us much to go on, but to calculate age at a
particular date in time, you'd use

DateDiff("yyyy", [DOB], [SpecificDate]) - _
IIf(Format([SpecificeDate], "mmdd") < Format([DOB], "mmdd"), 1, 0)

In general, you shouldn't store ages. I suppose that since this particular
age will never change, it might be okay to store it instead of the DOB.

 

[Guest]

Thanks for your response. I'm sorry, I thought I had provided enough
information.

I guess where I am having difficulty is referencing the Interview Date field
in another table. I'll try to be more clear:

tblParticipants
pptID (autonumber PK)
First
Last
DOB
etc.

tblResponses
responseID (autonumber PK)
pptID (fk)
questionnaireID (fk) (there are several questionnaires)
interviewDate
interviewLanguage
interviewerID (fk)
etc.

I need to determine the Age of the participant as of the (tblResponses)
interviewDate of questionnaireID **5** only, putting the result in the
tblParticipants.

I realize that this is unusual, but for historical purposes only, plus the
fact that the data will be exported to a statistical package that requires
this field, it's really the only choice I have.

Thanks so much for your help - I hope I have provided enough detail.



--
Thanks!

Dee

You haven't really given us much to go on, but to calculate age at a
particular date in time, you'd use

DateDiff("yyyy", [DOB], [SpecificDate]) - _
IIf(Format([SpecificeDate], "mmdd") < Format([DOB], "mmdd"), 1, 0)

In general, you shouldn't store ages. I suppose that since this particular
age will never change, it might be okay to store it instead of the DOB.

 

[Douglas J. Steele]

You could create a query along the lines of:

SELECT responseID, pptID, questionnaireID,
interviewDate,
DateDiff("yyyy", tblParticpants.DOB, tblResponses.interviewDate) -
IIf(Format(tblResponses.interviewDate, "mmdd") < Format(tblParticipants.DOB,
"mmdd"), 1, 0) AS Age,
interviewLanguage, interviewerID, ...
FROM tblResponses INNER JOIN tblParticipants
ON tblResponses.pptID = tblParticipants.pptID
WHERE questionnaireID = 5
UNION
SELECT responseID, pptID, questionnaireID,
interviewDate,
Null,
interviewLanguage, interviewerID, ...
FROM tblResponses
WHERE questionnaireID <> 5

What this would do is give you an additional field Age that would only be
populated for Question 5.

You'd then be able to use that query anywhere you would otherwise have used
tblResponses.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for your response. I'm sorry, I thought I had provided enough
information.

I guess where I am having difficulty is referencing the Interview Date
field
in another table. I'll try to be more clear:

tblParticipants
pptID (autonumber PK)
First
Last
DOB
etc.

tblResponses
responseID (autonumber PK)
pptID (fk)
questionnaireID (fk) (there are several questionnaires)
interviewDate
interviewLanguage
interviewerID (fk)
etc.

I need to determine the Age of the participant as of the (tblResponses)
interviewDate of questionnaireID **5** only, putting the result in the
tblParticipants.

I realize that this is unusual, but for historical purposes only, plus the
fact that the data will be exported to a statistical package that requires
this field, it's really the only choice I have.

Thanks so much for your help - I hope I have provided enough detail.



--
Thanks!

Dee

You haven't really given us much to go on, but to calculate age at a
particular date in time, you'd use

DateDiff("yyyy", [DOB], [SpecificDate]) - _
IIf(Format([SpecificeDate], "mmdd") < Format([DOB], "mmdd"), 1, 0)

In general, you shouldn't store ages. I suppose that since this
particular
age will never change, it might be okay to store it instead of the DOB.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

I have a DOB field in table1 and an Age field in table1 as well.

In table2, I have a field that contains a date that respondents
answered
specific questionnaires.

For historical purposes, I would like to capture the age of the person
at
the time one questionnaire in particular was answered in the Age field
of
table1.

I'm not sure how to go about this, especially since I need the
questionnaire
date to be for a specific questionnaire, i.e. questionnaireID 05
specifically.

I have searched high and low and can't seem to find anything like this.
Any
help would be greatly appreciated!

 

[yanwanli4622]

You haven't really given us much to go on, but to calculate age at a
particular date in time, you'd use

DateDiff("yyyy", [DOB], [SpecificDate]) - _
IIf(Format([SpecificeDate], "mmdd") < Format([DOB], "mmdd"), 1, 0)

In general, you shouldn't store ages. I suppose that since this particular
age will never change, it might be okay to store it instead of the DOB.

 

[Guest]

I posted back yesterday, but just checked and see that for some reason it was
never posted.

I seem to be doing something wrong with the code you sent - I can't seem to
get it to work.

The code I have been using generates what I want when I view it in
datasheet, but I can't seem to apply it to a control on my form - the control
just stays blank. Here is the code:

SELECT tbl_participants.ppt_id,
Int(DateDiff("d",[ppt_dob],[completion_date])/365.25) AS Age
FROM tbl_questionnaires INNER JOIN (tbl_participants INNER JOIN
tbl_responses ON tbl_participants.ppt_id = tbl_responses.ppt_id) ON
tbl_questionnaires.qstnaire_id = tbl_responses.qstnaire_id
WHERE (((tbl_responses.qstnaire_id)=2));

Any help is greatly appreciated! Thanks so much.

--
Thanks!

Dee

You could create a query along the lines of:

SELECT responseID, pptID, questionnaireID,
interviewDate,
DateDiff("yyyy", tblParticpants.DOB, tblResponses.interviewDate) -
IIf(Format(tblResponses.interviewDate, "mmdd") < Format(tblParticipants.DOB,
"mmdd"), 1, 0) AS Age,
interviewLanguage, interviewerID, ...
FROM tblResponses INNER JOIN tblParticipants
ON tblResponses.pptID = tblParticipants.pptID
WHERE questionnaireID = 5
UNION
SELECT responseID, pptID, questionnaireID,
interviewDate,
Null,
interviewLanguage, interviewerID, ...
FROM tblResponses
WHERE questionnaireID <> 5

What this would do is give you an additional field Age that would only be
populated for Question 5.

You'd then be able to use that query anywhere you would otherwise have used
tblResponses.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for your response. I'm sorry, I thought I had provided enough
information.

I guess where I am having difficulty is referencing the Interview Date
field
in another table. I'll try to be more clear:

tblParticipants
pptID (autonumber PK)
First
Last
DOB
etc.

tblResponses
responseID (autonumber PK)
pptID (fk)
questionnaireID (fk) (there are several questionnaires)
interviewDate
interviewLanguage
interviewerID (fk)
etc.

I need to determine the Age of the participant as of the (tblResponses)
interviewDate of questionnaireID **5** only, putting the result in the
tblParticipants.

I realize that this is unusual, but for historical purposes only, plus the
fact that the data will be exported to a statistical package that requires
this field, it's really the only choice I have.

Thanks so much for your help - I hope I have provided enough detail.



--
Thanks!

Dee

You haven't really given us much to go on, but to calculate age at a
particular date in time, you'd use

DateDiff("yyyy", [DOB], [SpecificDate]) - _
IIf(Format([SpecificeDate], "mmdd") < Format([DOB], "mmdd"), 1, 0)

In general, you shouldn't store ages. I suppose that since this
particular
age will never change, it might be okay to store it instead of the DOB.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a DOB field in table1 and an Age field in table1 as well.

In table2, I have a field that contains a date that respondents
answered
specific questionnaires.

For historical purposes, I would like to capture the age of the person
at
the time one questionnaire in particular was answered in the Age field
of
table1.

I'm not sure how to go about this, especially since I need the
questionnaire
date to be for a specific questionnaire, i.e. questionnaireID 05
specifically.

I have searched high and low and can't seem to find anything like this.
Any
help would be greatly appreciated!

 

[Douglas J. Steele]

That approach to calculating age is flawed.

I don't understand what you mean that you "can't seem to apply it to a
control on my form". How are you trying to use it?

For that matter, what's the problem with the code I gave you? "can't seem to
get it to work" doesn't tell me much.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I posted back yesterday, but just checked and see that for some reason it
was
never posted.

I seem to be doing something wrong with the code you sent - I can't seem
to
get it to work.

The code I have been using generates what I want when I view it in
datasheet, but I can't seem to apply it to a control on my form - the
control
just stays blank. Here is the code:

SELECT tbl_participants.ppt_id,
Int(DateDiff("d",[ppt_dob],[completion_date])/365.25) AS Age
FROM tbl_questionnaires INNER JOIN (tbl_participants INNER JOIN
tbl_responses ON tbl_participants.ppt_id = tbl_responses.ppt_id) ON
tbl_questionnaires.qstnaire_id = tbl_responses.qstnaire_id
WHERE (((tbl_responses.qstnaire_id)=2));

Any help is greatly appreciated! Thanks so much.

--
Thanks!

Dee

You could create a query along the lines of:

SELECT responseID, pptID, questionnaireID,
interviewDate,
DateDiff("yyyy", tblParticpants.DOB, tblResponses.interviewDate) -
IIf(Format(tblResponses.interviewDate, "mmdd") <
Format(tblParticipants.DOB,
"mmdd"), 1, 0) AS Age,
interviewLanguage, interviewerID, ...
FROM tblResponses INNER JOIN tblParticipants
ON tblResponses.pptID = tblParticipants.pptID
WHERE questionnaireID = 5
UNION
SELECT responseID, pptID, questionnaireID,
interviewDate,
Null,
interviewLanguage, interviewerID, ...
FROM tblResponses
WHERE questionnaireID <> 5

What this would do is give you an additional field Age that would only be
populated for Question 5.

You'd then be able to use that query anywhere you would otherwise have
used
tblResponses.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Thanks for your response. I'm sorry, I thought I had provided enough
information.

I guess where I am having difficulty is referencing the Interview Date
field
in another table. I'll try to be more clear:

tblParticipants
pptID (autonumber PK)
First
Last
DOB
etc.

tblResponses
responseID (autonumber PK)
pptID (fk)
questionnaireID (fk) (there are several questionnaires)
interviewDate
interviewLanguage
interviewerID (fk)
etc.

I need to determine the Age of the participant as of the (tblResponses)
interviewDate of questionnaireID **5** only, putting the result in the
tblParticipants.

I realize that this is unusual, but for historical purposes only, plus
the
fact that the data will be exported to a statistical package that
requires
this field, it's really the only choice I have.

Thanks so much for your help - I hope I have provided enough detail.



--
Thanks!

Dee


:

You haven't really given us much to go on, but to calculate age at a
particular date in time, you'd use

DateDiff("yyyy", [DOB], [SpecificDate]) - _
IIf(Format([SpecificeDate], "mmdd") < Format([DOB], "mmdd"), 1, 0)

In general, you shouldn't store ages. I suppose that since this
particular
age will never change, it might be okay to store it instead of the
DOB.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a DOB field in table1 and an Age field in table1 as well.

In table2, I have a field that contains a date that respondents
answered
specific questionnaires.

For historical purposes, I would like to capture the age of the
person
at
the time one questionnaire in particular was answered in the Age
field
of
table1.

I'm not sure how to go about this, especially since I need the
questionnaire
date to be for a specific questionnaire, i.e. questionnaireID 05
specifically.

I have searched high and low and can't seem to find anything like
this.
Any
help would be greatly appreciated!

 

[Guest]

Sorry, I should have explained that. The Age field doesn't contain any values.

Here is the code as per your instructions, with one minor modification. I
kept getting error 3079, so in the SELECT part, identified pp_id as coming
from tbl_responses. Maybe that's the problem?

SELECT response_id, tbl_responses.ppt_id, qstnaire_id, completion_date,
DateDiff("yyyy",[tbl_participants].[ppt_dob],[tbl_responses].[competion_date])-IIf(Format([tbl_responses].[completion_date],"mmdd")<Format([tbl_participants].[ppt_dob],"mmdd"),1,0)
AS Age, interviewer_id, start_time, end_time, interview_lang
FROM tbl_responses INNER JOIN tbl_participants ON tbl_responses.ppt_id =
tbl_participants.ppt_id
WHERE qstnaire_id=2 UNION SELECT response_id, ppt_id, qstnaire_id,
completion_date, Null,interviewer_id, start_time, end_time, interview_lang
FROM tbl_responses WHERE qstnaire_id <> 2;
--
Thanks!

Dee

That approach to calculating age is flawed.

I don't understand what you mean that you "can't seem to apply it to a
control on my form". How are you trying to use it?

For that matter, what's the problem with the code I gave you? "can't seem to
get it to work" doesn't tell me much.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I posted back yesterday, but just checked and see that for some reason it
was
never posted.

I seem to be doing something wrong with the code you sent - I can't seem
to
get it to work.

The code I have been using generates what I want when I view it in
datasheet, but I can't seem to apply it to a control on my form - the
control
just stays blank. Here is the code:

SELECT tbl_participants.ppt_id,
Int(DateDiff("d",[ppt_dob],[completion_date])/365.25) AS Age
FROM tbl_questionnaires INNER JOIN (tbl_participants INNER JOIN
tbl_responses ON tbl_participants.ppt_id = tbl_responses.ppt_id) ON
tbl_questionnaires.qstnaire_id = tbl_responses.qstnaire_id
WHERE (((tbl_responses.qstnaire_id)=2));

Any help is greatly appreciated! Thanks so much.

--
Thanks!

Dee

You could create a query along the lines of:

SELECT responseID, pptID, questionnaireID,
interviewDate,
DateDiff("yyyy", tblParticpants.DOB, tblResponses.interviewDate) -
IIf(Format(tblResponses.interviewDate, "mmdd") <
Format(tblParticipants.DOB,
"mmdd"), 1, 0) AS Age,
interviewLanguage, interviewerID, ...
FROM tblResponses INNER JOIN tblParticipants
ON tblResponses.pptID = tblParticipants.pptID
WHERE questionnaireID = 5
UNION
SELECT responseID, pptID, questionnaireID,
interviewDate,
Null,
interviewLanguage, interviewerID, ...
FROM tblResponses
WHERE questionnaireID <> 5

What this would do is give you an additional field Age that would only be
populated for Question 5.

You'd then be able to use that query anywhere you would otherwise have
used
tblResponses.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks for your response. I'm sorry, I thought I had provided enough
information.

I guess where I am having difficulty is referencing the Interview Date
field
in another table. I'll try to be more clear:

tblParticipants
pptID (autonumber PK)
First
Last
DOB
etc.

tblResponses
responseID (autonumber PK)
pptID (fk)
questionnaireID (fk) (there are several questionnaires)
interviewDate
interviewLanguage
interviewerID (fk)
etc.

I need to determine the Age of the participant as of the (tblResponses)
interviewDate of questionnaireID **5** only, putting the result in the
tblParticipants.

I realize that this is unusual, but for historical purposes only, plus
the
fact that the data will be exported to a statistical package that
requires
this field, it's really the only choice I have.

Thanks so much for your help - I hope I have provided enough detail.



--
Thanks!

Dee


:

You haven't really given us much to go on, but to calculate age at a
particular date in time, you'd use

DateDiff("yyyy", [DOB], [SpecificDate]) - _
IIf(Format([SpecificeDate], "mmdd") < Format([DOB], "mmdd"), 1, 0)

In general, you shouldn't store ages. I suppose that since this
particular
age will never change, it might be okay to store it instead of the
DOB.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a DOB field in table1 and an Age field in table1 as well.

In table2, I have a field that contains a date that respondents
answered
specific questionnaires.

For historical purposes, I would like to capture the age of the
person
at
the time one questionnaire in particular was answered in the Age
field
of
table1.

I'm not sure how to go about this, especially since I need the
questionnaire
date to be for a specific questionnaire, i.e. questionnaireID 05
specifically.

I have searched high and low and can't seem to find anything like
this.
Any
help would be greatly appreciated!

 

[Guest]

Just realized how incredibly stupid I look. Obviously it worked - I had made
a typo.

I'm really sorry - thanks very much for your patience. Now I need to add
this to a control on my form so that the age is displayed when all other info
is being input, linking it via the ppt_id on the form.
--
Thanks!

Dee

That approach to calculating age is flawed.

I don't understand what you mean that you "can't seem to apply it to a
control on my form". How are you trying to use it?

For that matter, what's the problem with the code I gave you? "can't seem to
get it to work" doesn't tell me much.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I posted back yesterday, but just checked and see that for some reason it
was
never posted.

I seem to be doing something wrong with the code you sent - I can't seem
to
get it to work.

The code I have been using generates what I want when I view it in
datasheet, but I can't seem to apply it to a control on my form - the
control
just stays blank. Here is the code:

SELECT tbl_participants.ppt_id,
Int(DateDiff("d",[ppt_dob],[completion_date])/365.25) AS Age
FROM tbl_questionnaires INNER JOIN (tbl_participants INNER JOIN
tbl_responses ON tbl_participants.ppt_id = tbl_responses.ppt_id) ON
tbl_questionnaires.qstnaire_id = tbl_responses.qstnaire_id
WHERE (((tbl_responses.qstnaire_id)=2));

Any help is greatly appreciated! Thanks so much.

--
Thanks!

Dee

You could create a query along the lines of:

SELECT responseID, pptID, questionnaireID,
interviewDate,
DateDiff("yyyy", tblParticpants.DOB, tblResponses.interviewDate) -
IIf(Format(tblResponses.interviewDate, "mmdd") <
Format(tblParticipants.DOB,
"mmdd"), 1, 0) AS Age,
interviewLanguage, interviewerID, ...
FROM tblResponses INNER JOIN tblParticipants
ON tblResponses.pptID = tblParticipants.pptID
WHERE questionnaireID = 5
UNION
SELECT responseID, pptID, questionnaireID,
interviewDate,
Null,
interviewLanguage, interviewerID, ...
FROM tblResponses
WHERE questionnaireID <> 5

What this would do is give you an additional field Age that would only be
populated for Question 5.

You'd then be able to use that query anywhere you would otherwise have
used
tblResponses.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Thanks for your response. I'm sorry, I thought I had provided enough
information.

I guess where I am having difficulty is referencing the Interview Date
field
in another table. I'll try to be more clear:

tblParticipants
pptID (autonumber PK)
First
Last
DOB
etc.

tblResponses
responseID (autonumber PK)
pptID (fk)
questionnaireID (fk) (there are several questionnaires)
interviewDate
interviewLanguage
interviewerID (fk)
etc.

I need to determine the Age of the participant as of the (tblResponses)
interviewDate of questionnaireID **5** only, putting the result in the
tblParticipants.

I realize that this is unusual, but for historical purposes only, plus
the
fact that the data will be exported to a statistical package that
requires
this field, it's really the only choice I have.

Thanks so much for your help - I hope I have provided enough detail.



--
Thanks!

Dee


:

You haven't really given us much to go on, but to calculate age at a
particular date in time, you'd use

DateDiff("yyyy", [DOB], [SpecificDate]) - _
IIf(Format([SpecificeDate], "mmdd") < Format([DOB], "mmdd"), 1, 0)

In general, you shouldn't store ages. I suppose that since this
particular
age will never change, it might be okay to store it instead of the
DOB.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


I have a DOB field in table1 and an Age field in table1 as well.

In table2, I have a field that contains a date that respondents
answered
specific questionnaires.

For historical purposes, I would like to capture the age of the
person
at
the time one questionnaire in particular was answered in the Age
field
of
table1.

I'm not sure how to go about this, especially since I need the
questionnaire
date to be for a specific questionnaire, i.e. questionnaireID 05
specifically.

I have searched high and low and can't seem to find anything like
this.
Any
help would be greatly appreciated!