Query: find most recent birthdate (day/month only) and insert person name into table (1 Viewer)

[lmh24]

Hello there,

I am attempting to design a survey questionnaire in Access 2010, and this is the first time I've used Access in several years. My SQL is rusty, and my VBA skills are introductory.

Currently I need to figure out a way to do the following tasks to create an overall function:

1) Select from a set of records in a Subform (with a many-to-one relationship to the Parent form) the person with the most recent date of birth THIS YEAR, i.e. by month and day only where that birthday < current date

2) Do this only if two other conditions are met: a) person was born in another country; and b) person is >=18;

And 3) Insert that person's name into a table so that the name can be pulled into a field on a subsequent form related to the same Parent record

Each record has a unique "File" number that serves as the primary key, and in the subform there is a "Member" number that serves as the primary key, so "File.Member" is the relationship between the two.

See pseudo-code below for crude example:

while FILE=x;
for HHMEMNO 1 to N (where N=total number of household members);
if (FBORN=1 & AGE>=18) . . .
generate time[n]=time between survey month/day and birth month/day,
select member in household with
smallest value for time, and insert his/her NAME in Access field in
subsequent screen.
x=x+1;

I would appreciate any assistance that may be offered!

 

[VilaRestal]

File-Member can;t be the relationship in a one to many if their both primary keys. The subtable must have a foreign key field it relates to File.

The pseudo code doesn;t make much sense: What's all this While File=x ... x = x + 1 business? Some sort of loop? But you only want to insert one value/record?

Not knowing the name of the table or fields you want to insert into, and assuming you actually want to insert the person's ID (Member) not their name (you can look up their name from their ID), something like this would do it:

CurrentDb.Execute "INSERT INTO Table1 (Member) " _
& "SELECT TOP 1 Member FROM tblPerson WHERE FBORN = 1 AND FK_File = " _
& Me.File & " AND DateDiff('yyyy',DoB,Date()) >= 18 ORDER BY DoB DESC"

And really that should be an update if it's just one value. Why have a whole record inserted for that?

But actually why do eithre? Why not just look it up with select statement? That way it will always be up to date.

 

[lmh24]

VilaRestal,

Thank you for your reply. To clarify, this is a survey questionnaire, so the idea is that with every new household an interviewer approaches, they will be opening a new record for that household, which will be associated with a unique file number (FILE). They will then ask the respondent to list all of the members in the household and answer a few questions about those household members -- this is the information in the subform. The remainder of the questions (in the parent and subsequent forms) will only be about the individual respondent.

In other words, each household will have a single FILE number (primary key), but this will need to be associated with the foreign key (my mistake) in the subform containing records for every household member. There may be several records in the subform (MEMBER as foreign key) corresponding to the same FILE (primary key).

What I need is a way to identify the person in each household we approach who last had a birthday (month, day) each time we approach a new household. I then want that person's name (pulling from the subform field HHNAME) to appear in the SUBJECT field in subsequent forms that make up the rest of the individual questionnaire.

This is where the x+1 comes in, though it may not be the appropriate designation - I need to select the HHNAME (name) or HHMEMNO (member number) from the subform (Household_Roster) for the person who 1) last had a birthday; 2) was born in another country; and 3) is >=18 for each record in the ADULT form (which corresponds to the FILE primary key) and insert it into SUBJECT field in the ADULT form as the data are entered.

I see this as a button in the form that populates the SUBJECT field with the subject's name based on the conditional selection indicated above.

I don't know if that clarifies or confuses, but hopefully the former.

 

[VilaRestal]

Ah ok. So it does update not insert. Still I'm not convinced it can't just be a calculated field rather than a stored one.

Either way the select part of it will be similar to what I did before. Again I still don;t know the names of all the fields. But here goes:

"SELECT TOP 1 HHMEMNO FROM Household_Roster " _
& "WHERE FBORN = 1 AND FILE = " & Me.FILE & " AND AGE >= 18 AND " _
& "(BDAY_MONTH < DatePart('m',Date()) OR " _
& "(BDAY_MONTH = DatePart('m',Date()) AND BDAY_DAY <= DatePart('d',Date()))) " _
& "ORDER BY BDAY_MONTH DESC, BDAY_DAY DESC"

You can then either put that into an Update SQL command or update an existing field on the form (even if it's hidden).