IIF question

[papabear5]

I have a predesigned database, from a program for tracking address/contact information (so I can't change the database structure), where I enter an individual and then in a different field for that record I enter the Spouse's name and Children's names. When I look at the Tables, for this program, it list each individual (primary person, spouse, and children) on a separate lines. What it does is have a field, in the Table for each individual, called Type. If the Type = "0" then that is the primary person in the address record. If the Type = "1" then that is the spouse of the primary person. Then Type = "2" is the individual names of children of the primary person.

I created a Query that captures every individual and their Type on a separate line, but what I am trying to do now is create a report that gives me the Primary person (Type = "0"), and in the same Section, the Spouse (Type = "1") and the Children (Type = "2"). But I have no idea how to create the three necessary IIF statements to get the right information into the section. Also how do I deal with 1, 2, 3, 4, or no children?

A second part of this question is that I have some labels in each section for each field (e.g. Address:, Cell Phone:, etc.) so when I don't have any information in a field (say I don't have their cell phone number), how do I eliminate that label (Cell Phone:) and field (cell phone information) from the report (just for that record) so I can shorten up the report and only report actual information?

[NeoPa]

I have a predesigned database, from a program for tracking address/contact information (so I can't change the database structure), where I enter an individual and then in a different field for that record I enter the Spouse's name and Children's names. When I look at the Tables, for this program, it list each individual (primary person, spouse, and children) on a separate lines. What it does is have a field, in the Table for each individual, called Type. If the Type = "0" then that is the primary person in the address record. If the Type = "1" then that is the spouse of the primary person. Then Type = "2" is the individual names of children of the primary person.

Yes. This sounds like a standard, properly normalised (Normalisation and Table structures), database structure.

I created a Query that captures every individual and their Type on a separate line, but what I am trying to do now is create a report that gives me the Primary person (Type = "0"), and in the same Section, the Spouse (Type = "1") and the Children (Type = "2"). But I have no idea how to create the three necessary IIF statements to get the right information into the section. Also how do I deal with 1, 2, 3, 4, or no children?

I presume all the children are type='2's.

You could have a look at Combining Rows-Opposite of Union. Standard structures do not allow for dynamically adding or removing fields from a query, although this is handled specially, in Access, by CrossTab queries. These are a special case though, and not standard SQL.

A second part of this question is that I have some labels in each section for each field (e.g. Address:, Cell Phone:, etc.) so when I don't have any information in a field (say I don't have their cell phone number), how do I eliminate that label (Cell Phone:) and field (cell phone information) from the report (just for that record) so I can shorten up the report and only report actual information?

As earlier, the design of the report is not something that can change between records. You can kludge it by putting things together in a single output field, you can even handle hiding controls when a page is formatted, but you're getting into quite tricky territory here whichever way you consider playing this.

[papabear5]

Thanks NeoPa, but not sure if this accomplishes the result I am trying to achieve. I can't change the database structure as the database is used by the "Address Program" and is looking for specific fields in specific Tables.

What I need to do is work with the database as it stands. The best I can do is create a SQL Query to pull out the data from the couple of Tables, where the data is located, and then I need to figure out how to pull the "Types"; that represent the primary person, spouse, and children (in a particular family - membership in a specific family is determined by a GroupID) and then put information related to the specific member of the family into particular Text Boxes in a Report. At the end of this is the SQL View of the Query that I use (pulls from two Tables - (GuestNames and GroupIDs) - and I have attached a spreadsheet with what the Query looks like (in a Zip file). I also attached a image of what the Report is that I am trying to create (in the same Zip file). As you can see when you go down through Work E-mail this would work fine. It is when I try to get data to complete the values for the Spouse and Children information that I have a problem.

Basically, as you can see from the Spreadsheet, the information for each individual in the family is duplicated over-and-over. So I am trying to have the Primary person (the "0" Type in a specific GroupID) show up in the report (record) and rather than having the "1" and "2" Type individuals show up as separate records in the Report I want just their First Names, and for the spouse the Cell # and E-mail address, to show up as fields in the record of the Primary Person (on the report).

I am thinking a number of If-Then statements with the expression might do it. Essentially If Type = "1" Then put GuestNames.FNam e into Spouse Name box, GuestNames.PMPh one in Spouse Cell box, and GuesNames.Perso nalEMail in Spouse E-mail box. Also If Type = "2" Then put GuestNames.FNam e into first Children's Name box, and if there is a second Type "2" in that specific GroupID Then put it into the second Children's Name box. BUT, I would also NOT want to print a record for Type "1" and "2" as we would already be capturing the information from them I would want in the printed record for Type "0".

Any detailed guidance would be greatly appreciated.

SQL VIEW of the Query:

SELECT   GuestNames.GroupID,
         GuestNames.Type,
         GuestNames.GTitle,
         GuestNames.GFName,
         GuestNames.GLName,
         GuestNames.GSuffix,
         GuestNames.GCName,
         GroupIDs.Address,
         GroupIDs.City,
         GroupIDs.State,
         GroupIDs.PostalCode,
         GroupIDs.Country,
         GroupIDs.HPhone,
         GuestNames.PMPhone,
         GuestNames.WPhone,
         GuestNames.PersonalEMail,
         GuestNames.BMPhone,
         GuestNames.BusinessEMail,
         GuestNames.Birthday,
         GroupIDs.Anniversary,
         GroupIDs.Notes

FROM     GroupIDs INNER JOIN GuestNames
  ON     GroupIDs.GroupID = GuestNames.GroupID

WHERE    (((GuestNames.GuestOf)=0))

ORDER BY GuestNames.GLName,
         GuestNames.GFName,
         GuestNames.Type,
         GuestNames.Birthday;

[NeoPa]

I'm generally happy to provide guidance on topics that aren't already covered PapaBear, and I'm happy to do a certain amount of hand-holding as required, but the first step here is to handle getting the data from multiple records into a single output record with a list. This is not supported natively by SQL, so you probably ought to look at the thread that I linked you to for this. I don't want to repeat all that here, as it's just a waste of time. If, after going through it and attempting to implement it you find you have questions then I will answer them as well as I can.

When you have reached the point where you have the children names in a list field, then we can look at separating them out into a number of resultant fields. This will not be dynamic though. You will need to define a set maximum number of children to show, and they will always be available.

[papabear5]

Thanks NeoPa, I appreciate the direction. I have tried to go through the thread that you referenced (and a couple of others), but I am not really a Programmer (it's been a couple of years and that was Java Script) -- more a User with a need. I can follow it through a certain degree, I can see what you are trying to do but I get lost with different functions and a lot of the terminology in the examples. I just don't where to go from here :o(

[NeoPa]

I hear you're a user rather than a coder. That's not a problem in itself, but it can lead to some. This is mainly a site for developers helping developers. I don't want to get too much into doing things for members. My interest (and the direction of the site) is to help members to help themselves. The scope otherwise would be simply too vast, and the interest would be much less for our volunteers. What I'm really trying to say is that although I can provide some hand-holding, there will definitely be a requirement of you to get into this to a certain extent (with your designer/coder hat on). Ultimately it will be little use to you unless you have at least a basic understanding of what's what.

We can look at this at a more detailed level if that would help. I can even answer questions that you have related to the matter. I will need quite specific questions though, related to the concept as given in the other thread.

As I implied in an earlier post, your method will need to include a query which returns all the values connected together into a single string value. Later, when displaying, or even in another query based on that one, we can separate the elements out into disparate fields.