Can someone with SQL knowledge help me out?

wouldn't learn anything, and it looks like home work to me. Though moderately difficult homework I'll admit.

The trick with these sort of queries where your schema doesn't match your usage requirements is to break them up.

First thing to do is to chop out all the records not for Celia and Kate, ie get rid of Bob and Mary.

Then to get the days where Celia and Kate both got a present e.g.
Select [Date],Count(date) as NumberOfTimes From KateandCelia Group By [Date]

Then join that back to KateAndCelia on Date where NumberOfTimes > 1

Then select Date, Sum(Presents) From Above.

The trick is to get the Kate and Celia records first, otherwise you'll be all over the place.

In access you'd just create intermediate queries and use them in a cascade, and it might be easier for you to develop that way.

In raw SQL you could use temporary tables either with select into or by creating them and using Insert #someable select ...

or there's this nifty trick

Select [Date] From (Select [Date],Count(*) as SomeField From MyTable) dummyTableName Where SomeField > 1

dummyTableName can be anything (as long as it's unique), without it you get a syntax error.

Do it in bits though, check the bits, don't try and blurt it all in one go.

HtHs

PS whoever set you this question, get them to stop using sql keywords for sql objects, it's a [PIA].

PPS there is is an easier way but it's an inflatable dartboard solution

You could get all the records for Celia, and all the ones for Kate and then do a join on date which would give you somnething like
Kate 3/3/2011 7 Celia 3/3/2011 10
...
...
Then select Date, KatePresent + CeliaPresent from above
But if the next question is for Bob and Celia you have a problem...

Optimising it for 100k records depends on the database, access is going to suck next to say SQL Server whatever you do.

http://www.codeguru.com/forum/showthread.php?t=514542

Books:
http://www.amazon.com/SQL-Relational-Theory-Write-Accurate/dp/0596523068

select date, name, sum(presents) as amount
from data
where date in (
SELECT Data.DATE
FROM Data
GROUP BY Data.DATE
HAVING Count(Data.PRESENTS)>1
) and (Data.NAME="kate" Or Data.NAME="celia")
group by date,name

Here it is, for every date where both Kate and Celia received presents show how many presents they received each.

select c.date,sum(c.presents) CeliaPresents,
sum(k.presents) KatePresents from
(select date,presents from PRESENT where name
='Cella') c ,
(select date,presents from PRESENT where name
='Kate') k
where k.date=c.date group by c.date order by c.date ;