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]

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).

First query pulls the data with TimeDay, stores in a table. Second query is performed on the the table which the data was stored:

        'INSERT DAILY READINGS (WITH TIMEDAY):
         sSQL = "INSERT INTO tblTempGroupNorm ( Username, Tab_Name, Click_Date, Criteria1, Criteria2, PropID, ReadingDate, Value, TimeDay) " _
            & "SELECT " & Chr(34) & strname & Chr(34) & " AS Username," _
            & Chr(34) & tabName & Chr(34) & " AS Tab_Name," _
            & strDate & " AS Click_Date," _
            & Chr(34) & Group & Chr(34) & " AS Criteria1, " _
            & "'" & subGroup & "' AS Criteria2, " _
            & "[" & tableName & "].PropID, [" & tableName & "].ReadingDate, [" & tableName & "].Value, 
            & "(SELECT COUNT(Table1A.ReadingDate) " _
            & "FROM [" & tableName & "] AS Table1A " _
            & "WHERE [Table1A].[ReadingDate]<=[" & tableName & "].[ReadingDate] " _
            & "AND [Table1A].[PropID]=[" & tableName & "].[PropID]) AS TimeDay " _
            & "FROM tblProperties INNER JOIN [" & tableName & "] ON tblProperties.PropID = [" & tableName & "].PropID "

'NORM QUERY WITH TIMEDAY IN TEMP TABLE:

    SelectedGroupSQL = "SELECT tblTempGroupNorm.TimeDay, " _
    & SumOrAvg & "(tblTempGroupNorm.Value) AS " & SumOrAvg & "OfValue, " _
    & "Count(tblTempGroupNorm.PropID) AS CountOfPropID " _
    & "FROM tblTempGroupNorm " _
            & "WHERE ((([Criteria1])='" & Group & "') AND (([Criteria2])=" & varSplit & cboBoxSelection & varSplit & ") AND " _
            & "(([Username])= '" & strname & "') AND (([Click_Date])=" & strDate & ") and (([Tab_Name])='GROUP')) " _
            & "GROUP BY tblTempGroupNorm.TimeDay;"

 

[xenou]

Okay. I'll have a play with this. Now I'm slightly interested at last

 

[xenou]

Looking at your last post there's new fields in there, variables I don't know the values of, and form data as well, so I'm ignoring it right now!

As far as the stuff before that is concerned, I'm not finding the problems you describe. I can create the TimeOfDay field with 80,000 records in about 6-7 seconds (second picture in post #13). Once that is inserted into a table, a crosstab query gets the next result in less than one second.

Here's the query I used based on the data in the first picture in post #13 to get the result in the second picture in post #13. I changed "Value" to "FValue" because value is a VBA keyword. Also, assume you have a table called XTMP001 which is a temp table to store the results (or if you want, you can run the query without the insert):

			DELETE XTMP001.* FROM XTMP001
			
			INSERT INTO XTMP001 (PropID, ReadingDate, FValue, TimeDay)
			SELECT 
				t1.PropID, 
				t1.ReadingDate, 
				t1.FValue, 
				(SELECT Count(ReadingDate) 
					FROM tblDaily t2 
					WHERE t2.ReadingDate <= t1.ReadingDate 
					AND t1.PropID = t2.PropID
				) AS TimeDay
			FROM 
				tblDaily t1

I can't post my test data right now but I can post a link tonight if someone wants it (i.e, my 80000 rows of data). Basically I just generated it with random data, using about 1000 propIDs.

Not sure but you might want to make sure your DB is in good shape - run compact and repair regularly - just to exclude that variable.

 

[bs0d]

Looking at your last post there's new fields in there, variables I don't know the values of, and form data as well, so I'm ignoring it right now!

I did rename some of the tables to be more simple and less detailed as I figured it would only add more confusion. Maybe that backfired. Also on my last post, I just copied the actual query code from VBA. I'm using ADO to pull the data from Access, and not working in Access directly.

As far as the stuff before that is concerned, I'm not finding the problems you describe. I can create the TimeOfDay field with 80,000 records in about 6-7 seconds (second picture in post #13). Once that is inserted into a table, a crosstab query gets the next result in less than one second.

Maybe there's something to the crosstab query. Can you post that one as well?

I can't post my test data right now but I can post a link tonight if someone wants it (i.e, my 80000 rows of data). Basically I just generated it with random data, using about 1000 propIDs.

Sure, I'd like to see it.

Not sure but you might want to make sure your DB is in good shape - run compact and repair regularly - just to exclude that variable.

I have a scheduled task to compact & repair each morning at 5 AM.

 

[xenou]

Okay, my test data set:
TestData

