Inner Join Error

[ConfusedNAccess]

anyone see what's wrong with this?

4: Sort the data by the random number and move the top 25 into a new table
strTableName = "tblRandom_" & Format(Date, "ddmmmyyyy")
strSQL = "SELECT tbltemp.LSO_ID, tbltemp.LSO, tbltemp.AM, tbltemp.BorrowerName, tbltemp.LastCalcDate, tbltemp.CalcType " & _
"INTO " & strTableName & " " & _
"FROM tbltemp INNER JOIN " & _
"QryLSOusers ON LastCalcs.LSO_ID = [QryLSOusers].UserIDMonitoring " & _
"WHERE (((tbltemp.LSO_ID)=[QryLSOusers]![UserIDMonitoring]))" & _
"ORDER BY tblTemp.randomnumber;"

 

[Zion7]

Remove the bang(!) operator

...)=[QryLSOusers]![UserIDMonitoring...

 

[PHV]

Replace this:
LastCalcs
with this:
tbltemp

and get rid of the unnecessary where clause.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ConfusedNAccess]

Thanks, for that.. the where clause.. ok.. here's the whole code.. i'm trying get a total of 25 random records and of those 25 random records have 5 records for each lso-ID.. here's the code.. any idease?

Sub PickRandomR()
Dim db As Database
Dim tdf As TableDef
Dim fld As Field
Dim rst As Recordset
Dim strSQL As String
Dim strTableName As String


' 1: Create a new temporary table containing the required fields
'

http://www.fontstuff.com/vba/vbatut02.htm

strSQL = "SELECT lastcalcs.lso_id, lastcalcs.lso, lastcalcs.AM, lastcalcs.borrid, lastcalcs.borrowername, lastcalcs.lastcalcdate, lastcalcs.calctype " & _
"INTO tblTemp " & _
"FROM lastcalcs;"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

' 2: Add a new field to the new table
Set db = CurrentDb()
Set tdf = db.TableDefs("tblTemp")
Set fld = tdf.CreateField("RandomNumber", dbSingle)
tdf.Fields.Append fld

' 3: Place a random number in the new field for each record
Set rst = db.OpenRecordset("tblTemp", dbOpenTable)
rst.MoveFirst
Do
Randomize
rst.Edit
rst![RandomNumber] = Rnd()
rst.Update
rst.MoveNext
Loop Until rst.EOF
rst.Close
Set rst = Nothing

' 4: Sort the data by the random number and move the top 25 into a new table
strTableName = "tblRandom_" & Format(Date, "ddmmmyyyy")
strSQL = "SELECT top 25 tbltemp.LSO_ID, tbltemp.LSO, tbltemp.AM, tbltemp.BorrowerName, tbltemp.LastCalcDate, tbltemp.CalcType " & _
"INTO " & strTableName & " " & _
"FROM tbltemp INNER JOIN " & _
"QryLSOusers ON tbltemp.LSO_ID = [QryLSOusers].UserIDMonitoring " & _
"WHERE (((tbltemp.LSO_ID)=[QryLSOusers]![UserIDMonitoring]))" & _
"ORDER BY tblTemp.randomnumber;"


DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True

' 5: Delete the temporary table
db.TableDefs.Delete ("tblTemp")
End Sub

 

[PHV]

In step 4 the WHERE clause is redundant due the JOIN.

 

[ConfusedNAccess]

ok.. sorry.. sent the wrong one..

strSQL = "SELECT top 25 tbltemp.lso_id, tbltemp.lso, tbltemp.am, tbltemp.borrid, lastcalcs.lastcalcdate, lastcalcs.calctype, tbltemp.borrowername " & _
"INTO " & strTableName & " " & _
"FROM tblTemp " & _
"Where ((tbltemp.lso_id in (select top 3 tbltemp.lso_id from tbltemp " & _
"where tbltemp.lso_id = QryLSOusers.UserIDMonitoring])))" & _
"ORDER BY tblTemp.randomnumber;

 

[PHV]

How JetSQL is supposed to know what QryLSOusers stands for ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[ConfusedNAccess]

i'm not sure how to define it.. what is the correct way? I'm new to this. Thanks for your help

 

[PHV]

what is the correct way
To do what ?
What is QryLSOusers ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886