The proposed solution demonstrates the concept of generating a large data series by using structured query language (SQL) and relatively small data tables, thus dramatically reducing the overall 'digital footprint' (rather important design consideration for any mobile/compact/embedded solutions).
Sample coding technique to demonstrate the concept utilizes a small table (Table1) with just 10 records, containing integer numbers: 0,1,2,3,...9, and a single SQL query to generate a virtual time line, which spans for 10,000 days, or roughly 27 years moving backward from the current date (Access SQL, Listing 1):
SELECT Now()-CLng([Table1].[Num] & [Table1_1].[Num] & [Table1_2].[Num] & [Table1_3].[Num]) AS TimeSeries
FROM Table1, Table1 AS Table1_1, Table1 AS Table1_2, Table1 AS Table1_3
ORDER BY Now()-CLng([Table1].[Num] & [Table1_1].[Num] & [Table1_2].[Num] & [Table1_3].[Num]) DESC;
The logic behind the solution is rather straightforward. The query creates 2 virtual tables (Table_1, Table_2 and Table_3) in addition to the 'concrete' permanent Table1, and then applies the so-called 'Cartesian product' technique, concatenating every record from all 4 tables (permanent and virtual as well). Then it applies the data type conversion to long integer, which represents the date, and finally, sorts the date series of 10,000 records, going from current date backward in descending order.
