Joining two count results

[Adam Pedder]

I've got a problem with a query that I'm sure should be quite simple.

Basically I have a table that looks like this:

Date | Agent
01/01/2001 | 12345
01/02/2001 | 12345
01/01/2001 | 12346

An I want to return this:

Count 1 | Agent | Count 2
1 | 12345 | 2
1 | 12346 | 1

Where Count 1 is the number of claims since 01/02/2001 and Count 2 is the
number of claims since 01/01/2001.

Count 1 is easy with Select Count(*), agent from Claims where date >
'01/02/2001' group by agent.but I cannot get Count 2 in there.

I'm at the point of using a temporary table to store the results, but there
must be a simple way of Joining two select statements on the same table.

Can anyone help?

Adam

 

[Dale Fye]

Adam,

How about:

SELECT Agent
, SUM(iif(DateField > #1/2/2001#, 1, 0)) as Count1
, SUM(iif(DateField > #1/1/2001#, 1, 0)) as Count2
FROM yourTable
GROUP BY Agent

BTW, Date is a reserved word in Access, so I would advise
against using it as a field name. I have changed it to
DateField in my example.

HTH
Dale

 

[Gerald Stanley]

Try something along the lines of
SELECT Q1.agent, Q1.claims, Q2.claims
FROM
(SELECT agent, Count(*) as Claims FROM YourTable WHERE
claimDate > #01/01/2001# GROUP BY agent) As Q1,
(SELECT agent, Count(*) as Claims FROM YourTable WHERE
claimDate > #01/02/2001# GROUP BY agent) As Q2
WHERE Q1.agent = Q2.agent

You should be aware that this will only produce a row for
agents who return claim counts in both queries.

Hope This Helps
Gerald Stanley MCSD

 

[Adam Pedder]

Thanks a lot, I needed that kick in the right direction.

I was actually using an ADP (forgot to mention that), but I converted the
iifs to CASEs and away I went.

Thanks again.

Adam

 

[Guest]

Glad I could help.

-----Original Message-----
Thanks a lot, I needed that kick in the right direction.

I was actually using an ADP (forgot to mention that), but I converted the
iifs to CASEs and away I went.

Thanks again.

Adam




.