OK - does childnum increment from 1 to 5 (for 763)
OK - 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.
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
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.
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!!