UNION query

[bsmith]

Re: https://www.accessforums.net/access/...tml#post239206

I'm a newbie and I have a similar problem. Mine is exactly as the following:

ID	Parent	Address	Child1	Child1DOB	Child2	Child2DOB	Child3	Child3DOB
1	Sarah	1st St.	F	9/2/2010	E	8/1/2012	B	1/31/2014
2	Mary	9th Cr.	C	3/15/2011	A	1/26/2013	D	5/6/2014
Sorted by Child DOB (Month first and then Day), Printed in the Report as:
	Parent		Child	DOB
	Mary		A	1/26/2013
	Sarah		B	1/31/2014
	Mary		C	3/15/2011
	Mary		D	5/6/2014
	Sarah		E	8/1/2012
	Sarah		F	9/2/2010

I was trying to work with the Query Union function, but so far I'm just not getting it. Sorry :~(



Any help is much appreciated.

[June7]

Your data is not normalized. For some reason I cannot type UNION in the example query. Replace ______ with UNION.

SELECT ID, Parent, Address, 1 AS ChildNum, Child1 AS Child, Child1DOB AS DOB FROM tablename
______ SELECT ID, Parent, Address, 2, Child2, Child2DOB FROM tablename
______ SELECT ID, Parent, Address, 3, Child3, Child3DOB FROM tablename;

[pbaldy]

And given the likelihood that not everyone has the max number of kids I'd filter each, like:

SELECT ID, Parent, Address, 2, Child2, Child2DOB FROM tablename WHERE Child2 Is Not Null

[bsmith]

Great! I'll give that a shot tonight and let you guys know how things go.

[bsmith]

Thanks June7 and pbaldy! It worked! I did some extra stuff, but I could not have done it without your help. On a side note, I changed the pbaldy's suggestion into "SELECT ID, Parent, Address, 2, Child2, Child2DOB FROM tablename WHERE Child2DOB Is Not Null" in case a child's name was entered and not the birthdate.

[June7]

You don't want those children listed if the DOB is not entered? List them and you know data is missing.

[bsmith]

It's all good.

Some parents will provide the child name and DOB, but some think the DOB information should not be shared within our group. So far the way I have it now, this solves it and gives the results for kids and kids with DOB, but not blank fields for people with 1 kid (or less).