Page 2 of 2 FirstFirst 12
Results 16 to 21 of 21
  1. #16
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    OK - does childnum increment from 1 to 5 (for 763)

  2. #17
    fetster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    24
    Quote Originally Posted by Ajax View Post
    OK - so what about the first query - where childnum is created - are they showing the correct data, and does childnum increment from 1 to 5 (for 763)

    Childnum does not seem to be incrementing. 763 is showing Child 5 for all the Childnum fields. 768 is showing Child2 for both records.

  3. #18
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I've just checked your query and you are missing some quotations - it should be

    SELECT HeadMemberID, "," & Forename & "," & Surname & "," & DateOfBirth AS ChildDetail, "Child" & DCount("*","Person","HeadMemberID=" & HeadMemberID & "AND PersonID <=" & PersonID) AS Childnum
    FROM Person
    WHERE HeadMemberID<>0;

    correct fro this and try again

  4. #19
    fetster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    24
    Quote Originally Posted by Ajax View Post
    I've just checked your query and you are missing some quotations - it should be

    SELECT HeadMemberID, "," & Forename & "," & Surname & "," & DateOfBirth AS ChildDetail, "Child" & DCount("*","Person","HeadMemberID=" & HeadMemberID & "AND PersonID <=" & PersonID) AS Childnum
    FROM Person
    WHERE HeadMemberID<>0;

    correct fro this and try again

    This works great. I just need to get it to also include the [Parent First Name] [Parent Surname] [Parent Email]. At present it is showing the HeadMemberID and then the relevant child details so I need to swap the HeadMemberID for Forename, Surname and EmailAddress fields.



  5. #20
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    link the headMemberID to the personID of another copy of the person table (just drag it on to the query screen - it will automatically be aliased as Person_1 to differentiate it). Then drag down the required fields to the query grid

  6. #21
    fetster is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Apr 2016
    Posts
    24
    Quote Originally Posted by Ajax View Post
    link the headMemberID to the personID of another copy of the person table (just drag it on to the query screen - it will automatically be aliased as Person_1 to differentiate it). Then drag down the required fields to the query grid

    Thanks very much for all your help with this. I really appreciate it - I owe you a beer!!

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Linking fields between a query and table
    By Access_Novice in forum Access
    Replies: 3
    Last Post: 09-13-2014, 11:52 PM
  2. Replies: 8
    Last Post: 06-27-2014, 11:30 AM
  3. Create a Query that won't mess up when linking a new table
    By DavidMichaelangelo in forum Queries
    Replies: 12
    Last Post: 11-25-2013, 12:24 PM
  4. Replies: 5
    Last Post: 06-14-2012, 07:27 PM
  5. Trouble linking a table to a query
    By wtubell in forum Queries
    Replies: 1
    Last Post: 03-26-2009, 06:34 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums