Count of records by group

Any SQL people out there.

How can I do this using SQL or preferably with an Access Query.

I have 6 records in a recordset.

Name Date Outcome
Colin 01/01/03 Call
Colin 02/02/03 Call Back
Colin 03/02/03 No thankyou
Dec 01/01/03 Call
Dec 02/03/03 No thankyou
Rachel 01/03/03 Call


What I want to do is add this COUNT field to it.
Name Date Outcome Count
Colin 01/01/03 Call 1
Colin 02/02/03 Call Back 2
Colin 03/02/0 No thankyou 3
Dec 01/01/03 Call 1
Dec 02/03/03 No thankyou 2
Rachel 01/03/03 Call 1

Do you know of a way of doing this?

re: Count of records by group

"Cargeoff" <colin.carmichael@resultseurope.com> wrote in message
news:47280b27.0312040129.68a82c9c@posting.google.c om...[color=blue]
> Any SQL people out there.
>
> How can I do this using SQL or preferably with an Access Query.
>
> I have 6 records in a recordset.
>
> Name Date Outcome
> Colin 01/01/03 Call
> Colin 02/02/03 Call Back
> Colin 03/02/03 No thankyou
> Dec 01/01/03 Call
> Dec 02/03/03 No thankyou
> Rachel 01/03/03 Call
>
>
> What I want to do is add this COUNT field to it.
> Name Date Outcome Count
> Colin 01/01/03 Call 1
> Colin 02/02/03 Call Back 2
> Colin 03/02/0 No thankyou 3
> Dec 01/01/03 Call 1
> Dec 02/03/03 No thankyou 2
> Rachel 01/03/03 Call 1
>
> Do you know of a way of doing this?[/color]


You don't show any primary key - so what happens when 2 records have the
same date? Which gets which row number - or is this impossible?
Anyway, if your table were called tblEvents with fields EvtID, EvtName,
EvtDate and EvtText - I might write a query like this:

SELECT EvtName, EvtDate, EvtText,
RowNumber([EvtName]) AS EvtCount
FROM tblEvents
ORDER BY EvtName, EvtDate, EvtID

You will just need to paste the following into a new code module:

Option Compare Database
Option Explicit

Public g_strName As String
Public g_lngCount As Long

Public Function RowNumber(strName) As Long

If strName = g_strName Then
g_lngCount = g_lngCount + 1
Else
g_strName = strName
g_lngCount = 1
End If

RowNumber = g_lngCount

End Function


HTH

Fletcher

re: Count of records by group

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


This should do the trick:

SELECT L.Caller, L.CallDate, L.Outcome,
(SELECT Count(*) FROM CallLog WHERE Caller = L.Caller AND CallDate <=
L.CallDate) As CallSequence
FROM CallLog AS L
ORDER BY 1, 2

I've changed the names of the columns 'cuz your column names were SQL
reserved words.

I've noticed that if queries, like the above, are used as
RecordSources in reports an error occurs, but the query runs fine as a
query! To avoid this report error use Domain Aggregate functions:

DCount("*", "CallLog", "Caller = '" & L.Caller & "'
AND CallDate <= #" & L.CallDate & "#")

in place of the subquery.

HTH,

MGFoster:::mgf
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBP9DVn4echKqOuFEgEQLjpgCgtZqsOGqd2mPb/jrPoVQo/QcRGbYAnRc3
TXkFfv3ZykuXl279dDnNbBeL
=k8T1
-----END PGP SIGNATURE-----

Cargeoff wrote:[color=blue]
> Any SQL people out there.
>
> How can I do this using SQL or preferably with an Access Query.
>
> I have 6 records in a recordset.
>
> Name Date Outcome
> Colin 01/01/03 Call
> Colin 02/02/03 Call Back
> Colin 03/02/03 No thankyou
> Dec 01/01/03 Call
> Dec 02/03/03 No thankyou
> Rachel 01/03/03 Call
>
>
> What I want to do is add this COUNT field to it.
> Name Date Outcome Count
> Colin 01/01/03 Call 1
> Colin 02/02/03 Call Back 2
> Colin 03/02/0 No thankyou 3
> Dec 01/01/03 Call 1
> Dec 02/03/03 No thankyou 2
> Rachel 01/03/03 Call 1
>
> Do you know of a way of doing this?[/color]