Count field

[Lars Brownies]

I have a table like this
Field1 Field2
1 A
1 B
1 K
2 X
3 Y
3 Z

I'd like to add a field so that the table looks like this
Field1 Field2 Field3
1 A 1
1 B 2
1 K 3
2 X 1
3 Y 1
3 Z 2

So I need to add a count field for every field1 value.

Can this be done in a query or do need VBA?

Thanks, Lars

 

[John Mishefske]

I have a table like this
Field1 Field2
1 A
1 B
1 K
2 X
3 Y
3 Z

I'd like to add a field so that the table looks like this
Field1 Field2 Field3
1 A 1
1 B 2
1 K 3
2 X 1
3 Y 1
3 Z 2

So I need to add a count field for every field1 value.

Can this be done in a query or do need VBA?

A query can do this:

SELECT t.Field1, t.Field2, (SELECT Count(*) FROM tblYourTableNameHere As
S WHERE S.Field1 = t.Field1 AND S.Field2 <= t.Field2) AS Field3
FROM tblYourTableNameHere AS t
ORDER BY t.Field1, t.Field2;

You don't want to store a calculated field like 'Field3'; instead you
re-calculate it at the time you need it.

I'm assuming your field names and db structure are just examples for
this post. If not then you have database schema issues that need to be
dealt with.

HTH.

--
John Mishefske, Microsoft MVP 2007 - 2009
UtterAccess Editor
Tigeronomy Software
web: http://www.tigeronomy.com
email: sales ~at~ tigeronomy.com

 

[Graham Mandeno]

Hi Lars

Something like this should work:

Select Field1, Field2,
DCount('*', 'YourTable',
'Field1=' & Field1 & " and Field2<"' & Field2 & '"') as Field3
from YourTable
order by Field1, Field2;

Instead of the DCount, it might be faster to use a subquery, but this would
probably render your query non-updatable. If that's not a problem, then try
this:

Select Field1, Field2,
(Select Count(*) from YourTable as X
where X.Field1=YourTable.Field1
and X.Field2<YourTable.Field2) as Field3
from YourTable
order by Field1, Field2;

 

[Graham Mandeno]

Sorry Lars, in both cases I omitted "+1" before "as Field3".

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Lars

Something like this should work:

Select Field1, Field2,
DCount('*', 'YourTable',
'Field1=' & Field1 & " and Field2<"' & Field2 & '"') as Field3
from YourTable
order by Field1, Field2;

Instead of the DCount, it might be faster to use a subquery, but this
would probably render your query non-updatable. If that's not a problem,
then try this:

Select Field1, Field2,
(Select Count(*) from YourTable as X
where X.Field1=YourTable.Field1
and X.Field2<YourTable.Field2) as Field3
from YourTable
order by Field1, Field2;

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have a table like this
Field1 Field2
1 A
1 B
1 K
2 X
3 Y
3 Z

I'd like to add a field so that the table looks like this
Field1 Field2 Field3
1 A 1
1 B 2
1 K 3
2 X 1
3 Y 1
3 Z 2

So I need to add a count field for every field1 value.

Can this be done in a query or do need VBA?

Thanks, Lars

 

[Lars Brownies]

Thanks John, Graham,
The solution works like a charm.

Lars