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

 

[xenou]

For the Excel vba, there is no need for a loop. Use a formula that is pasted all at once and then calculated. It would be as fast as Excel is fast (but I'm not able to say whether that is faster than the current process).

 

[Joe4]

Using a <acronym title="visual basic for applications" style="border-width: 0px 0px 1px; border-bottom-style: dotted; border-bottom-color: rgb(0, 0, 0); cursor: help; color: rgb(51, 51, 51); background-color: rgb(250, 250, 250);">VBA</acronym> loop that touches each single record doesn't sound that efficient.

The "loop" suggestion I made originally was for an Access VBA solution, not an Excel one. I don't think you should have to need to use loops in the Excel VBA solution. I gave you the formula you need, and you just copy it down.

Quite honestly, in my line of work, I seldom used Pivot Tables. If I ever need that kind of summary, I usually end up using Access instead. So I cannot really speak to using Pivot Tables in Excel to do what you want.

Without knowing the exact structure of the data you are working with, I really cannot say what methodology I would use (you mentioned transposing the data, so I take it that the original data is not structured like what you have shown in your first post).

 

[bs0d]

The "loop" suggestion I made originally was for an Access VBA solution, not an Excel one. I don't think you should have to need to use loops in the Excel VBA solution. I gave you the formula you need, and you just copy it down.

Quite honestly, in my line of work, I seldom used Pivot Tables. If I ever need that kind of summary, I usually end up using Access instead. So I cannot really speak to using Pivot Tables in Excel to do what you want.

Without knowing the exact structure of the data you are working with, I really cannot say what methodology I would use (you mentioned transposing the data, so I take it that the original data is not structured like what you have shown in your first post).

I don't get the feeling we're following each other on the subject that well. I don't need a formula to number days by property. As you said, it's very simple. And as I've said, I have an Access query which calculates a CalcTime column sum's the data for all properties grouped by the CalcTime field. It works like a charm. It just gets too slow with a large data set. Slow = ~60 seconds to execute.

To sort of start fresh and make clear what I'm after, here are some pictures:

1. Assume the following table of data:

2. TimeDay is needed to normalize the data (time zero / first date is the same, regardless of actual date): This is currently processed via sub query in Access, which has been found to be too slow for larger data sets.

3. This is how I need the data organized, properties by column, and sorted by TimeDay:

4. This allows me to calculate the data I need, the SUM by TimeDay:

Is there a way to query the data in a manner that would output the data in the format of #3 above without calculating TimeDay? The query would just force the date as the first row, 2nd date on 2nd row and so on?

If not, it seems Excel maybe the fastest way to calculate what I need (#4).
I can easily put #1 to a sheet in Excel, and plugin a formula for TimeDay (#2). The trick becomes reorganizing the data to #3, to calculate #4.

 

[xenou]

Can you post your queries that you are using? Looks like there should only be two (or three?)

 

[bs0d]

Can you post your queries that you are using? Looks like there should only be two (or three?)

Here you go:

SELECT 
Group_Norm_Query.TimeDay, 
Sum(Group_Norm_Query.Value) AS SumOfValue, 
Count(Group_Norm_Query.PropID) AS CountOfPropID

FROM (SELECT tblDaily.PropID, 
tblDaily.ReadingDate, 
tblDaily.Value, 
(SELECT COUNT(Table1A.ReadingDate) FROM [tblDaily] AS Table1A  
 WHERE [Table1A].[ReadingDate]<=[tblDaily].[ReadingDate] AND [Table1A].[PropID]=[tblDaily].[PropID]) AS TimeDay 

FROM tblProperties INNER JOIN tblDaily ON tblProperties.PropID = tblDaily.PropID
WHERE (((tblProperties.Field1)="condition 1") AND ((tblProperties.Field2)="condition 2")) 
ORDER BY tblDaily.ReadingDate)  AS Group_Norm_Query
GROUP BY Group_Norm_Query.TimeDay;

 

[xenou]

Just throwing this out there but have you indexed the fields in the join conditions and where clauses?

Also, as a general rule with MSAcess (not SQLServer), I find it can perform better if you put the output of a nested query into an actual table then query that result (so making one query into two - the subquery is first used with INSERT INTO to populate the "inner result", then the outer query queries that result table). Again, we can *sometime* improve performance with indexing.

 

[bs0d]

Just throwing this out there but have you indexed the fields in the join conditions and where clauses?

Also, as a general rule with MSAcess (not SQLServer), I find it can perform better if you put the output of a nested query into an actual table then query that result (so making one query into two - the subquery is first used with INSERT INTO to populate the "inner result", then the outer query queries that result table). Again, we can *sometime* improve performance with indexing.

I actually have 2 queries, but not broken up as you suggested. The first one pulls the data you see in Table #1 and stores it in another "temporary" table. Then, I run the query I posed above to the temp table.

I checked the temp table, and I had not set indexing on the fields in the WHERE clause. After setting them, I re-tested and there was no change in the query speed. I've heard indexing can also work against you on INSERT queries, but again - no real change.

If I alter the initial query to do the TimeDay calc, and store in the temporary table, followed up with a sum query to the temp table, this is significantly slower than the current method. Not sure why, but the difference is remarkable.

A query with < 10 properties and a few thousand rows, takes about 2 seconds to run.
A query with ~50 properties and ~40,000 rows, takes just under 1 minute.

 

[xenou]

I missed this little jewel in the middle of your query:

	(
		SELECT COUNT(Table1A.ReadingDate) 
		FROM [tblDaily] AS Table1A  
		WHERE [Table1A].[ReadingDate]<=[tblDaily].[ReadingDate] AND [Table1A].[PropID]=[tblDaily].[PropID]
	) AS TimeDay

That's probably correlated and so yes, running time should grow significantly as number of rows increases.

You could try to decorrelate by creating that result set as an actual table ...

I'll need to look closer though.

Also btw shouldn't have order by in nested queries.


-- Note that 1 minute isn't terribly bad for the query you have posted In MSAccess defense!

 

[bs0d]

I missed this little jewel in the middle of your query:

	(
		SELECT COUNT(Table1A.ReadingDate) 
		FROM [tblDaily] AS Table1A  
		WHERE [Table1A].[ReadingDate]<=[tblDaily].[ReadingDate] AND [Table1A].[PropID]=[tblDaily].[PropID]
	) AS TimeDay

That's probably correlated and so yes, running time should grow significantly as number of rows increases.

You could try to decorrelate by creating that result set as an actual table ...

I'll need to look closer though.

Also btw shouldn't have order by in nested queries.


-- Note that 1 minute isn't terribly bad for the query you have posted In MSAccess defense!

I'll be curious if you think of anything. I think I need ORDER BY since the order is important. Although it would be quite rare for the dates to be out of order in the source table, I couldn't rule it out. Otherwise, the TimeDay would no longer be true, if that makes sense.

I tried a query where TimeDay is calculated up front and stored in a table, the querying that table and it was significantly slower.

 

[xenou]

TimeOfDay is based on this logic:

SELECT COUNT(Table1A.ReadingDate) FROM [tblDaily] AS Table1A  
 WHERE [Table1A].[ReadingDate]<=[tblDaily].[ReadingDate] AND [Table1A].[PropID]=[tblDaily].[PropID]

so actually order by does nothing (it comes well after TimeOfDay is determined).

I tried a query where TimeDay is calculated up front and stored in a table, the querying that table and it was significantly slower.

Post that query if you can - it doesn't really make sense that it would be slower (unless you mean creating that table is just as slow, not querying - although still don't see why it would be slower rather than just as slow).