Count field

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 wrote:
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

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.Field2YourTable.Field2) as Field3
from YourTable
order by Field1, Field2;

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Lars Brownies" wrote in message
...
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

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

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Graham Mandeno" wrote in message
...
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.Field2YourTable.Field2) as Field3
from YourTable
order by Field1, Field2;

--
Good Luck :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Lars Brownies" wrote in message
...
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

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

Lars

"John Mishefske" schreef in bericht
...
Lars Brownies wrote:
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