counting number question

[angelinarose via AccessMonster.com]

Hi,

I have a table that has an IDfield(autonumber), Datefield, Namefield.

data sort of looks like this....
IDfield(autonumber), Datefield, Namefield
1 1/1/2009 Smith
2 1/1/2009 Braxton
3 1/2/2009 James
4 1/3/2009 James

I need a query that will add a field and updated it with a count like this
IDfield(autonumber), Datefield, Namefield, newfield
1 1/1/2009 Smith 1
2 1/1/2009 Braxton 2
3 1/2/2009 James 1
4 1/3/2009 James 1

it will count the number times a date is entered into the table

anyone that could help would be greatly appreciated

thanks!

 

[Dale Fye]

Angelina,

In order to do this, you will need a subquery of some sort. The example
below should do what you want. What the subquery is doing is counting the
number of records whose dateField match the dateField in the current record,
and whose ID value is less than or equal to the current record.

Select IDField, DateField, NameField,
(SELECT Count(*) FROM yourTable
WHERE [DateField] = T.[DateField]
AND [IDField] <= T.[IDField]) as DateCount
FROM yourTable as T
ORDER BY IDField

 

[angelinarose via AccessMonster.com]

I'll try it and let you know,
thanks
Angelina,

Angelina,

In order to do this, you will need a subquery of some sort. The example
below should do what you want. What the subquery is doing is counting the
number of records whose dateField match the dateField in the current record,
and whose ID value is less than or equal to the current record.

Select IDField, DateField, NameField,
(SELECT Count(*) FROM yourTable
WHERE [DateField] = T.[DateField]
AND [IDField] <= T.[IDField]) as DateCount
FROM yourTable as T
ORDER BY IDField

----
HTH
Dale

Hi,

[quoted text clipped - 19 lines]

thanks!