choosing between options

[bhrosey via AccessMonster.com]

Hello. I'm creating a church directory and I have it set up so that below
their picture it shows their names and then the names of their children. In
my table I have a place to list their children, however, some members want
their grown children listed (they are not pictured) or a spouse that is not
pictured. So what i thought I would do, is in my table add fields so that if
the children are pictured they would go in ChildName field and if they are
NOT pictured they would go in the ChNotPic field. My question is this, when
I write my query, how do I do it so that if ChildName is Null, use ChNotPic
instead.

Thanks for the help

 

[Clifford Bass]

Hi,

To answer your question, you could do something like:

IIf(IsNull(ChildName), ChNotPic, ChildName)

However, it sounds to me that your tables may need some significant
redesigning. No reason to put that information in different fields. You
should be using a main people table and a relations table to relate the
various people within the people table.

tblPeople
PersonID
LastName
FirstName
PictureFileName
etc.

tblRelations
PersonID (The PersonID of someone with a relation)
RelationTypeCode (A code from a relation type codes table)
RelatedPersonID (The PersonID of the related person)

When you set up your report, you can set up a subreport that pulls up
the children to report as identified by the the relation type and and/or the
lack of a picture. Your subreport's data source might look something like
this:

Select A.PersonID, C.PersonID, C.LastName, C.FirstName
From (tblPeople As A Inner Join tblRelations As B On B.PersonID =
A.PersonID) Inner Join tblPeople As C On C.PersonID = B.RelatedPersonID
Where C.PictureFileName is null;

Hope that helps,

Clifford Bass

 

[bhrosey via AccessMonster.com]

Thanks Cliff, I'll try the first suggestion. I understand what you're saying
about the tables, however maybe I didn't explain it in enough detail. The
picture is a family portrait, it shows the parents and children so if I
simply list the children, their names show up on the report even if they are
not in the picture. I need to be able to put them in different place based
on whether or not they are in the photo.

Thanks

 

[bhrosey via AccessMonster.com]

that worked exactly like I wanted, thank you very much. Maybe you can answer
another question for me. Some people do not want their birth year listed,
can I make that optional somehow in the input mask or do I need to do
something else?

Hi,

To answer your question, you could do something like:

IIf(IsNull(ChildName), ChNotPic, ChildName)

However, it sounds to me that your tables may need some significant
redesigning. No reason to put that information in different fields. You
should be using a main people table and a relations table to relate the
various people within the people table.

tblPeople
PersonID
LastName
FirstName
PictureFileName
etc.

tblRelations
PersonID (The PersonID of someone with a relation)
RelationTypeCode (A code from a relation type codes table)
RelatedPersonID (The PersonID of the related person)

When you set up your report, you can set up a subreport that pulls up
the children to report as identified by the the relation type and and/or the
lack of a picture. Your subreport's data source might look something like
this:

Select A.PersonID, C.PersonID, C.LastName, C.FirstName
From (tblPeople As A Inner Join tblRelations As B On B.PersonID =
A.PersonID) Inner Join tblPeople As C On C.PersonID = B.RelatedPersonID
Where C.PictureFileName is null;

Hope that helps,

Clifford Bass

Hello. I'm creating a church directory and I have it set up so that below
their picture it shows their names and then the names of their children. In

[quoted text clipped - 7 lines]

Thanks for the help

 

[Clifford Bass]

Hi,

You are welcome!

In the case of family portraits you still could do something like the
layout I suggested. If only one person from each family has the picture
listed, such as the head of the family, your main report would list everyone
with a picture, and the subreport would list everyone else listed as being
related.

Clifford Bass

 

[Clifford Bass]

Hi,

Great! Sure can. Probably I would add a boolean field to your person
table that would store a true or false as to whether or not to withhold the
birth year. Then in the report you could set the control's record source to:

=IIf([Withhold_Birth_Year], Null, [Birth_Year])

Clifford Bass

 

[bhrosey via AccessMonster.com]

what's a boolean field? Sorry not familiar with that

Hi,

Great! Sure can. Probably I would add a boolean field to your person
table that would store a true or false as to whether or not to withhold the
birth year. Then in the report you could set the control's record source to:

=IIf([Withhold_Birth_Year], Null, [Birth_Year])

Clifford Bass

that worked exactly like I wanted, thank you very much. Maybe you can answer
another question for me. Some people do not want their birth year listed,
can I make that optional somehow in the input mask or do I need to do
something else?

 

[Clifford Bass]

Hi,

Ah, I forgot that Access calls it a Yes/No field. A boolean field is
one that can have one of two values: true/false or yes/no or on/off. These
are all stored internally in Access as -1/0.

Clifford Bass