Function to Create a Recordset of Recordsets

[Lightwave]

Early days of an idea.

I was wondering if anyone has ideas or experience of creating a function that would take a table or a query that partitions the recordset loaded from the query or table into multiple recordsets. The recordsets to be defined by a variable number of total records.

For instance imagine you are wanting to email but you are only allowed to email 10 emails in a single email. Taking the email list from one table may produce 10 recordsets while another might make 20.

I can see a number of ways I might do it but looking for the most elegant one and thought I would ask everyone first.

Thanks in advance

 

[Ranman256]

thats a query of a query.
I use this method to breakup data into the old Excel 65k limit. You can do it for 10.
The table has a field SENT (true false) all set to false at start.
Q1 to pull TOP 10 records, sent = false.
,this query is processed.
Q2 update on Q1 records, set sent = true.

now Q1 pulls the next batch.

 

[CJ_London]

I think the answer to your question is to use the TOP statement in SQL. e.g.

SELECT TOP 10 *
FROM myTable

or you can use %

SELECT TOP 10 PERCENT *
FROM myTable

 

[Lightwave]

Thanks guys yes I think that looks like a solution

 

[gemma-the-husky]

I would just use a single recordset, with a counter

this sort of pseudo-code

counter=0
while not rst.eof
    if counter = maxvalue then
         sendemail
        counter=0  'reset counter
        clear attachements
   end if

   prepare attachment
   increment counter
wend

'final tidy up
if counter>0 then
     sendemail
     clear attachments
end if

 

[LPurvis]

Hi

I'd have to say that, given the example cited, I'd agree with Dave. If you're performing an iterative action, then performing multiple fetches of the data itself seems pointless (and likely marginally wasteful).

Of course, the question subject itself: "Recordset of Recordsets" would lead to the kind of functionality that you can't readily achieve if you're using DAO and an ACE database.
However ADO and SQL can perform multiple selects upon data and return all those in a single recordset - which actually exposes subsequent recordsets.
But you've still got the requirement to query those sections, and you might well end up with the SELECT TOP 10% as already suggested.
However, of course, you need to iterate down through and not select records that were found in the previous selection (so excluding the previous X%).
SELECT TOP 10% ...
SELECT TOP 10% ... WHERE PKID Not In (SELECT TOP 10% PKID ...)
SELECT TOP 10% ... WHERE PKID Not In (SELECT TOP 20% PKID ...)
SELECT TOP 10% ... WHERE PKID Not In (SELECT TOP 30% PKID ...)

And so on. It's never pleasant that.

Cheers

 

[CJ_London]

A more efficient way is to use a left join, rather than in

SELECT TOP 10%
From myTable A LEFT JOIN (SELECT TOP 10% PK ...) B ON A.PK=B.PK
WHERE B.PK is null

 

[Galaxiom]

A more efficient way is to use a left join, rather than in

SELECT TOP 10%
From myTable A LEFT JOIN (SELECT TOP 10% PK ...) B ON A.PK=B.PK
WHERE B.PK is null

Do you have any references or test results to back this? Database engines are remarkably good at optimising queries and I wouldn't be surprised if they ran much the same plan for both versions.

 

[CJ_London]

Do you have any references or test results to back this?

it was something I was told many years ago on this or another forum when I had a similar question. I just took it as gospel.

Just had a quick look round and general consensus is that there is no real difference in execution (but it depends) and arguments for and against on readability. Having said that none of the threads used this threads requirement for comparison, but found this https://msdn.microsoft.com/en-us/library/ms189575(v=sql.105).aspx

Many Transact-SQL statements that include subqueries can be alternatively formulated as joins. Other questions can be posed only with subqueries. In Transact-SQL, there is usually no performance difference between a statement that includes a subquery and a semantically equivalent version that does not. However, in some cases where existence must be checked, a join yields better performance. Otherwise, the nested query must be processed for each result of the outer query to ensure elimination of duplicates. In such cases, a join approach would yield better results. The following is an example showing both a subquery SELECT and a join SELECT that return the same result set

my google search was based on 'sql left join v subquery'

 

[Galaxiom]

However, in some cases where existence must be checked, a join yields better performance. Otherwise, the nested query must be processed for each result of the outer query to ensure elimination of duplicates. In such cases, a join approach would yield better results.

However, EXISTS() is not equivalent to IN().

IN() can be a single subquery that yields a set of values that are fed to the main query like a Where clause.

EXISTS() is a correlated subquery and runs for every record in the main query. Hence it is not very efficient.

 

