Report with entries from a second table RRS feed

  • Question

  • I have created a Report Address list of participants of an event.

    I have a main form with address, financial, and administrative (like Confirmed, Letter sent, etc.) info, and a subform with personal information (name, DOB, passport number, etc.). The Main form is populated from a query based on the Main Info table, the Sub form is populated from the Participants table.

    Some participants are singles and therefore have one phone number in the Main table and Main form, and that phone number is repeated the Participants table and form. For them there is only one Subform/record. Some participants are married and the wife can have a different phone number in Participants. Some participants are families with children, where husband, wife and children each have a subrecord with each their own (cell) phone number.

    The main issue that I am encountering on several things I want to do, occures when I use both tables in a query. They are related "one to many" between Main and Participants. When I use them together in a querry the result lists ALL participants, whereas what I want is to list only the entries in the Main table (so one listing for each family).

    My specific question for the Address list is that I already have this.

    Last Name / all participants in family (manually entered in table/form).

         All address info including the initial registrant's phone number (from the Main Table/query).

    I would like to add to that any participants that have a different phone number as:

         First name - [Phone number Participant]


    The wife does not always has not always registered with a seperate phone number (not necessary for the event). Children (they are all teens) all have their own phone numbers.

    I cannot even begin to think of a way to do this.

    Any help?


    • Edited by DBoydNL Thursday, September 13, 2018 10:16 AM
    Thursday, September 13, 2018 10:16 AM

