Question Comparing two fields?

[scooteman]

Thanks for the help. I was able to get it working and I can now see what teams were away/home on a particular date. I am now making a query that will show the name of the winning team. This is what my tables currently look like in Access:



To create the winning score I want to use:
max(homeScore, awayScore)


Where do I put these expressions in the query? I tried to put them in the field and Criteria but this is not working for some reason.

Thanks

If your querry holds both Home and away scores then you can add a calculated field with:

High Score: IIf([HomeScore]>[AwayScore],[HomeScore],IIf([HomeScore]<[AwayScore],[AwayScore],IIf([HomeScore]=[AwayScore],[HomeScore])))


I tested it and it worked to show the high score for each record.

 

[Brianwarnock]

If your querry holds both Home and away scores then you can add a calculated field with:

High Score: IIf([HomeScore]>[AwayScore],[HomeScore],IIf([HomeScore]<[AwayScore],[AwayScore],IIf([HomeScore]=[AwayScore],[HomeScore])))


I tested it and it worked to show the high score for each record.

I have told him to use an IIF , we should allow posters to use their brains.

Brian

 

[adamskiii]

Thanks everyone. Appreciate it. working great :

For Winning Team:
winningTeam: IIf([homeScore]>[awayScore],[homeTeam.name],IIf([awayScore]>[homeScore],[awayTeam.name]))

For Winning Score
winnerScore: IIf([homeScore]>[awayScore],[game].[homeScore],[game].[awayScore])

For Losing Score
loserScore: IIf([awayScore]>[homeScore],[game].[homeScore],[game].[awayScore])

For Score Spread
spread: Abs([homescore]-[awayscore])

 

[rahulgty]

Better you create 3 temporary tables. In first 2 table write the data which you want to compare. And in 3rd table you you put the unmatched data. For which you can use the below mentioned code after making necessary changes as per your requirement.

Private Sub cmdImport_Click()
Dim rs As New ADODB.Recordset
Dim rsChk As New ADODB.Recordset
Dim rsIns As New ADODB.Recordset
Dim strDel, str, strIns, strChk As String
Dim DtEnd, DtBgn, DtChk, VC As String
Dim Amt As Double

On Error GoTo Err_cmdImport_Click

DtEnd = Format(Me.txtDtEnd, "mm/dd/yy")
DtEnd1 = Format(Me.txtDtEnd1, "mm/dd/yy")
DtBgn = Format(Me.txtDtBgn, "mm/dd/yy")

DoCmd.SetWarnings False
strDel = "DELETE EPRS.* FROM EPRS"
DoCmd.RunSQL strDel
strDel = "DELETE EPRSChk.* FROM EPRSChk"
DoCmd.RunSQL strDel
strDel = "DELETE EPRSunmatched.* FROM EPRSunmatched"
DoCmd.RunSQL strDel

strIns = "INSERT INTO EPRS ( Amt, VC, nDate ) " _
& "SELECT Sum(QryIns01.Debit) AS SumOfDebit, QryIns01.VC, QryIns01.sDate " _
& "FROM QryIns01 GROUP BY QryIns01.VC, QryIns01.sDate"

DoCmd.RunSQL strIns

strIns = "INSERT INTO EPRS ( Amt, VC, nDate ) " _
& "SELECT Sum(QryIns02.Debit) AS SumOfDebit, QryIns02.VC, QryIns02.sDate " _
& "FROM QryIns02 GROUP BY QryIns02.VC, QryIns02.sDate"

DoCmd.RunSQL strIns

strIns = "INSERT INTO EPRSChk ( sDate, Amt, VC ) " _
& "SELECT Renewal.REN_DATE, Sum(Renewal.AMT) AS SumOfAMT, Right([VC],10) AS VCs " _
& "FROM Renewal " _
& "GROUP BY Renewal.REN_DATE, Renewal.VC, Renewal.Item " _
& "HAVING (((Renewal.REN_DATE) Between #" & DtBgn & "# And #" & DtEnd & "#) AND ((Sum(Renewal.AMT))>0) AND ((Renewal.Item)='Dishtv EPRS'));"

DoCmd.RunSQL strIns

strIns = "INSERT INTO EPRSChk ( sDate, Amt, VC ) " _
& "SELECT CardSale.SDate, Sum(CardSale.Amt) AS SumOfAmt, CardSale.Dealer " _
& "FROM CardSale " _
& "GROUP BY CardSale.SDate, CardSale.Dealer, CardSale.Item " _
& "HAVING (((CardSale.SDate) Between #" & DtBgn & "# And #" & DtEnd & "#) AND ((Sum(CardSale.Amt)) Is Not Null And (Sum(CardSale.Amt))>0) AND ((CardSale.Item)='Dishtv EPRS')) " _
& "ORDER BY CardSale.SDate"

DoCmd.RunSQL strIns
DoCmd.SetWarnings True

str = "Select * From EPRSChk Order By VC"
Call Connection
rs.Open str, CNN, adOpenStatic, adLockReadOnly
If rs.EOF = True Then
Else
rs.MoveFirst
Do Until rs.EOF
Amt = rs.Fields("Amt")
VC = rs.Fields("VC")
DtChk = rs.Fields("sDate")
strChk = "Select * From EPRS Where cDbl(Amt) = '" & [Amt] & "' And cStr(VC) = '" & [VC] & "' And cDate(nDate) = '" & [DtChk] & "'"
rsChk.Open strChk, CNN, adOpenDynamic, adLockReadOnly
If rsChk.EOF Then
strIns = "Select * From EPRSunmatched"
rsIns.Open strIns, CNN, adOpenDynamic, adLockOptimistic
rsIns.AddNew
rsIns.Fields("sDate") = DtChk
rsIns.Fields("Amt") = Amt
rsIns.Fields("VC") = VC
rsIns.Update
rsIns.Close
End If
rsChk.Close
rs.MoveNext
Loop
End If
rs.Close

str = "Select * From EPRS Order By VC"
Call Connection
rs.Open str, CNN, adOpenStatic, adLockReadOnly
If rs.EOF = True Then
Else
rs.MoveFirst
Do Until rs.EOF
Amt = rs.Fields("Amt")
VC = rs.Fields("VC")
DtChk = rs.Fields("nDate")
strChk = "Select * From EPRSChk Where cDbl(Amt) = '" & [Amt] & "' And cStr(VC) = '" & [VC] & "' And cDate(sDate) = '" & [DtChk] & "'"
rsChk.Open strChk, CNN, adOpenDynamic, adLockReadOnly
If rsChk.EOF Then
strIns = "Select * From EPRSunmatched"
rsIns.Open strIns, CNN, adOpenDynamic, adLockOptimistic
rsIns.AddNew
rsIns.Fields("sDate2") = DtChk
rsIns.Fields("Amt2") = Amt
rsIns.Fields("VC2") = VC
rsIns.Update
rsIns.Close
End If
rsChk.Close
rs.MoveNext
Loop
End If

rs.Close
CNN.Close

DoCmd.OpenTable "EPRSunmatched"


Exit_cmdImport_Click:
Exit Sub

Err_cmdImport_Click:
MsgBox Err.Description
Resume Exit_cmdImport_Click

End Sub