Query to linking Records together in same table

[fetster]

Hi

I have a 'Person' table with approx 76,000 records of people from our EPOS system who have agreed to sign up to an email database. It has details of all our customers ID, Name, Date of Birth, Head Members Email address etc. The database is part of an admission system and has parent and associated childrens details. The children are linked to an adult HeadMember via a HeadmemberID field (The children and adult records all sit in the same table).

For Example:

PersonID FirstName Surname HeadMemberID DOB Email
0000001 Johnny Bloggs 0000008 01/12/2008 daddybloggs@email.com
0000002 Jessie Bloggs 0000008 11/03/2004 daddybloggs@email.com
0000003 Jacob Bloggs 0000008 06/02/2004 daddybloggs@email.com
0000008 Daddy Bloggs 01/01/1980 daddybloggs@email.com (The headmember does not have a Headmember ID if that helps)

Some HeadMembers will have only 1 associated child record while others could have upto 6 associated children.

I want to create a query which will create a table that I can import into mailchimp in the following format:

[Parent First Name] [Parent Surname] [Parent Email] [Child 1 First Name] [Child 1 Surname] [Child 1 DOB] [Child 2 First Name] [Child 2 Surname] [Child 2 DOB] [Child 3 First Name] [Child 3 Surname] [Child 3 DOB] [Child 4 First Name] [Child 4 Surname] [Child 4 DOB]




