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

 

[bs0d]

Your two approaches just put the "hard work" in the first query (approach 1) or in the second query (approach 2). So I don't see much of a gain one way or the other - should be a wash.

Are you saying one of these works better than the other (significantly?) (on test data with the same number of rows?)

Yeah! If I do the heavy work up front (approach 1), it takes about 40+ seconds for an 8k data set. While approach 2 takes 6 seconds for the same data.

 

[xenou]

Looking again it seems in approach #2 you have done some filtering in the first query:

WHERE (((tblProperties.dbField) = 'Group') And ((tblProperties.dbField2) = 'subGroup'))

If that reduces the number of rows then yes, it should go faster -- fewer rows to process. Although you have a similar criteria in the first approach, so who knows. No answer - not without test data that replicates the issue. Six seconds is good speed. It really depends on how Access executes the query (low level) - possibly the second approach simply works better because it does.

 

[bs0d]

I have found a solution that is faster than Access, even if there were no issues or questions about query structure, design, speed and so on.

For the data normalization, I query all of the data I need and return it to a sheet in Excel.
I apply a formula to calculate TimeDay, and fill it to the last row of data.
Next, I create a PivotTable with TimeDay by rows, and the "value" field as a data field with xlSum. I also include a data field for PropID xlCount

The rest is cosmetic, but I copy the values from the PivotTable and paste them in the chart reference fields, and delete the PivotTable. This occurs behind the scenes. Nobody but me would ever know a PivotTable is being created to transpose and calculate the data.

I can do this to all Properties (~160k rows), and it takes 2 or 3 seconds. All other scenarios are nearly instantaneous. Still limited by Access speed, but it's only pulling data, and not performing any calculations.

 

[xenou]

Okay good! For what its worth, databases have to work with networks, file systems, disk access and so on - since as a rule you are dealing with persistent data not in memory data. So it can and will normally be a critical area for performance - the slowest link in the chain. You can't always expect instant results even with best query optimization. Sometimes Access or other databases will even "look" instant but it is only because the first results are displayed quickly - and Access is still retrieving records in the background in reality!

 

[bs0d]

Okay good! For what its worth, databases have to work with networks, file systems, disk access and so on - since as a rule you are dealing with persistent data not in memory data. So it can and will normally be a critical area for performance - the slowest link in the chain. You can't always expect instant results even with best query optimization. Sometimes Access or other databases will even "look" instant but it is only because the first results are displayed quickly - and Access is still retrieving records in the background in reality!

Well thanks for sticking with me on it and helping sanity check the process and consider optimizations and alternatives. I'm still open to other ideas. It's nice to be aware of the one stop solution (query). If I were doing this by hand, I'd prefer to wait the minute for the query to execute than the extra work of query+formula+pivot. But since I'm in Excel and automating the process behind the scenes with VBA, it seems to be the most efficient.