All replies

  • Hi David,

    It might help if you could post your table structure and some screenshot of your data to help us get a better picture of what you're having difficulties with.

    Just my 2 cents...

    Thursday, September 13, 2018 2:58 PM
  • Here is what the two source tables look like.

    Hoofdscherm contains the family data.
    Deelnemers contains the family member / participant data.
    The use of Keyname is due to that fact that many Dutch Last names have "de" or "van den" as a prefix, and contarary to the way things are done in the US, these are not considered when Alphabetising, so "van den Berg" or "de Bruin" is listed under Berg and Bruin respectively. When duplicate last names occur of people who are not of the same family (could be married brothers), I enter them as Smith and Smith2. 

    I am not sure what sample of the data you would like to see. Again, I am trying to make an address list with a first line that has Family address information from the Hoofdscherm, and a second line below that with the firstname (Roepnaam) and that individual participant's phone number (Telefoon Deelnemer). I think I know how to skip the line for family members, who listed the same phone number as her husband. I just don't know how to avoid having the report list every participant as a new Family entry.

    What I want is this:

    Boyd, David and Crissie. (First names of all participants in a family is in the field [Deelnemers].
         David - 06-12345678
         Crissie - 06-87654321

    What I get is this:

    Boyd, David and Crissie.
         David - 06-12345678
    Boyd, David and Crissie.
         Crissie - 06-87654321

    Does that clarify it?


    • Edited by DBoydNL Friday, September 14, 2018 6:43 AM
    Friday, September 14, 2018 6:39 AM
  • Group the report on Keynaam, and put the Keynaam and Deelnemers data in a group header.   Put the Roepnaam and Telefoon Deelnemer data in the detail section.

    However, personal names can legitimately be duplicated, so should not be used as keys.  The primary key of Hoofdscherm should be an autonumber column e.g. HoofdschermID and Deelnemers should have a corresponding foreign key of the same name, of long integer number data type.  The report should first be ordered by Keynaam and then grouped by HoofdschermID, with the Keynaam and Deelnemers data in the HoofdschermID group header.  This will differentiate two or more families with the same Keynaam  value.

    Ken Sheridan, Stafford, England

    • Marked as answer by DBoydNL Wednesday, September 19, 2018 6:31 AM
    • Unmarked as answer by DBoydNL Thursday, September 20, 2018 8:11 AM
    Sunday, September 16, 2018 10:40 PM
  • Thank you Ken, that lets me do what I want.

    Now, I want to do two other things, which admittedly are merely tweaks to make my report look better and more informative.

    With single participants (indicated by the number 1 in [Deelnemers], I want to eliminate the repetition of the person's first name in front of the phone number. In the Groupheader the Last name and participants names are listed, which in the case of a single person, is simply their first name. It is superfluous to repeat it on the next line in Details.
    So I have a field there with the formula: =IIf([Aantal deelnemers]>1;[Roepnaam]), where [Roepnaam] is the field where firstname is located.
    When I look at the Print Example, it says #Fout ther (which I assume is #Error in English) when there is more than 1 participant (when the condition is met; [Aantal deelnemers] is 1. How wonderful that there is no indication what the error is. I have tried everything I can think of to fix this. If I simply put [Roepnaam] outside of the formual, it is listed. If I put a different field in the TRUE section, that data is displayed. I cannot figure out what I am doing wrong.

    I would like to put Total number of participants in the page footer. This doesn't work. I have placed a field with =Aantal([Roepnaam]) and that gets me: #Fout. If I put that field in Report footer I do get the right number, but now it is listed under the last entry in Details (above the footer). Why can't I do this. Better: how CAN I accomplish this.

    It is obvious that I don't really understand what I am doing, right? Help.


    Wednesday, September 19, 2018 6:44 AM
  • To suppress the Roepnaam value if the Deelnemers control's value is 1 I'd suggest that you bind the Roepnaam control to the column in the usual way, and conditionally hide the control by putting the following in the Format event procedure of the Detail section:

        Me.Roepnaam.Visible = (Me.Deelnemers > 1)

    You cannot use an aggregation operator like Sum in a control in a page footer, only in a group or report footer or header.  To show the overall number of rows returned, first put a text box, txtTotal say, in the report header and set its controlSource property to:


    Set its Visible property to False (No) to hide the control.  Then in the page footer add another text box with a ControlSource property of:


    Ken Sheridan, Stafford, England

    Wednesday, September 19, 2018 11:18 AM
  • Okay, even though I am fluent in both English and Dutch, I am now encountering the problem that you have a UK version of Access and I have a Dutch version.

    Perhaps you can send me a screenshot of "putting the following in the Format event procedure of the Detail section" I cannot find a Dutch equivalent/translation of that, nor am I sure that I sould be looking in the properties.

    I am still curious why my method with Iif did not work. It shows every indication that it should (when I use a field other than Roepnaam), but it doesn't. Don't ge me wrong, I am happy to try your suggestion, I am simply also curious. Also the "Me.Roepnaam" and "Me.Deelnemers" (and actually that entire phrase baffles me. I can copy & paste just like anybody, but then I am just perpetuating my ignorance, and not learning to do things myself.

    In regards to the counter, it doesn't work, nor do I understand why it would.

    I am also not sure what you mean by "bind the Roepnaam control to the column in the usual way." Bind? That may be why it ain't workin'.
    Other than "binding" I did exactly what you said: put a text box in the Report Header - the "label" is txtTotal, the controlsource property is =Count(*), which my version of Access automatically changes to =Aantal(*).
    I put another text box (with label) in the Page footer, removed the label and entered =[txtTotal] in the controlSource property.

    When I select Print Preview I get this error (translated from Dutch): The MS Database engine does not recognize txtTotal as a valid fieldname or expression. I tried retyping the field name (just in case copying and pasting it from here did not work... no joy. I see no reason why this would work, what is the first command counting? Probably whatever you want me to "bind" it to, but I am clueless.



    Wednesday, September 19, 2018 12:07 PM
  • This is how it would look in one of the reports in my DatabaseBasics demo:

    To open the event procedure select the report's detail section in design view, and in its property sheet, select On Format in the Events tab.  Click on the 'build' button (the one on the right with three dots) and select 'Code Builder' in the dialogue.

    A control is 'bound' to a column (field) in a form or report's recordset by setting the ControlSource property of the control to the name of the column.  In the above    Roepnaam and Deelnemers  are the names of the controls.  The names might or not be the same as the names of the columns to which they are bound, but if they differ, the names of the controls should be used in the code.

    As regards the totalling, txtTotal should be the name of a hidden text box control in the report header with a ControlSource property of =Count(*).    This control is not bound.  The expression used in its ControlSource property calls the built in Count operator, and by the use of the asterisk, it counts the number of detail rows returned by the report.

    Ken Sheridan, Stafford, England

    • Edited by Ken Sheridan Wednesday, September 19, 2018 12:47 PM Typo corrected.
    Wednesday, September 19, 2018 12:44 PM
  • Ken,

    Your screen shot is VBA, right? I am not using VBA, I don't understand VBA, never touch the stuff. Why can't what I want be done withour VBA with an expression like I ended up with in the Event Procudure, like I do in other places?

    In regards to the totalling. I am pretty sure I got the first part right: text box with "txtTotal" in the label and =Aantal(*) in the ControlSource property. This is not the one giving the error. It is the one at the bottom, which as far as I can tell I entered just as you instructed.


    Wednesday, September 19, 2018 1:41 PM
  • You said earlier, "With single participants (indicated by the number 1 in [Deelnemers]".  Then you said "=IIf([Aantal deelnemers]>1;[Roepnaam])".  So what is the control's Name property, [Deelnemers] or [Aantal deelnemers]?  If it's the former the expression would be:

        = IIf([Deelnemers] > 1,Roepnaam], Null)

    If it's the latter:

        = IIf([Aantal deelnemers] > 1,Roepnaam], Null)

    Note that txtTotal is the Name property of the unbound control in the report header, not its label.

    Ken Sheridan, Stafford, England

    Wednesday, September 19, 2018 2:19 PM
  • That field started out being [Roepnaam] (first name), and so the Control name property is still [Roepnaam]

    Then I decided I didn't want the first name displayed if the person is a single (it is already listed one line up).

    So in Properties I clicked on ControlEventSource and came up with my IF statement:
    =IIf([Aantal deelnemers]>1;[Roepnaam])

    I read that as follows: If the field [Aantal deelnemers] is greater than one, then display [Roepnaam] else display nothing. In Word and Excel's formulae as well as in Basic if you didn't need an "untrue" action, you could just leave it out of the IF-Then-Else formula. Apparently, you are using NULL as a "don't do anything clause"?

    Also you twice gave the Roepnaam field without a opening bracket, which I am assuming is a typo that you ended up copying and pasting. Also, the little bit of help I get from Access when I type in an expression, directs me to use a semicolon as a seperator between clauses of the expression. I see you use comma's. Is that my problem? See, I am feeling a severe lack of confidence in my latent programming skills, so I have to ask. I am using Access 2016 (Office Pro).

    So now I have this (copied and pasted from Access): =IIf([Aantal deelnemers]>1;[Roepnaam];Null). Near as I can tell this is what you have in your previous post (sans spaces, which Access removes if I try to put them in), and with semicolons, rather than comma's (if I use comma's I get a missing operand error or something like that). This is also what I have had from the begining and it just gives me #fout instead of the first name, but for singles it neatly skips the first name (as intended). So the IF formula is working, it is just not displaying [Roepnaam]. As I said before, If i put the field [Roepnaam] right behind the formule (so outside the parentheses) then it displays Roepnaam just fine.

    Here is an overview of what I've got going. What am I doing wrong?

    BTW I removed the actual text box from the Page footer that is supposed to show txtTotal, since it gave me an error and then I couldn't see Print Preview. One problem at a time.


    Wednesday, September 19, 2018 2:54 PM
  • You refer to 'fields'.  A report or form does not have fields, it has controls.  Fields (columns) are in tables or queries.  A bound control can have the same name as the field to which it is bound, so if Aantal deelnemers is the name of both the control and the field to which it is bound your expression should work (I assume that the use of semi-colons as the delimiters, rather than commas is a difference in the Dutch version of Access).

    If the name of the control differs from that of the field to which its bound, i.e. is not Aantal deelnemers, then you should use the name of the control in the expression.

    Null is the absence of a value.  You could also use the constant vbNullString, or a zero-length string "".

    Brackets are only needed around object names which include spaces or other special characters.  Microsoft unwisely allows this in Access, but experienced developers use CamelCase or represent a space by an underscore character like_this.  The visual design interface adds the brackets automatically in most contexts, whether they are needed or not.  If in doubt use them.  Putting the third argument outside the parentheses doesn’t make any sense to me, but it might be something to do  with the difference between the Dutch and English versions.  If I do it I get an 'argument not optional' error.

    For an example of totals in a report's page footer take a look at in my public databases folder at:!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file illustrates how to put both page totals and an overall total in a page footer, using Northwind data as an example.

    Ken Sheridan, Stafford, England

    Wednesday, September 19, 2018 4:27 PM
  • I am very much influenced by my use of formula's in Word's Mailmerge. There all the items that come to me through an Access query are called "fields". Since I routinely use my Dutch version, I was at a loss to describe them with the proper English phraseology. I will try to get it right.

    So in my report the control I want to subject to a simple IIf is called Roepnaam.
    You say "If the name of the control differs from that of the field to which its bound, i.e. is not Aantal deelnemers, then you should use the name of the control in the expression." I think that is what I have done. The control called Roepnaam has the following expression: =IIf([Aantal deelnemers]>1;[Roepnaam];Null). I have said repeatedly that I am confused and have no idea what Access expects me to do. I do not understand why this IIF (nor any of my previous ones) doesn't work.

    The only reason I added [Roepnaam] behind the expression was to show it upon Print Preview, to prove to myself that there was actually data in [Roepnaam] that should be displayed in my IIf formula. After it did, I removed it, since it indeed makes no sense to have it there. It was just a test. The fact that the FALSE part of the formula is reached and works (no name is displayed when [Aantal deelnemers] <2 seems to indicate that Access is getting past the criteria to the TRUE and FALSE results, but is failing to display [Roepnaam]. I don't understand why not? Am I missing some syntax?

    In regards to the NULL, vbNullString and "" (quote quote), are you saying that Access requires the FALSE section of an IIf formula to be included, when neither Basic, Excel or Word require that (in other words: common sense)? As I stated in my last post, I added the NULL as you described it, but that generated a new error.

    As with so many things in Access, I am sighing all the time: this should be easier. Like: why are the edges of controls, text boxes, squares & rectangles and even lists of tables in queries so horribly sensitive and precise that it takes me forever to find the "sweet spot" with my mouse to enlarge it? When I use Ctrl Cursor to precisely position a control in Reports or in a Form, why does the box always move 1 notch to the right and one notch up. All I expect it to do is move one notch to the right. I waste so much time twinkying with stuff that should be so much easier to accomplish. Okay, rant over. Sorry. Back to my problem: Every explanation you give, seems to verify to me that I am doing it right, but it won't work. I am obviously missing something, but I won't know what until you tell me. That is basically why I included an overview screen shot in my last post. What am I doing wrong?


    Wednesday, September 19, 2018 4:53 PM
  • Access differs radically from other members of the Office suite in that it is a development environment.  While in Word or Excel for instance, a user can just open the application and immediately create and save a new worksheet of document,  Access provides the environment in which database applications are developed.  The users then open the database in which the developer has provided them with the means of modelling the real world situation with which the database is concerned.  The user has no need to be familiar with the development environment, but the developer must be comfortable in that environment, which means not only being familiar with Access itself, and, if the application is to go beyond the trivial, the VBA and SQL languages which it uses, but also with the principles of the database relational model itself, including concepts like normalization and functional dependency.  It would be foolish to claim that the learning curve is a shallow one, but it is within the compass of anyone of reasonable intelligence.

    Your expression, =IIf([Aantal deelnemers]>1;[Roepnaam];Null), allowing for the apparent difference in the delimiter character between your version and mine, is syntactically correct.  I see no reason why the control whose ControlSource property is the expression should not be empty (Null) if the value of the Aantal deelnemers is greater than 1, or the current value of Roepnaam otherwise.  If it is not doing so, then there is something preventing it from doing so, which is not apparent from the information available.

    Regarding the need for all three arguments of the IIF function here's what happens in the immediate pane:

    a = 2
    b = 3
    c = 4
    ? IIF(a = 1,b,c)

    ? IIF(a = 1,b)
    raises an 'Argument not optional' compile error

    ? IIF(a = 1,b,)
    raises an 'Expected: expression' error

    And BTW, the use of a semi-colon as a delimiter character….
    ? IIF(a = 1;b;c)
    raises an 'Expected: list separator or )' compile error.

    On the subject of moving controls be sure to make use of the 'Size/space' and 'Align' facilities in the Arrange ribbon.  Where appropriate, these make the relative positioning and sizing of controls a lot easier than manipulating them manually with the mouse or keyboard.

    Ken Sheridan, Stafford, England

    Wednesday, September 19, 2018 8:11 PM
  • Ken,

    You explain (with no real reason why) that Access is radically different than other Office applications. The point at hand, was my obserevation that you seemed to be saying that an UNTRUE result always has to be included for an IIf expression to work. My complaint on that matter (should it be so) was, that this is a departure in SQL (and possibly VBA - again I don't use VBA) from the other Office applications. I understand that Access is Access, and not Word or Excel, but my reasoning was that SQL is SQL, why would it work differently in Access than in the other Office applications. More on that down below.

    On the one hand you say that "The user has no need to be familiar with the development environment" (I saw the "but"), and do not defince what this new phrase ("development environment") is, but then in the rest of the paragraph you list about 5 major items that the user needs to be familiar with. Sure, that is a steep learning curve! I have been working with a relatively complex Access database for 25 years. Initially, the required procedure was beyond my understanding, and I had a friend fix my issues with some small chunks of VBA and other corrections, which at the  time and to this day I gladly accept without understanding what he has done. I have been able to use the data in that database for complex MailMerges and a variety of queries and reports over the years. I would never consider myself knowledgeable about Access, but I can generally "make my database do what I want". I would consider myself "of reasonable intelligence," but that is not helping me to understand what is going on.

    So finally you seem to conclude the same thing I have said nearly all along: I see no reason why my IIF expression does not work. That's nice, except that I don't see a solution to the problem ("there is something preventing it from doing so, which is not apparent from the information available"). Great. Will I encounter that "something" later on? How can I figure out what is wrong (how many times have I asked that question?).

    I have to say, Ken, that you explain things in a way that is well above my level of understanding Access, and you also fail to explain what I really need to know, and have asked several times. For instance, I have asked outright whether an IIf expression requires the final "ELSE" or "UNTRUE" statement in Access in order to work. I cannot discern an answer in all that you have said. Chalk that up to me not being "reasonably intelligen" if you must, but could you just give this dummy a straight "yes" or "no" answer?! I have added the NULL parameter for the UNTRUE section of the expression, but it still does not work.

    As I have mentioned, I am fluent in both English (I am an American) and Dutch (I have lived in the Netherlands most of my life). However, a sentence like "Regarding the need for all three arguments of the IIF function here's what happens in the immediate pane." means nothing to me because I have no idea what "the immediate pane" means. Churchill said that America and Great Brittain are two countries divided by a common language. Perhaps that phrase (immediate pane) means something in British English, it means nothing in American English.

    What follows after that makes sense to me, even though the syntax is suddenly extremely strange. You have done this twice, suddenly leaving off brackets, and now with these oddly phrased IIf expressions. I really don't know why you are suddenly telling me which mistakes in IIf expressions generate which errors? More importantly, none of it answers my question: Must I have an UNTRUE clause in an IIf expression in Access for it to work?
    We have discussed the delimiters (comma or semicolon), and you suggested that this is possibly a matter of my Dutch version versus your UK version. Does the UK (version) use decimal points or decimal comma's? That could be the issue, I could evaluate a result with a decimal in it, but if the comma is seen as a delimiter, that would really mess things up. Yet you tell me in your last post "And BTW, the use of a semi-colon as a delimiter character….
    ? IIF(a = 1;b;c) raises an 'Expected: list separator or )' compile error." You do not include any clarifying statement like "in my UK version". So I am left to wonder: "What is Ken trying to tell me?" I don't know.

    I am not sure how you are trying to solve the "nudge" or "bump" problem I described by using the Arrange ribbon. If I want two controls to have no space between them or even slightly overlap each other, then the perfect way to do that is to place the control (either with some kind of Autoarrange function or with the mouse) and then nudge it exactly to where I want that particular control. It is annoying when a Ctrl RightArrow "nudge" always nudges the object up and to the right one "notch". Each time it requires me to remember to nudge it back down. I paid good money for Access, this should be easier. Actually: this should not be allowed to happen.

    We have been going around and around since Sunday, September 16, 2018 10:40 PM on the matter if my non functioning IIf expression. Nothing has been accomplished in all the posts, other than that you now agree with me: there seems to be no reason why my IIf expression should not work. Plus I still don't know whether the UNTRUE statement is required by Access.

    I have completely dropped my second (or third) question about giving a Total number of participants, since it didn't seem to work to discuss two seperate questions. That doesn't mean that I no longer have that question, or have any understanding of what to do to make that work. I am just trying to get the problem of eliminating a single participator's first name (Roepnaam) from a line in my report. Again: that should be a whole lot easier.

    If you are sensing frustration in my post, you are right. Please remember that I am a lay person when it comes to Access. I do understand SQL to a certain extent. My English is good, but your answers are at times very complicated (couched in English Access terminology), hard to follow, and (I am sorry to say), seem to have little to do with my problem. The language difference does cause problems in that certain controls and properties are not translated literally, so when you mention one of those, at times I have no idea what that is.


    Thursday, September 20, 2018 8:10 AM
  • Today I opened the database and the report, and noticed for the first time, that there was a warning symbol to the left of the control in question. Okay, my bad, although in my defense: in retrospect that warning "sign" IS shown on the last screenshot I uploaded, and Ken also did not notice it. When I hovered over it, it stated that the control element refers to itself (again: I am translating). Well, I thought that was what Ken told me I was supposed to do. One of the suggestions Access gave was to change the name of the control element. So I did that: changing the name to "Voornaam" (Firstname), a name not being used for anything in the database.

    Again my "this should be easier" complaint rears it's head. Without me indicating that it should do so, Access also changed the name of the control (or is it now an "expression") that it is suppsed to display if the criteria is TRUE, from [Roepnaam] to [Voornaam], which does not exist. Obviously, this generated a #Fout again in Print preview. Rather than assume that this also did not work, I persisted and checked the formula/expression and noticed the wrong control name, changed it, and now the Report is working as I want it to. At this point I am just barely interested in why it works.

    Perhaps now I can bring up how to add up the participants and place that information in the Report Footer? What was suggested earlier either does not work, or (more likely) I did not understand and therefore did not enter the controls correctly.


    Friday, September 21, 2018 10:28 AM