Probably.. said:
I did change "Unique Values" to "Yes," so I don't know what went
wrong. But when I viewed the SQL, it said SELECT DISTINCTROW...
So I deleted the "ROW" part of the word, and it now WORKS: It
eliminates the duplicates as you wished. I have copied the SQL for
you anyway (it will wrap around, so I'll number the lines):
1
SELECT DISTINCT tblWorksites.fldWS_HomeID,
tblDailySessions.fldDS_WorksiteID, tblDailySessions.fldDS_ResidentID
2
FROM tblDailySessions LEFT JOIN tblWorksites ON
tblDailySessions.fldDS_WorksiteID = tblWorksites.fldWorksiteID
3
ORDER BY tblWorksites.fldWS_HomeID, tblDailySessions.fldDS_WorksiteID,
tblDailySessions.fldDS_ResidentID;
It's not an error message: we put that in the program to show it's working
right. This time it does look fine.
Oh! <red face>
Creating the table goes like this: we will run through the
tblSessionLookup to create one column in the temp table for each Session.
Let's make a new procedure like this: copy all this text into the BOTTOM of
the module you have already (below the End Function)
and replace them all with
' Call a proc to create the new table
MakeTempTable c_strTempTable
Save it all and run it with TryThis again.
I did this.
You should see the DROP TABLE message...
Yes.
But then it gave me a "Compile Error - Variable Not Defined" message.
When I clicked okay, the line:
Private Sub MakeTempTable(NewTableName As String)
was highlighted in yellow.
and in the following section:
' Add a column for every line in tblSessionLookup
Do While Not rsSessions.EOF
strSQL = strSQL & "," & vbNewLine & _
" " & rs!fldSessionLookUp & " TEXT(1)"
the letters "rs" in the last line were highlighted in blue.
When I tried to run the procedure a second time (just to see what
happened), I no longer got the first DROP TABLE message, and there is
no table in the Database Window.
So... do we Dim "rs"?
Oops! I forgot! (grin)
I have pasted the entire code below as it now stands.
Thanks!!!
Jessi
___________________
Option Compare Database
' this line forces VBA to check the existence of all the variables
Option Explicit
'This is a little test program just to get into things:
Public Sub TryThis()
'"dim" statements let VBA know what variables we need
'we are using DAO so make sure it is checked in Tools...References
'
Dim db As DAO.Database ' handle to the database engine
Dim strSQL As String ' a SQL command
'"const" statments are convenient ways to handle names etc.
'that might change but usually don't
Const c_strTempTable As String = "t_tblWeeklySchedule"
'set up the handles
Set db = CurrentDb()
'check if there is already a temp table
If TableExists(c_strTempTable) Then
'get rid of it
strSQL = "DROP TABLE " & c_strTempTable
'remove this line once we are happy it's working
MsgBox strSQL
'carry it out
'dbFailOnError makes an error if there was a problem with the SQL
db.Execute strSQL, dbFailOnError
End If
' Call a proc to create the new table
MakeTempTable c_strTempTable
'okay, run the command
db.Execute strSQL, dbFailOnError
End Sub
'Quick function to see if the table already exists
Private Function TableExists( _
TableName As String) As Boolean
'dim statements as above
Dim db As Database
Dim tdf As TableDef
'set up handles
Set db = CurrentDb()
'don't respond to the error
On Error Resume Next
'this will cause an error if the table does not exist
Set tdf = db.Tabledefs(TableName)
'now see if there _was_ an error
If Err.Number = 0 Then
'okay: the table was there
TableExists = True
Else
'not okay, it wasn't there
TableExists = False
End If
End Function
' Create a new temp table using columns from the SessionLookup table
'
Private Sub MakeTempTable(NewTableName As String)
' variables
Dim db As Database ' current db
Dim strSQL As String ' make a command
Dim rsSessions As Recordset ' sessionlookup table
' Handle to current database
Set db = CurrentDb()
' Get the sessions
strSQL = "SELECT fldSessionLookUp " & vbNewLine & _
"FROM tblSessionLookup" & vbNewLine & _
"ORDER BY fldSessionSortNo;"
' it's a ForwardOnly snapshot because it's fastest
Set rsSessions = db.OpenRecordset( _
strSQL, dbOpenSnapshot, dbForwardOnly)
' Now we start the new table
strSQL = "CREATE TABLE " & NewTableName & vbNewLine & _
"( fldResidentID LONG CONSTRAINT pk PRIMARY KEY"
' Add a column for every line in tblSessionLookup
Do While Not rsSessions.EOF
strSQL = strSQL & "," & vbNewLine & _
" " & rs!fldSessionLookUp & " TEXT(1)"
' move to next one
rsSessions.MoveNext
Loop
' complete the command
strSQL = strSQL & ");"
' delete this too when it's all working: not for a while!!
MsgBox strSQL
' and try running it
db.Execute strSQL, dbFailOnError
' all well, tidy up
rs.Close
Set rs = Nothing
Set db = Nothing
End Sub
End Sub