My crosstab query based on the results of running my query posted in my last post (where I created temp table XTMP001 which had PropID, ReadingDate, FValue, and the "normalized" data field called TimeDay:

TRANSFORM Sum(XTMP001.FValue) AS SumOfFValue
SELECT XTMP001.TimeDay
FROM XTMP001
GROUP BY XTMP001.TimeDay
PIVOT XTMP001.PropID;

The crosstab can only have so many fields across the top so although my test data has 996 propID's I only ran this query for 250 PropID (add a where clause such as "where propID < "A250" or otherwise just delete some data with a query such as "delete XTMP001.* where PropID > "A250"). My PropID's are labeled A000 through A996 in the original test dataset.

 

[bs0d]

Okay, my test data set:
TestData

The crosstab can only have so many fields across the top so although my test data has 996 propID's I only ran this query for 250 PropID (add a where clause such as "where propID < "A250" or otherwise just delete some data with a query such as "delete XTMP001.* where PropID > "A250"). My PropID's are labeled A000 through A996 in the original test dataset.

Hmm, so what about just getting the total? I think I only need to crosstab / pivot if Access is too slow with TimeDay.

I guess i'm not quite sure I understand why I can run a query to insert with TimeDay calc into a table, then run a sum query by TimeDay on the host table and it takes 40+ seconds for 8000 rows. But if I just insert the data without TimeDay into a container table, and run a sum query with TimeDay for the same 8000 rows of data, it takes 6 seconds, and that should even be faster, as it's the same speed as you process 10x the records.

 

[bs0d]

I noticed in my data, some field values have very long decimal places (13). Would this slow things down? I don't want to introduce a new variable here, but I'm pulling the daily data down from another source, perhaps I can store the data with fewer decimals?

 

[xenou]

I would assume double data types have no measurable effect - not until we get to millions of records.

I guess i'm not quite sure I understand why I can run a query to insert with TimeDay calc into a table, then run a sum query by TimeDay on the host table and it takes 40+ seconds for 8000 rows. But if I just insert the data without TimeDay into a container table, and run a sum query with TimeDay for the same 8000 rows of data, it takes 6 seconds, and that should even be faster, as it's the same speed as you process 10x the records.

I don't understand this either but I may not know what you mean. When you say run a sum of TimeDay on the host table, what's the host table? Which table has more records? Are the two queries with different speeds *exactly* the same (just one with a temp table and the other with a nested subquery)?

 

[bs0d]

I don't understand this either but I may not know what you mean. When you say run a sum of TimeDay on the host table, what's the host table? Which table has more records? Are the two queries with different speeds *exactly* the same (just one with a temp table and the other with a nested subquery)?

Two approaches. Each utilize 2 queries to get the normalized total for the group of properties:
Approach A (SLOW):
• Query 1 - Pull data from tblDaily, store in tblTempGroupNorm with TimeDay calc

       sSQL = "INSERT INTO tblTempGroupNorm ( Username, Tab_Name, Click_Date, Criteria1, Criteria2, PropID, ReadingDate, FValue, TimeDay) " _
       & "SELECT " & Chr(34) & strname & Chr(34) & " AS Username," _
       & Chr(34) & tabName & Chr(34) & " AS Tab_Name," _
       & strDate & " AS Click_Date," _
       & Chr(34) & Group & Chr(34) & " AS Criteria1, " _
       & "'" & subGroup & "' AS Criteria2, " _
       & "[tblDaily].PropID, [tblDaily].ReadingDate, [tblDaily].FValue, " _
       & "(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." & dbField & ") = '" & Group & "') And ((tblProperties." & dbField2 & ") = '" & subGroup & "'));"

• Query 2 - Query SUM of FValue, group by TimeDay:

   SelectedGroupSQL = "SELECT tblTempGroupNorm.TimeDay, " _
    & "Sum(tblTempGroupNorm.FValue) AS SumOfFValue, " _
    & "Count(tblTempGroupNorm.PropID) AS CountOfPropID " _
    & "FROM tblTempGroupNorm "
    & "WHERE ((([Criteria1])='" & Group & "') AND (([Criteria2])=" & varSplit & cboBoxSelection & varSplit & ") AND " _
    & "(([Username])= '" & strname & "') AND (([Click_Date])=" & strDate & ") and (([Tab_Name])='GROUP')) " _
    & "GROUP BY tblTempGroupNorm.TimeDay;"

Approach B (Faster than 'A', but still slow):
• Query 1 - Pull data from tblDaily, store in tblTempGroupNorm (No TimeDay calc)

   sSQL="INSERT INTO tblTempGroupNorm ( Username, Tab_Name, Click_Date, Criteria1, Criteria2, PropID, ReadingDate, FValue) " _
       & "SELECT " & Chr(34) & strname & Chr(34) & " AS Username," _
       & Chr(34) & tabName & Chr(34) & " AS Tab_Name," _
       & strDate & " AS Click_Date," _
       & Chr(34) & Group & Chr(34) & " AS Criteria1, " _
       & "'" & subGroup & "' AS Criteria2, " _
       & "[tblDaily].PropID, [tblDaily].ReadingDate, [tblDaily].FValue " _
       & "FROM tblProperties INNER JOIN [tblDaily] ON tblProperties.PropID = [tblDaily].PropID "
       & "WHERE (((tblProperties." & dbField & ") = '" & Group & "') And ((tblProperties." & dbField2 & ") = '" & subGroup & "'));"

• Query 2 - Query SUM of FValue, with calculated TimeDay:

    SelectedGroupSQL ="SELECT [_Group_Norm_Query].TimeDay, " _
    & "Sum([_Group_Norm_Query].FValue) AS SumOfFValue, " _
    & "Count([_Group_Norm_Query].PropID) AS CountOfPropID " _
    & "FROM (SELECT " _
    & "tblTempGroupNorm.PropID, tblTempGroupNorm.ReadingDate, tblTempGroupNorm.FValue, " _
    & "(SELECT COUNT(Table1A.ReadingDate) FROM [tblTempGroupNorm] AS Table1A " _
    & "WHERE [Table1A].[ReadingDate]<=[tblTempGroupNorm].[ReadingDate] AND [Table1A].[PropID]=[tblTempGroupNorm].[PropID]) AS TimeDay " _
    & "FROM tblTempGroupNorm "
    & "WHERE ((([Criteria1])='" & Group & "') AND (([Criteria2])=" & varSplit & cboBoxSelection & varSplit & ") AND " _
    & "(([Username])= '" & strname & "') AND (([Click_Date])=" & strDate & ") and (([Tab_Name])='GROUP')))" _
    & " AS [_Group_Norm_Query] GROUP BY [_Group_Norm_Query].TimeDay;"

 

[xenou]

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?)