[CJ_London]

I don't read it as using the exists function - but checking for existence which to me is different - the exists function is just one way of checking for existence.

The debate is using IN v Left Join which is not used in the link, So I tried googling 'sql left join v in'

These links seem relevant, both for mySQL

https://dev.mysql.com/doc/refman/5.5/en/rewriting-subqueries.html
which merely states left join can be faster.

There is also this link
http://www.xoogu.com/2013/optimising-mysql-queries-join-vs-in-subquery/
The conclusion was 'it depends'. It depends on the number of rows in the table, the number of rows to be returned and the complexity of the subquery.

 

[Lightwave]

Thought I'd give you an update on my eventual conclusion.

I used a WEND and SELECT TOP with flags.

First some more background - at work we have a requirement to transfer large amounts of lookups from one system to another. As part of the transfer many of those lookups need to be shortened as the destination fields only accept 6 characters. Clearly the best method will be to create translation tables or converstion tables listing the old and new values and then include then join the table in an update query to update the values.

I am working on this project with others and it may be difficult for us to coordinate a link to a database that will allow everyone to join in the correct conversion tables. I was therefore thinking about nested IF queries that could be distributed to individual developers. Some may be aware but in Access there is a limit on how many nested IFs Access will accept before complaining that statements are too complicated if you go over this limit. From my testing I believe this to be 13 in MS Access 2003.

So I thought if I could generate multiple nested IF statements none larger than 13.

I set it up with a WEND loop and a select top statement with three flag fields in three recordsets. The structure of a nested IF statement is such that I I appear to need three recordsets. One that manages the loop through of the oldvalues at the start of the IF statement, one to manage the closing brackets after the string construction of old values and then a further recordset to manage the construction of the tail end of the IF statement.

I really only wanted one flag field that as per the previous discussion allows re-querying of the top 13 records. Interestingly things got a bit weird when I only used one flag field. It appears that while I was setting the recordsets at the beginning of the statement as I went through there was some kind of dynamic link between the recordsets such that as I flagged up completion through the loop with a 1 this was altering the subsequent recordsets such that my recordsets were getting out of sync. (My IF statement uses a where clause to select only those records to be updated in the front of the statement as such the front and back of the SQL statement that is pulling from two different recordsets need to match such that an if statement for an old value is matched with a where statement of the same old value). It was as if the recordsets rather than being set at the beginning of the VBA were themselves a query on the table that as it was altered by the first recordset altered the subsequent recordset. This lead to a mismatch of the IF front part to the where back part.

I also had issues with rs.recordcount which I needed in order to create the commas between the IIF statements and the ORs in the second half of my SQL. I had to use rs.MoveLast before each recordset.recordcount to ensure that recordcount was correct as omission of movelast resulted in a record count of 0 on first pass. Thus I used rs.MoveLast then rs.recordcount I then had to reset the recordsets to movefirst otherwise the recordsets loop would immediately exit.

I also used the WEND statement to loop through an arbitrary number of times. My code definitely has room for improvement - I'm sure that there must be a way of only using one flag field to make it work and I feel I probably am using more recordsets than are strictly necessary. Additionally I have set the loop on the wend statement subjectively at 200 there is room to dynamically set this figure by measuring the count in the conversion table and then dividing that number by the number you have set the query at - in this case 13. As it stands. Conversion tables with a record count beyond 200 * 13 would have those records above this number un-translated. That's not an issue for me as I don't have quite that many nested If statements to make.

Out of interest I tried a for next loop as well but didn't seem to like that.

After all that probably will use a table anyway

 

[Lightwave]

THE CODE : on the off chance people are interested
The following iterates through a conversion table and writes the resulting SQL into a table named T005NestedIIFs.

To have this working you will need two tables and know what table the resulting SQL will be written against and what field will be updated.
T001ConversionCodeTable
This should have 5 fields
OldValue
NewValue
xFlag1
xFlag2
xFlag3

The flag fields only require a single integer value.

The queries are placed in table
T005NestedIIFs
In which I have created 3 fields
SQLfield
TargField
TargTable

TargetFieldforUpdate and TargetTable may not strictly be necessary but I wanted them for my task.

I allow the setting of variable number of nested IIF at function call on the off chance that sometime in the future if I am requiring this for statements to be run in Oracle and SQL SERVER I can if I want max out the number of loops to create a single string and because for other tasks - like exporting to EXCEL or heaven forbid a management policy on limiting the number of emails to be bcc'd in a single email I can define whatever limit is required.