Is this possible to do in Access? If so how? (I'm a complete novice sorry).

Thanks for taking the time to read my problem.

[CJ_London]

anything is possible

1. create a query something like

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

lets call it Children. This lists all your children data - since this will be going to a text file, you might need to change the commas to something else but include them - including the first one - if they need to be in square brackets then modify accordingly. I've also used dcount rather than the faster subquery because this data will be used in a crosstab - and crosstabs and subqueries do not get on.

2. create the next query to add the headmember

SELECT FirstName & "," & Surname  & "," & Email AS HeadMember, ChildDetail
FROM Person LEFT JOIN Children ON Person.PersonID=Children.HeadMemberID
WHERE Person.HeadmemberID=0

Now you can convert this to a crosstab -

HeadMember woud be a row heading
Childnum would be the column heading
ChildDetail would be the Value (use first rather than sum)

you may have to set headings

3. save the crosstab as say allDetail then create a new query which uses alldetail

SELECT Headmember &  nz(Child1) & nz(Child2) & nz(Child3) & nz(Child4) as linedetail
FROM alldetail

You should now see all values as defined, separated by commas on one line and in one field. Note if nulls still need to be represented separated by commas, change

....& nz(Child1) &....
to

....& nz(Child1,",,,") &....

[NTC]

Assuming that the email defines the family, and that the difference between parent & child is the date, and that the difference between child1 & child2 is the date, etc. Then, primarily for test-ability and sanity - I might set up a series of discrete queries so I can inspect everything at each step. The approach I suggest is very manual grunt intensive and would only work for a limited number of kids such as you have - but it is nicely segmented into discrete data sets to review.

Set up an aggregate query grouped on the email field and set the date field to Min - and this should return just the parents. Save that query

Then make a new query of the table joined to the parents query using the No Match feature and that will return a set with just kids. Save that query

Then make an aggregate query of the kids query grouped on the email field and set the date field to Min - and this will return the 1st child. Save that query.

Etc...repeat for all kids...

If one had an unlimited or a lot of kids - this approach isn't feasible. One would go with a cross tab query but the challenge would identifying and managing the order by date - I would think it to be do-able but would require crafting the sql code correctly which I can't do off the cuff.

In any case this doesn't imply any limitation of Access - regardless of the brand database you are changing the structure of your data on the fly and presenting data side-by-side when it is fundamentally stored in rows is typically a major effort that often times is really not worth the effort. If one is willing to view the data stacked in rows, as it is stored - non of this needs to be done.

[CJ_London]

NTC makes a good point - the field I am using to determine first child is based on the autonumber field - so if entered in a different order first child would not be first born. To fix this change this bit

DCount("*","Person","HeadMemberID=" & HeadMemberID & " AND PersonID<=" & PersonID) AS Childnum

to this

DCount("*","Person","HeadMemberID=" & HeadMemberID & " AND DOB<=" & DOB) AS Childnum

[ssanfu]

I want to create a query which will create a table that I can import into mailchimp in the following format:

[Parent First Name] [Parent Surname] [Parent Email] [Child 1 First Name] [Child 1 Surname] [Child 1 DOB] [Child 2 First Name] [Child 2 Surname] [Child 2 DOB] [Child 3 First Name] [Child 3 Surname] [Child 3 DOB] [Child 4 First Name] [Child 4 Surname] [Child 4 DOB]

Have you seen:
Concatenate values from related records
http://www.allenbrowne.com/func-concat.html

[fetster]

Hi Ajax,

I've only just been able to work on this project again sorry.

I've created the first query called Children:
SELECT HeadMemberID, "," & Forename & "," & Surname & "," & DateOfBirth AS ChildDetail, "Child" & DCount("*","Person","HeadMemberID=" & HeadMemberID & "AND PersonID <=PersonID") AS Childnum
FROM Person
WHERE HeadMemberID<>0;

I'm now trying to create the next query (below) but it is just returning an empty table

SELECT Forename & "," & Surname & "," & EmailAddress AS HeadMember, Children.ChildDetail, Person.EmailAddress
FROM Person LEFT JOIN Children ON Person.PersonID = Children.HeadMemberID
WHERE Person.HeadmemberID=0

Am i doing something wrong? (Sorry i'm still very new to Access / SQL.

Regarding your post below, it doesn't matter which child is shown as the first child, I just need to show them all connected to the correct headmember.

Thanks again for your help.

[CJ_London]

based on this from your first post

0000008 Daddy Bloggs 01/01/1980 daddybloggs@email.com (The headmember does not have a Headmember ID if that helps)

try

WHERE Person.HeadmemberID is null

[fetster]

Hi Ajax

I've managed to get step 2 to work adding the headMember details so I now get a table with 3 columns : HeadMember (which shows FirstName, Surname and Email), ChildDetail (which shows ,ChildFirstName,ChildSurname,ChildDOB), EmailAddress The next row shows child 2 details etc which I assume is correct.

I think I'm going wrong somewhere with the crosstab as it's producing a table with:
HeadMemberID, Total Of Child Detail (,FirstName,Surname,DOB), it then has columns Child1 ..... Child26 with 1 of the columns showing the information this is shown in the Total Of Child Detail field

The SQL for the crosstab i have is:
TRANSFORM First(Children.[ChildDetail]) AS FirstOfChildDetail
SELECT Children.[HeadMemberID], First(Children.[ChildDetail]) AS [Total Of ChildDetail]
FROM Children
GROUP BY Children.[HeadMemberID]
PIVOT Children.[Childnum];


Is the above correct? (Thanks again for your assistance by the way)

[CJ_London]

don't think you need this

First(Children.[ChildDetail]) AS [Total Of ChildDetail]

[fetster]

The problem seems to be that it is only showing 1 child for each HeadMemberID. THe child details will either show in the Child 1 .... Child 9 Columns. None of the rows are showing more than 1 child data. It has also reduced the records from 83,000 rows to 32,000.

[CJ_London]

think it is time for you to show a few rows of the result you get from the query you called children in post#6

I would expect to see something like (leaving out irrelevant fields)

HeadmemberID...Childnum
1.......................Child1
1.......................Child2
1.......................Child3
2.......................Child1
3.......................Child1
3.......................Child2
4......etc

[fetster]

Could well be a problem with this query to be honest asd the data does not look as though it is linking correct Children to HeadMemberID (surnames are different and Childnum is same)

HeadMemberID ChildDetail Childnum
763 ,Joanne,Smith,20/4/1983 Child5
763 ,John,Jones,25/03/1988 Child5
763 ,Faye,Martins,19/08/2004 Child5
763 ,Kasey,Burns,12/06/2006 Child5
763 ,Amie,Jones,28/02/2013 Child5
768 ,Andrew,Kennedy,23/12/1984 Child2
786 ,Nathan,Walls,01/08/2009 Child2
771 ,Kate,Kyle,24/7/1984 Child3
771 ,Ava,Partridge,23/02/2014 Child3
771 ,Steve,Kyle,02/06/2005 Child3
..........
........

[CJ_London]

OK, so look at the children table records for 763 and see if they are right there. If they are, then that means there is something wrong with the query(s). So check the first one (where childnum is calculated), again, just for 763

[fetster]

OK, so look at the children table records for 763 and see if they are right there. If they are, then that means there is something wrong with the query(s). So check the first one (where childnum is calculated), again, just for 763

Hi Ajax, The details the children query is creating are correct and they are linked to the correct HeadMemberID.

[CJ_London]

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)