Calculated Table Field

[bs0d]

Is there a way to create a calculated field which auto increments by unique ID's in another field? For example, see CalcTime in the data set below. Starting with 1, it numbers each row until the PROP changes and starts over again.

ID---- PROP---date-----CalcTime
1----- abc---1-Jan----- 1
2----- abc---2-Jan----- 2
3----- abc---3-Jan----- 3
4----- def--- 7-May----- 1
5----- def--- 8-May----- 2
6----- def--- 9-May----- 3
7----- ghi--- 25-Mar----- 1
8----- ghi--- 26-Mar----- 2
9----- ghi--- 27-Mar----- 3

 

[Joe4]

Access really doesn't lend itself to doing tasks like this easily. In relational database, the order of records as compared to other records really has no meaning (someone one said to think of a table as a bag of marbles, where everything is jumbled up and order has no meaning).

So, you have to get a little creative to accomplish tasks like this.
This thread shows one way without VBA: https://bytes.com/topic/access/answers/555526-adding-counter-id-set-data-within-group
Another way would be to use RecordSets in VBA and loop through all the data and populate a table field value (which then is not a calculated field, but a hard-coded value).

 

[bs0d]

Access really doesn't lend itself to doing tasks like this easily. In relational database, the order of records as compared to other records really has no meaning (someone one said to think of a table as a bag of marbles, where everything is jumbled up and order has no meaning).

So, you have to get a little creative to accomplish tasks like this.
This thread shows one way without VBA: https://bytes.com/topic/access/answers/555526-adding-counter-id-set-data-within-group
Another way would be to use RecordSets in VBA and loop through all the data and populate a table field value (which then is not a calculated field, but a hard-coded value).

That's the way I handle it now, with a sub query. Because what I'm doing next, is summing the values for another column grouped by the CalcTime field.

But for larger data sets, it can be slow. So I was thinking if there was a way to have Access calculate this at the table level, it might be faster.

Here is another idea I had: Query the properties, and push the data to a spreadsheet, one property per column. Then sum the values in the sheet by row, where the first row = CalcTime 1, second row = CalcTime 2, and so on.

Would I need to code a pivotTable to be applied to the query results to orient the data by column for each property? Or is there a way to write to the next column in a loop when each new property is detected? Most of of the time, I just do a .copyFromRecordset to put the data to a sheet. Thinking out loud, I wonder if looping the results would just come full circle in terms of speed....

 

[Joe4]

So I was thinking if there was a way to have Access calculate this at the table level, it might be faster.

Most of us are not big fans of the new feature that allows calculated fields directly in the table. It is not compatible with any other relational database program (so if you were to ever upgrade to SQL, it would not work). Anyway, I believe those table calculations are very limited, and you wouldn't be able to do this kind of calculation at the table level.

What is the end goal here? Are you returning data to a Form or Report? If so, you may be able to use Grouping and to do these calculations in Controls right on the Form/Report.

 

[bs0d]

Most of us are not big fans of the new feature that allows calculated fields directly in the table. It is not compatible with any other relational database program (so if you were to ever upgrade to SQL, it would not work). Anyway, I believe those table calculations are very limited, and you wouldn't be able to do this kind of calculation at the table level.

What is the end goal here? Are you returning data to a Form or Report? If so, you may be able to use Grouping and to do these calculations in Controls right on the Form/Report.

The end goal is to have the data in Excel and to display it in a chart. That's why I was kicking around the idea to pull the data and have Excel orient the property data in the fashion I need it (without an iterative calculation perhaps), then do a simple sum or average in a column.

 

[Joe4]

If you are already involving Excel in the process, why not export the data to Excel, and then do that numbering in Excel (it is much easier to do in Excel)?

 

[xenou]

If the goal is to sum the numbers you are created you can do that with a formula, without actually creating the numbers. For instance, if the count of the group is 4 then the sum is 4(5)/2 = 10. The general formula is n(n+1)/2 where n is the count. So a nested query would do the trick (inner query to group and count, and outer query to do the math).

 

[bs0d]

If the goal is to sum the numbers you are created you can do that with a formula, without actually creating the numbers. For instance, if the count of the group is 4 then the sum is 4(5)/2 = 10. The general formula is n(n+1)/2 where n is the count. So a nested query would do the trick (inner query to group and count, and outer query to do the math).

I have a query now that pulls the data, numbers it, and sum. The issue is, the query gets slower with more and more data. I need it to compute and have the answer within a second or two.

If you are already involving Excel in the process, why not export the data to Excel, and then do that numbering in Excel (it is much easier to do in Excel)?

That's what I mentioned before. I think this may be the fastest way. Any suggestions on specifically handling the data? Is there an efficient way to take data that's vertical, and transpose it to columns at each change in property? Pivot table? Loop? I may be able to drop the subquery for TimeDay and speed things up even more.

 

[Joe4]

That's what I mentioned before. I think this may be the fastest way. Any suggestions on specifically handling the data? Is there an efficient way to take data that's vertical, and transpose it to columns at each change in property? Pivot table? Loop? I may be able to drop the subquery for TimeDay and speed things up even more.

I would probably use VBA to do any data transposing that you need to do. The Macro Recorder may help you get a lot of the base code you need.
As for the calculated column, you can do that with a simple formula, once your data is structured and sorted properly.
Let's say that we have the data you posted in your original post above in columns A - C, starting on row 2 (row 1 is your header), and you want to add that calculation in column D. Then enter this formula in cell D2 and copy down for all rows:

=IF(B2=B1,D1+1,1)

 

[bs0d]

I would probably use VBA to do any data transposing that you need to do. The Macro Recorder may help you get a lot of the base code you need.
As for the calculated column, you can do that with a simple formula, once your data is structured and sorted properly.
Let's say that we have the data you posted in your original post above in columns A - C, starting on row 2 (row 1 is your header), and you want to add that calculation in column D. Then enter this formula in cell D2 and copy down for all rows:

=IF(B2=B1,D1+1,1)

I doubt I need the macro recorder for the VBA. I guess my question was, with both of your extensive experience in VBA and Excel, if either of you were aware of what might be the most efficient (fastest) method. I'm after speed here. I already have something that works, I just need to make it faster. Using a VBA loop that touches each single record doesn't sound that efficient. Especially when my data set can easily be 175 x 1100 (close to 200,000 records), and will continue to grow through time. I know when you create a PIVOT Table, you can push vertical data to columns with one click. I guess to think of it another way, if you needed to achieve the same results, what approach would you follow?

Thanks again