Select 1
Union All Select 2
Union All Select 3
Once we have some minimal sequence in a table, we can use table joining 'magic' (matrix product growth) in a query to produce a much larger sequence of numbers. I will show you a trick to create a query-only source of numbers, but I do a little cheating with my From clause.
Public Sub MakeTallyTable(Optional parmLimit As Long = 1000, Optional parmPopulate As Boolean = False)
Dim lngLoop As Long
Dim td As TableDef
Dim fd As Field
Dim vID As Variant
Const cCommitInterval As Long = 500
'Create the Tally Table if it doesn't already exist.
On Error Resume Next
vID = DBEngine(0)(0).TableDefs("TallyTable").Name
If vID = "TallyTable" Then
Else
Set td = DBEngine(0)(0).CreateTableDef("TallyTable")
Set fd = New Field
fd.Name = "ID"
fd.Type = dbLong
td.Fields.Append fd
DBEngine(0)(0).TableDefs.Append td
End If
'************************
'Optional population of the table
If parmPopulate Then
vID = DMax("ID", "TallyTable")
If IsNull(vID) Then
vID = 0
End If
If vID < parmLimit Then
DBEngine(0).BeginTrans
For lngLoop = vID + 1 To parmLimit
DBEngine(0)(0).Execute "Insert Into TallyTable (ID) Values (" & lngLoop & ")"
If (lngLoop Mod cCommitInterval) = 0 Then
DBEngine(0).CommitTrans
DBEngine(0).BeginTrans
End If
Next
DBEngine(0).CommitTrans
End If
End If
'************************
End Sub
Create Table TallyTable2
(ID Long)
Note: You can also rewrite the first 'populating query' below, converting them into a Make Table query.
Public Sub PopulateTallyTable(Optional parmLimit As Long = 1000)
Dim lngLoop As Long
Const cCommitInterval As Long = 500
vID = DMax("ID", "TallyTable")
If IsNull(vID) Then
vID = 0
End If
If vID < parmLimit Then
DBEngine(0).BeginTrans
For lngLoop = vID + 1 To parmLimit
DBEngine(0)(0).Execute "Insert Into TallyTable (ID) Values (" & lngLoop & ")"
If (lngLoop Mod cCommitInterval) = 0 Then
DBEngine(0).CommitTrans
DBEngine(0).BeginTrans
End If
Next
DBEngine(0).CommitTrans
End If
End Sub
SELECT DISTINCT ID
FROM (SELECT Top 1 1 As ID FROM MSysObjects
UNION ALL
SELECT Top 1 2 FROM MSysObjects
UNION ALL
SELECT Top 1 3 FROM MSysObjects
UNION ALL
SELECT Top 1 4 FROM MSysObjects
UNION ALL
SELECT Top 1 5 FROM MSysObjects
UNION ALL
SELECT Top 1 6 FROM MSysObjects
UNION ALL
SELECT Top 1 7 FROM MSysObjects
UNION ALL
SELECT Top 1 8 FROM MSysObjects
UNION ALL
SELECT Top 1 9 FROM MSysObjects
UNION ALL
SELECT Top 1 10 FROM MSysObjects);
Insert Into TallyTable2 (ID)
Select ID From QBTallyTableBase10
SELECT DISTINCT ID INTO TallyTable2
FROM (SELECT Top 1 1 As ID FROM MSysObjects
UNION ALL
SELECT Top 1 2 FROM MSysObjects
UNION ALL
SELECT Top 1 3 FROM MSysObjects
UNION ALL
SELECT Top 1 4 FROM MSysObjects
UNION ALL
SELECT Top 1 5 FROM MSysObjects
UNION ALL
SELECT Top 1 6 FROM MSysObjects
UNION ALL
SELECT Top 1 7 FROM MSysObjects
UNION ALL
SELECT Top 1 8 FROM MSysObjects
UNION ALL
SELECT Top 1 9 FROM MSysObjects
UNION ALL
SELECT Top 1 10 FROM MSysObjects);
SELECT QBTallyTableBase10.ID INTO TallyTable2
FROM QBTallyTableBase10;
SELECT ((QBTT6.ID-1)*100000)+((QBTT5.ID-1)*10000)+((QBTT4.ID-1)*1000)+((QBTT3.ID-1)*100)+((QBTT2.ID-1)*10)+QBTT.ID AS ID
FROM TallyTable2 AS QBTT, TallyTable2 AS QBTT2, TallyTable2 AS QBTT3, TallyTable2 AS QBTT4, TallyTable2 AS QBTT5, TallyTable2 AS QBTT6;
SELECT ((QBTT3.ID-1)*100)+((QBTT2.ID-1)*10)+QBTT.ID AS ID
FROM TallyTable2 AS QBTT, TallyTable2 AS QBTT2, TallyTable2 AS QBTT3;
SELECT ((TT6.ID-1)*100000)+((TT5.ID-1)*10000)+((TT4.ID-1)*1000)+((TT3.ID-1)*100)+((TT2.ID-1)*10)+TT.ID AS ID
FROM QBTallyTableBase10 AS TT, QBTallyTableBase10 AS TT2, QBTallyTableBase10 AS TT3, QBTallyTableBase10 AS TT4, QBTallyTableBase10 AS TT5, QBTallyTableBase10 AS TT6;
SELECT DISTINCT ((TT3.ID-1)*100)+((TT2.ID-1)*10)+TT.ID AS ID
FROM QBTallyTableBase10 AS TT, QBTallyTableBase10 AS TT2, QBTallyTableBase10 AS TT3;
Recordset type Avg time min max
========================= ======== ==== ====
1K table rows (Dynaset) 22.00 16 31
1K table rows (snapshot) 24.80 15 31
1K query rows (Dynaset) 62.60 47 94
1K query rows (snapshot) 62.20 47 78
1M table rows (Dynaset) 22308.60 20686 25319
1M table rows (Snapshot) 25915.00 24570 26958
1M query rows (Dynaset) 27827.80 26411 28596
1M query rows (Snapshot) 29272.00 28392 30575
PARAMETERS [start date] DateTime, [end date] DateTime;
SELECT DISTINCT TT.ID, ([start date]+[ID]-1) AS GenDate
FROM TallyTable As TT
WHERE (TT.ID <= ([end date]-[start date]+1) );
SELECT DISTINCT T.GenDate, (Select Min(NxD.gendate) From JustDatesFromTallyTable As NxD Where (NxD.ID Between T.ID And (T.ID+7)) And (Weekday(Nxd.GenDate)=1 Or NxD.GenDate = [end date] Or NxD.GenDate = DateSerial(Year(NxD.GenDate),Month(NxD.GenDate)+1,0) )) AS EOW
FROM JustDatesFromTallyTable AS T
WHERE (((Weekday([T].[GenDate]))=2)) OR (((Day([T].[GenDate]))=1)) OR (((T.ID)=1));
PARAMETERS [start date] DateTime, [end date] DateTime;
SELECT DISTINCT TT.ID, ([start date]+[ID]-1) AS GenDate
FROM QueryBasedTallyTable AS TT
WHERE (((TT.ID)<=([end date]-[start date]+1)));
SELECT DISTINCT T.GenDate, (Select Min(NxD.gendate) From JustDatesFromQBTallyTable As NxD Where (NxD.ID Between T.ID And (T.ID+7)) And (Weekday(Nxd.GenDate)=1 Or NxD.GenDate = [end date] Or NxD.GenDate = DateSerial(Year(NxD.GenDate),Month(NxD.GenDate)+1,0) )) AS EOW
FROM JustDatesFromQBTallyTable AS T
WHERE (((Weekday(T.GenDate))=2)) Or (((Day(T.GenDate))=1)) Or (((T.ID)=1));
Recordset type Avg time min max
============== ======== ==== ====
Dynaset (table) 707.00 686 733
Snapshot (table) 738.67 718 749
Snapshot (query) 1456.00 1451 1466
Dynaset (query) 1487.33 1482 1498
*** No index on TallyTable ***
Recordset type Avg time min max
============== ======== ==== ====
Snapshot 8164.00 8065 8315
Dynaset 8398.00 7956 8705
*** Non-PK Index on TallyTable **
Recordset type Avg time min max
=============== ======== ==== ====
Dynaset 7498.33 7441 7535
Snapshot 7555.67 7519 7598
*** Primary Key index on TallyTable ***
Recordset type Avg time min max
============== ======== ==== ====
Snapshot 7695.67 7612 7816
Dynaset 7748.33 7551 8112
*** Query ***
Recordset type Avg time min max
============== ======== ==== ====
Snapshot 12972.33 12605 13728
Dynaset 13040.00 12745 13838
PARAMETERS ____ Long;
(Select Count(*) From Lot_Mast As C Where C.[lot-no] >= lot_mast.[lot-no]) AS OrdinalValue
CLng(DCount("[lot-no]","[Lot_mast]","[lot-no]>='" & [lot-no] & "'")) AS OrdinalValue
Recordset type Avg time min max
============================ ======== ==== ====
Select Tally Join (Snapshot) 156.00 156 156
Select Tally Join (Dynaset) 161.33 156 172
Select (Snapshot) 228.67 218 234
Select (Dynaset) 234.33 219 265
Dcount Tally Join (Dynaset) 894.67 874 921
Dcount Tally Join (Snapshot) 920.67 906 936
Dcount (Dynaset) 1481.67 1466 1513
Dcount (Snapshot) 1549.67 1513 1591
Dcount Tally (Snapshot) 2106.00 2090 2137
Dcount Tally (Dynaset) 2158.00 2106 2184
Select Tally (Dynaset) 5179.33 5117 5257
Select Tally (Snapshot) 5200.00 5117 5288
In case you were curious, the fastest result was with a primary key index on the TallyTable
Recordset type Avg time min max
============================ ======== ==== ====
Select Tally Join (Snapshot) 140.67 140 141
PARAMETERS Delim Text ( 255 );
SELECT DISTINCT DataToSplit.PersonID, TallyTable.ID
FROM DataToSplit, TallyTable
WHERE (((TallyTable.ID)<=255) AND ((CBool(Len(Mid([Delim] & [data] & [Delim],[id],2))=Len([Delim]) And Mid([Delim] & [data] & [Delim],[id],2)=[Delim]))=True));
PARAMETERS Delim Text ( 255 );
SELECT DISTINCT DataToSplit.PersonID, TallyTable.ID
FROM DataToSplit, TallyTable
WHERE (((TallyTable.ID)<=255) AND (Instr(ID,[Delim] & [data] & [Delim],[Delim])=[ID]));
SELECT DelimiterPositions.PersonID, Count(DelimiterPositions.ID)-1 AS WordCount
FROM DelimiterPositions
GROUP BY DelimiterPositions.PersonID;
PARAMETERS Delim Text ( 255 );
SELECT DataToSplit.PersonID, Trim(Mid([data],[DelimiterPositions_Instr].[ID],[NextDelimPosn]-[DelimiterPositions_Instr].[ID])) AS [Procedure], DelimiterPositions_Instr.ID, InStr([ID]+1,[delim] & [data] & [Delim],[Delim]) AS NextDelimPosn
FROM DataToSplit INNER JOIN DelimiterPositions_Instr ON DataToSplit.PersonID = DelimiterPositions_Instr.PersonID
WHERE (((InStr([ID]+1,[delim] & [data] & [Delim],[Delim]))<>0))
ORDER BY DataToSplit.PersonID, DelimiterPositions_Instr.ID;
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (2)
Author
Commented:I caught an error on the first of the delimiter position queries. The length parameter of the Mid() function was a literal. It should have been equal to the length of the [Delim] parameter. I also simplified the query, although it still does not perform as well as the InStr version of the query.
DelimiterPositions
Open in new window
Author
Commented:A new constraint in this question is the need to prevent duplicate sets of rows when the process was repeated. Therefore, a new table was required (HasBeenCloned) to persist the key values involved in the copying process.
http:/Q_27663167.html