473,320 Members | 1,861 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

IIF question

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?
Nov 29 '09 #1
5 1392
NeoPa
32,556 Expert Mod 16PB
@PapaBear
Yes. This sounds like a standard, properly normalised (Normalisation and Table structures), database structure.
@PapaBear
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.
@PapaBear
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.
Nov 29 '09 #2
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.FName into Spouse Name box, GuestNames.PMPhone in Spouse Cell box, and GuesNames.PersonalEMail in Spouse E-mail box. Also If Type = "2" Then put GuestNames.FName 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:
Expand|Select|Wrap|Line Numbers
  1. SELECT   GuestNames.GroupID,
  2.          GuestNames.Type,
  3.          GuestNames.GTitle,
  4.          GuestNames.GFName,
  5.          GuestNames.GLName,
  6.          GuestNames.GSuffix,
  7.          GuestNames.GCName,
  8.          GroupIDs.Address,
  9.          GroupIDs.City,
  10.          GroupIDs.State,
  11.          GroupIDs.PostalCode,
  12.          GroupIDs.Country,
  13.          GroupIDs.HPhone,
  14.          GuestNames.PMPhone,
  15.          GuestNames.WPhone,
  16.          GuestNames.PersonalEMail,
  17.          GuestNames.BMPhone,
  18.          GuestNames.BusinessEMail,
  19.          GuestNames.Birthday,
  20.          GroupIDs.Anniversary,
  21.          GroupIDs.Notes
  22.  
  23. FROM     GroupIDs INNER JOIN GuestNames
  24.   ON     GroupIDs.GroupID = GuestNames.GroupID
  25.  
  26. WHERE    (((GuestNames.GuestOf)=0))
  27.  
  28. ORDER BY GuestNames.GLName,
  29.          GuestNames.GFName,
  30.          GuestNames.Type,
  31.          GuestNames.Birthday;
Attached Files
File Type: zip Address-Query-Report.zip (27.5 KB, 73 views)
Nov 30 '09 #3
NeoPa
32,556 Expert Mod 16PB
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.
Nov 30 '09 #4
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(
Dec 1 '09 #5
NeoPa
32,556 Expert Mod 16PB
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.
Dec 1 '09 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Mohammed Mazid | last post by:
Can anyone please help me on how to move to the next and previous question? Here is a snippet of my code: Private Sub cmdNext_Click() End Sub Private Sub cmdPrevious_Click() showrecord
3
by: Stevey | last post by:
I have the following XML file... <?xml version="1.0"?> <animals> <animal> <name>Tiger</name> <questions> <question index="0">true</question> <question index="1">true</question> </questions>
7
by: nospam | last post by:
Ok, 3rd or is it the 4th time I have asked this question on Partial Types, so, since it seems to me that Partial Types is still in the design or development stages at Microsoft, I am going to ask...
3
by: Ekqvist Marko | last post by:
Hi, I have one Access database table including questions and answers. Now I need to give answer id automatically to questionID column. But I don't know how it is best (fastest) to do? table...
10
by: glenn | last post by:
I am use to programming in php and the way session and post vars are past from fields on one page through to the post page automatically where I can get to their values easily to write to a...
10
by: Rider | last post by:
Hi, simple(?) question about asp.net configuration.. I've installed ASP.NET 2.0 QuickStart Sample successfully. But, When I'm first start application the follow message shown. ========= Server...
53
by: Jeff | last post by:
In the function below, can size ever be 0 (zero)? char *clc_strdup(const char * CLC_RESTRICT s) { size_t size; char *p; clc_assert_not_null(clc_strdup, s); size = strlen(s) + 1;
56
by: spibou | last post by:
In the statement "a *= expression" is expression assumed to be parenthesized ? For example if I write "a *= b+c" is this the same as "a = a * (b+c)" or "a = a * b+c" ?
2
by: Allan Ebdrup | last post by:
Hi, I'm trying to render a Matrix question in my ASP.Net 2.0 page, A matrix question is a question where you have several options that can all be rated according to several possible ratings (from...
3
by: Zhang Weiwu | last post by:
Hello! I wrote this: ..required-question p:after { content: "*"; } Corresponding HTML: <div class="required-question"><p>Question Text</p><input /></div> <div...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.