<%

Set dbaseConn = Server.CreateObject("ADODB.Connection")
dbaseConn.Open "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & Server.Mappath("\WednesdayNumbers2008Trial.mdb") & ";"

' Now, create the SQL statement
SQLQuery = "SELECT * FROM winningNumbers WHERE (number1='" & Num1 & "') OR (number2='" & Num1 & "') OR (number3='" & Num1 & "') OR (number4='" & Num1 & "') OR (number5='" & Num1 & "') OR (number6='" & Num1 & "');"

Set RsIndividual = dbaseConn.Execute(SQLQuery)

Do while not RsIndividual.EOF
   SQLQuery2 = "INSERT INTO tempTable  VALUES (" & RsIndividual("number1") & ", " & RsIndividual("number2") & ", " & RsIndividual("number3") & ", " & RsIndividual("number3") & ", " & RsIndividual("number4") & ", " & RsIndividual("number5") & ", " & RsIndividual("number6") & " );"
set RsIndividual2 = dbaseConn.Execute(SQLQuery2)
RsIndividual.MoveNext
Loop

' Close the Recordset object and destroy it
RsIndividual.Close
Set RsIndividual = Nothing

RsIndividual2.Close
Set RsIndividual2 = Nothing

QryFrequency = "SELECT QryUnionLottery.LotNum, Count(QryUnionLottery.LotNum) AS ItemCount FROM QryUnionLottery GROUP BY QryUnionLottery.LotNum ORDER BY Count(QryUnionLottery.LotNum) DESC;"

' Execute the SQL statement, and set the recordset object
' to the result of this execution. We obtain the resulting
' records in Rs object
Set RsFrequency = dbaseConn.Execute(QryFrequency)

' Use this RecordSet object to populate your HTML output stream
' In this example, we will just write out the last name field
Do While NOT RsFrequency.EOF
  Response.Write(Num1)
  Response.Write("-")
  Response.Write(RsFrequency.Fields("LotNum").value)
Response.Write("<br/>")
  ' Move to the next record in the resultset
  RsFrequency.MoveNext
Loop

' Close the Recordset object and destroy it
RsFrequency.Close
Set RsFrequency = Nothing

' You might want to release the resources for connection object,
' unless you want to use the same connection again in the later code
dbaseConn.Close
Set dbaseConn = Nothing
						%>

SELECT number1 AS LotNum
FROM tempTable
UNION ALL SELECT number2 as LotNum
FROM tempTable
UNION ALL SELECT number3 as LotNum
FROM tempTable
UNION ALL SELECT number4 as LotNum
FROM tempTable
UNION ALL SELECT number5 as LotNum
FROM tempTable
UNION ALL SELECT number6 as LotNum
FROM tempTable;

SELECT number
     , Count(*) As [frequency]
FROM   datable
GROUP
    BY number