Public Function CreateTableofSQL(TargetTable As Variant, TargetFieldforUpdate As Variant, BatchSizeNo As Long)
On Error GoTo Err_Writetofile

Dim rst As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim rst3 As DAO.Recordset
Dim rst4 As DAO.Recordset
Dim RecordCount1 As Long
Dim RecordCount2 As Long
Dim LCounter As Integer
Dim SQLString1 As String

LCounter = 1

While LCounter < 200 LCounter = LCounter + 1 SQLString1 = "" 'Three recordsets were testing differing flag fields because order of the integrity of the recordsets were being affected by each other Set rst2 = CurrentDb.OpenRecordset("SELECT TOP " & BatchSizeNo & " T001CodeConversionTable.PKID, T001CodeConversionTable.OldValue, T001CodeConversionTable.NewValue, T001CodeConversionTable.xFlag2 FROM T001CodeConversionTable WHERE (((T001CodeConversionTable.xFlag2)<>1));")
Set rst3 = CurrentDb.OpenRecordset("SELECT TOP " & BatchSizeNo & " T001CodeConversionTable.PKID, T001CodeConversionTable.OldValue, T001CodeConversionTable.NewValue, T001CodeConversionTable.xFlag3 FROM T001CodeConversionTable WHERE (((T001CodeConversionTable.xFlag3)<>1));")
Set rst = CurrentDb.OpenRecordset("SELECT TOP " & BatchSizeNo & " T001CodeConversionTable.PKID, T001CodeConversionTable.OldValue, T001CodeConversionTable.NewValue, T001CodeConversionTable.xFlag1 FROM T001CodeConversionTable WHERE (((T001CodeConversionTable.xFlag1)<>1));")

'MoveLast required to ensure correct record count at first pass reset to first after this
rst.MoveLast
RecordCount1 = rst.RecordCount
rst.MoveFirst
rst3.MoveLast
RecordCount2 = rst3.RecordCount
rst3.MoveFirst

SQLString1 = SQLString1 & "UPDATE " & TargetTable & " SET " & TargetTable & "." & TargetFieldforUpdate & "="
Do Until rst.EOF = True
rst.Edit
rst!xFlag1 = 1
rst.Update
RecordCount1 = RecordCount1 - 1
SQLString1 = SQLString1 & "IIF((" & TargetTable & "!" & TargetFieldforUpdate & "='" & rst!OldValue & "'),'" & rst!NewValue & "'"
If RecordCount1 = 0 Then
SQLString1 = SQLString1 & " "
Else
SQLString1 = SQLString1 & ","
End If
rst.MoveNext
Loop

rst.Close

Do Until rst2.EOF = True
SQLString1 = SQLString1 & ")"
rst2.Edit
rst2!xFlag2 = 1
rst2.Update
rst2.MoveNext
Loop
SQLString1 = SQLString1 & " WHERE (("

rst2.Close

Do Until rst3.EOF = True
RecordCount2 = RecordCount2 - 1
SQLString1 = SQLString1 & "(" & TargetTable & "!" & TargetFieldforUpdate & ")='" & rst3!OldValue & "'"
rst3.Edit
rst3!xFlag3 = 1
rst3.Update
If RecordCount2 = 0 Then
SQLString1 = SQLString1 & " "
Else
SQLString1 = SQLString1 & " OR "
End If

rst3.MoveNext
Loop

rst3.Close

SQLString1 = SQLString1 & "));"

Set rst4 = CurrentDb.OpenRecordset("T005NestedIIFs")
With rst4
.AddNew
rst4!SQLfield = SQLString1
rst4!Targfield = TargetFieldforUpdate
rst4!Targtable = TargetTable
rst4.Update
rst4.Close
End With

Wend

Exit_WritetoFileError:
Exit Function

Err_Writetofile:
If Err.Number = 3021 Then
MsgBox "All records in Translation table written out no more to translate"
Else
MsgBox Err.Description
End If
Resume Exit_WritetoFileError

End Function

 

[Cronk]

I think this is being made over complicated if indeed you want to break down the universe of records into groups. I'd just loop through the universe with a counter.

Something like
set rst = db.openrecordset("select email from YourTable")
n =0
rst.movefirst
do while not rst.eof
str = str &";" & rst!Email
n = n+1
if n= YourMax then
'do whatever
n=0
endif
rst.movenext
loop

I've done this for clients where their ISP for spam reasons, has limits on the number of addressees in a single email, which I assume is your requirement.

Of course if you have a requirement to send to a number of random addressees, that is a different situation.