Distinct Recordset SQL as Loop Variable

[hmiller]

Hey Guys,

I'm just playing around with some code I wrote for work and am trying
to minimize the coding as much as possible.

So two questions:

1. What are your best practices for organizing your code, aside from
comments? I typically split my code up into sub procedures and then run
them all from the top of the code. My problem is that my code page is
taking me a while to scroll through and find the code i'm looking for.

2. I have two queries. The first creates a recordset of unique values
of a single field. The second uses that list of unique values as a
variable to loop through another recordset and pull the correct
records. Is there away to write some SQL that would combine these two
queries.

Private Sub Tabs()

Dim db As DAO.Database
Set db = CurrentDb()

db.Execute _
"SELECT DISTINCT[Customer Group] FROM [Customers];", _
dbFailOnError

End Sub
__________________________________________________ _____________________
I hacked this up for privacy, hopefully you can make sense of it! :)

Private Sub Export()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim XLS As DAO.QueryDef
Dim rs1 As String
Dim SQL As String

Set db = CurrentDb()
Set rs = db.OpenRecordset("Tabs", dbOpenSnapshot)

Do Until rs.EOF

rs1 = rs.Fields("Customer Group")

SQL = etc.

Set XLS = db.CreateQueryDef(rs1, SQL)

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
rs1, "c:\test\loss.xls", True

db.QueryDefs.Delete rs1

rs.MoveNext

Loop

rs.Close
XLS.Close

End Sub

Thanks guys, hope that clear enough

[Rich P]

>>
...
rs1 = rs.Fields("Customer Group")

SQL = etc.

Set XLS = db.CreateQueryDef(rs1, SQL)
...
<<

SQL = etc. must be the 2nd query. Could you post what that second
query looks like - then we can combine it with the first query. The
combining will looke something like this:

Select ... From tblX t1 Join tbly t2 On t1.ID = t2.ID
Where exists
(SELECT DISTINCT[Customer Group] FROM [Customers] t3 Where t3.ID =
t1.ID)

This will return all the rows for each distinct Customer group in one
shot. The query above will only work if the primary table and the Where
Exists table has a primary key field (which in this case I called ID).

Rich

*** Sent via Developersdex http://www.developersdex.com ***