Creating A query on an existing query. please help

[Aye]

Hi,



I am a newbie in MS Access and I have come across a challenging task (at least on my level) that anyone of you might already solved..

Can anyone tell me how to create a Query with an existing query.
I have attached a snapshot of the Datasheet view of an existing query wherein there are 3 fields; Parents, Child and BirthDate.
I want to create a query that will filter all the Parents that has more than 1 Child and are not born on the same BirthDate. The result should show the same fields (Parents, Child and BirthDate) with the above condition.
In a simple explanation, we can say that those child having the same parents and the same BirthDates are twins or triplets, there is also an Only Child and the others are Parents with more than 2 children. I want to have the list of those Parents with more than 2 children with their BirthDates.
I have more than 12 thousand rows to work on and I want to do this using a MS Access.
Please see the attached snapshot for your reference.

Thanks in advance.
Aye

[KathyCo]

Try this.

You just attach the query just like you would a table. Then copy this into your SQL view. Change name of fields and query as needed.

SELECT Query1.Parents, Query1.Birthdate, Query1.Child
FROM Query1
WHERE (((Query1.Parents) In (SELECT [Parents] FROM [Query1] As Tmp GROUP BY [Parents],[Birthdate] HAVING Count(*)=1 And [Birthdate] <> [Query1].[Birthdate])))
ORDER BY Query1.Parents, Query1.Birthdate;

I hope this helps!

Thanks,

Kathy

[KathyCo]

The result will be different than what you had because F is actually in there 4 times instead of 3. Just wanted to note that so you would not wonder why. I assumed that the Parents field would be unique or it needs to be for what I sent to work.
Thanks,

Kathy

[Aye]

Hi Kathy,

Indeed, you are right, there is an extra child on Parent "F" which was not included on the expected result I posted as reference.
I tried your Code and it worked!!!

Thanks and have a GREAT day ahead.

Regards,
Aye

[KathyCo]

You are welcome. Glad I could help!

Kind Regards,

Kathy