linked fields in a query

[Joseph Greenberg]

I have a membership database with tables I have inherited for which I can't
change the structure.

Table 1 essentially the family membership unit information - member1,
member2 (assume most are married), address, contact info, etc. It includes a
familyname, member1name, member2name, and famno (famno is the overall key).

Table 2 has member details, but doesn't have first names of member1 and
member 2, it only has first names of kids. This is also where birthday
resides (as you would expect, this is where the individual data is).

I have a query that is pulling records based on birthday. I would like it to
show me ONLY the member name which is relevant to the birthday (right now I
can't figure out how to not get both names). So if I select all those under
40, the record in the query shows me both member1name and member2name. But
since the record is pullnig based on birthday in Table 2, theoretically
there should be a way to show only the name of the member with the birthday
that fits the criteria.

There is a field in Table 2 which tells me if it's a member1, member2, or
child. But i can't figure out how to tell the query that if it's a member 1
then give me only member 1 name on the output data, if it's a member 2 only
give me the member 2 name, or if it's member 3 then give me the first name
data.

Any thoughts?

 

[John Spencer]

Use a calculated expression - this is all on one line, but for ease of
understanding I've split the three nested IIF statements onto separate
lines.

IIF([WhichMember]="Member 1",[Member1Field
,IIF([WhichMember]="Member 2"
,[Member2Field],IIF(WhichMember[="Member 3",[Member3Field],Null)))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Joseph Greenberg]

can you please be a little specific about what you are testing here? i
understand the code, i'm just not understanding the logic.

Use a calculated expression - this is all on one line, but for ease of
understanding I've split the three nested IIF statements onto separate
lines.

IIF([WhichMember]="Member 1",[Member1Field
,IIF([WhichMember]="Member 2"
,[Member2Field],IIF(WhichMember[="Member 3",[Member3Field],Null)))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a membership database with tables I have inherited for which I
can't change the structure.

Table 1 essentially the family membership unit information - member1,
member2 (assume most are married), address, contact info, etc. It
includes a familyname, member1name, member2name, and famno (famno is the
overall key).

Table 2 has member details, but doesn't have first names of member1 and
member 2, it only has first names of kids. This is also where birthday
resides (as you would expect, this is where the individual data is).

I have a query that is pulling records based on birthday. I would like it
to show me ONLY the member name which is relevant to the birthday (right
now I can't figure out how to not get both names). So if I select all
those under 40, the record in the query shows me both member1name and
member2name. But since the record is pullnig based on birthday in Table
2, theoretically there should be a way to show only the name of the
member with the birthday that fits the criteria.

There is a field in Table 2 which tells me if it's a member1, member2, or
child. But i can't figure out how to tell the query that if it's a member
1 then give me only member 1 name on the output data, if it's a member 2
only give me the member 2 name, or if it's member 3 then give me the
first name data.

Any thoughts?

 

[John Spencer]

You gave us no field or table names and you did not post the SQL
statement of your query. SO, I constructed an example using names I
made up.

You said you have a field in your query that tells you which member has
the birthday. If that is true then you can use that field (WhichMember)
to determine which field to display - MemberName1, MemberName2, or
ChildName. Again, I was forced to make up names and assume that you did
have all the names in your current query and needed to choose the
appropriate one.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

can you please be a little specific about what you are testing here? i
understand the code, i'm just not understanding the logic.

Use a calculated expression - this is all on one line, but for ease of
understanding I've split the three nested IIF statements onto separate
lines.

IIF([WhichMember]="Member 1",[Member1Field
,IIF([WhichMember]="Member 2"
,[Member2Field],IIF(WhichMember[="Member 3",[Member3Field],Null)))

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I have a membership database with tables I have inherited for which I
can't change the structure.

Table 1 essentially the family membership unit information - member1,
member2 (assume most are married), address, contact info, etc. It
includes a familyname, member1name, member2name, and famno (famno is the
overall key).

Table 2 has member details, but doesn't have first names of member1 and
member 2, it only has first names of kids. This is also where birthday
resides (as you would expect, this is where the individual data is).

I have a query that is pulling records based on birthday. I would like it
to show me ONLY the member name which is relevant to the birthday (right
now I can't figure out how to not get both names). So if I select all
those under 40, the record in the query shows me both member1name and
member2name. But since the record is pullnig based on birthday in Table
2, theoretically there should be a way to show only the name of the
member with the birthday that fits the criteria.

There is a field in Table 2 which tells me if it's a member1, member2, or
child. But i can't figure out how to tell the query that if it's a member
1 then give me only member 1 name on the output data, if it's a member 2
only give me the member 2 name, or if it's member 3 then give me the
first name data.

Any thoughts?