Question Comparing two fields?

[adamskiii]

I am making a database for hockey teams and games. How can I create a validation rule so that one team cannot play itself and that a team can not play the same team more than once a day? I have tried:

IIf([homeTeamId]=[awayTeamId])

But this is not working. Any help would be appreciated.

Thanks

 

[scooteman]

If you are adding the information through forms, you may be better off to use VBA than the validation property. I had a similar issue and it was suggested in this forum for me to use the DCount() function. I have a training database and a form to spot missing training. One subform on it shows all the training they should have and another subform shows what they actually do have. It is setup so I can click on a training type and add an undated record to the individual so it could be tracked on reports. But I needed to limit it to only training that was actually missing. I did it with the DCount fucntion and a querry. My code looks like this:

Private Sub Training_Name_Click()
Dim trainmsg As Integer
Dim LTotal As Long
LTotal = DCount("Training_ID", "Query_Find_Null")
If LTotal = 0 Then
trainmsg = MsgBox("Are you sure you want to add this training type to the selected employee?", 1, "Training Addition Message")
If trainmsg = 1 Then
DoCmd.SetWarnings warningsOff
DoCmd.OpenQuery "Query_Append_unassignedtraining"
DoCmd.SetWarnings warningsOn
Forms![spot_unassigned_training]![What_Employee_does_Have].Requery
MsgBox "A new record has been added.", vbInformation + vbOKOnly, "Record Added"
End If
Else
If LTotal >= 1 Then
MsgBox "Training type already exists and will not be added.", vbInformation + vbOKOnly, "No Record Added"
End If
End If
End Sub

 

[adamskiii]

thanks for the answer but I do not know how to use the forms yet in Access. Is there any way to use the validation?

Thanks

 

[scooteman]

I don't think you can do that type of validation on the feild level. Try it at the Table Records property level. (In table Design mode, right click on the top left corner to get to the table records property sheet) Are the ID feilds numerical? If they are a simple rule at the table records level that would work to keep the team from playing itself would be:

[HomeTeamID]<>[AwayTeamID]

I am not sure what to recommend for keeping one team from playing another team more than once in a day. More informaition on your dabase structure would help. How is you database setup? How many tables? Hopefully someone here in this forum who is much more knowledgeable than I can give you a better answer.

 

[vbaInet]

Scooteman has just giving you some good advice. If you want to have this level of validation then you would need to use forms.

As for the team not playing with itself, then you would set the TeamID field not to allow duplicates.

 

[adamskiii]

Thank you cooteman. I got the validation to work but now if I was to insert data in again to show that a team is going to play the same one again, but on another day I get this error:

Any help on how to remove this? I have allowed it to make duplicates but still nothing.

Thanks

 

[scooteman]

I would recommend you add a new field called [RecordID], set it to autonumber and make it the primary key field. I am guessing that the table has made your [HomeTeamID] field as the primary key field and will not allow duplicates.

 

[adamskiii]

I have added a RecordId field, made it an auto number and primary key but still the same message

 

[Brianwarnock]

thanks for the answer but I do not know how to use the forms yet in Access. Is there any way to use the validation?

Thanks

Then learn pronto.

Tables are for storing data, they are not for looking at or working on directly.

You enter data via a form

You enquire on the data via a query and then you look at the data via a form or report based on that query.

Brian

 

[scooteman]

I have added a RecordId field, made it an auto number and primary key but still the same message

Make sure all your other fields are set to allow duplicates. The one that was the previouse primary key may still be set to not allow dulicates.

 

[scooteman]

I have added a RecordId field, made it an auto number and primary key but still the same message

adamskiii,

Did you get your database working? Are you using Access 2007? Brianwarnock is correct in that you should enter data through forms instead of directly in the tables. Forms can be setup to look like a data sheet similar to the table if that is how you prefer to enter data. If you need help with building a form, let me know.

I learned to build forms by disecting the forms in different MS templates to see how the various functons worked.

 

[adamskiii]

Thanks for all the answers. I got it to work fine now . Just one last question. In my relationships, how do I make so that I have my teamId in my team table connects to both the awayTeamId and homeTeamId in the game table? It says I can only have one relationship per field but I know there is a way to do this.

Thanks

 

[MSAccessRookie]

Thanks for all the answers. I got it to work fine now . Just one last question. In my relationships, how do I make so that I have my teamId in my team table connects to both the awayTeamId and homeTeamId in the game table? It says I can only have one relationship per field but I know there is a way to do this.

Thanks

Prehaps you could create an Index to the table that has no duplicates, and uses multiple fields (Home Team, Away Team, Date of Game). This would prevent duplications and might give you want you want.

http://www.ehow.com/how_13622_create-index-microsoft.html

 

[adamskiii]

That is not working for me. Here is my query:

I want that empty line to be like the one above it, a many-to-one relationship. I cant figure it out. I want to show a query of game dates and which teams were away/home. Cant do it because awayTeamId is not related to the team table.

 

[scooteman]

I don't think queries will run with the double join you're trying to make. It should work fine with just the one join of [TeamID] to [HomeTeamID]. Are the Away Teams not in your [Team] table? If they are not, then adjust your join type to show all the [Game] records and only [Team] records that are equal and see if that works.

 

[scooteman]

adamskiii,
You may want to rethink your game table. Are all the teams that play, are they all in the Team table? Does a record only hold the score for one team or are your recording the scores for both teams.

If all the teams are not in your Team table, them maybe it would be better to have the Game table record set to one TeamID and add a field that contains the info on whether the team played at home or away. The record field could even be a Yes/No field titled [HomeField]. It would cause less confusions with the queries this way.

It may be even better to split you game table into two tables a Game table and a Game Details Table. Have the Game table contain the fields:
GameID
Date
Time
Arena

Game Details Table could contain:
GameID
TeamID
HomeAway (can be a Yes/No field or store the text Home and Away)
Score

In GameDetial you would store one record for each team in the game and designate if the team was home or away.

 

[MSAccessRookie]

That is not working for me. Here is my query:

I want that empty line to be like the one above it, a many-to-one relationship. I cant figure it out. I want to show a query of game dates and which teams were away/home. Cant do it because awayTeamId is not related to the team table.

Try adding another copy of the Table team (in design mode), and linking the awayTeamID to it instead of to the original Table. Then set the Criteria for homeTeamID as <> awayTeamID, and see if that helps at all.

 

[Brianwarnock]

I would either

! Have 2 copies of the Game table joined on gameID and each joined to the team table on teamid


Or
2 Run 3 queries, 1 to extract home teams, 2 to extract away teams, 3 join 1 and 2 on gameid and get both teams.

Brian

 

[adamskiii]

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

 

[Brianwarnock]

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

Perhaps thats because max doesn't do that as a read of help would have shown.
You are going to need to use an IIF construct or write a function, and don't forget it could be a draw.

Brian