If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Count field
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 |
#3
|
|||
|
|||
Count field
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 |
#4
|
|||
|
|||
Count field
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 |
#5
|
|||
|
|||
Count field
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 |
Thread Tools | |
Display Modes | |
|
|