How can i update so many diff results?

[editolis]

Hi all,
Need you help creating update qry or code.

Here is the problem.

I Have a table with thousands soccer results.

I Want to update the FinalPoint of the table.

We have 3 FinalPoints in soccer. Win – Draw – Defeat.

So if,

Home team wins then the result is: 1
Draw the result is: X
Away team wins then the result is: 2

The results (and my criteria) are for example: 0-0, 1-1, 2-2, 3-3 (X), 1-0, 2-0, 2-1 (1), 0-1, 0-4, 0-2(2) and more...

How can I do this?
How can I update my table with the final point of (1 or X or 2) when we have so many different results?

 

[ByteMyzer]

What does your table structure look like? Could you show us some sample data?

 

[MSAccessRookie]

Hi all,
Need you help creating update qry or code.

Here is the problem.

I Have a table with thousands soccer results.

I Want to update the FinalPoint of the table.

We have 3 FinalPoints in soccer. Win – Draw – Defeat.

So if,

Home team wins then the result is: 1
Draw the result is: X
Away team wins then the result is: 2

The results (and my criteria) are for example: 0-0, 1-1, 2-2, 3-3 (X), 1-0, 2-0, 2-1 (1), 0-1, 0-4, 0-2(2) and more...

How can I do this?
How can I update my table with the final point of (1 or X or 2) when we have so many different results?

Calculated data should not normally be stored in a table, since its value is subject to change. I suspect that what you want is a Form or Report to perform the calculation on Demand each time that it is needed for Display.

 

[Brianwarnock]

Assuming the score is in 1 field then
Result: IIf(left(score,1)=Right(score,1),"X", IIf(etc

Of course if any team reaches double figures it wont work, you have more parsing based on the - to consider.

Brian

 

[editolis]

Assuming the score is in 1 field then
Result: IIf(left(score,1)=Right(score,1),"X", IIf(etc

Of course if any team reaches double figures it wont work, you have more parsing based on the - to consider.

Brian

Brian the biggest score i have in my table is 9-2.
I Was thinking SELECT CASE. One score by one...

MSAccessRookie i need to store that data for complicate calculations.

ByteMyzer the table is very simple. The final point is text. I can show you some sample data but the team names are in greek.

 

[gemma-the-husky]

you shouldnt be scoring the games as text 4-0

you should have a column for the home team score, and a column for the away team score

secondly - how come you have thousands of results - eg in premier league, with 20 teams there are only 380 games per season. - do you have more than 1 season's results

anyway can you provide details of the fields you have on your table

 

[editolis]

you shouldnt be scoring the games as text 4-0

you should have a column for the home team score, and a column for the away team score

secondly - how come you have thousands of results - eg in premier league, with 20 teams there are only 380 games per season. - do you have more than 1 season's results

anyway can you provide details of the fields you have on your table

Here is my Table.

Also by the way can somebody help me convert the txtDATE column to Real DATE?

Thank you in advance...

 

[Brianwarnock]

secondly - how come you have thousands of results - eg in premier league, with 20 teams there are only 380 games per season. - do you have more than 1 season's results

He means that there are loads of combinations of scores.

brian

 

[Brianwarnock]

Took a look at the database but it was all Greek to me.

Why are you storing the score twice?

My approach to determining the result still applies, but you can just compare fields once you alter the format from text to numeric, and then reults with scores greater than 10 are ok.
I don't know what you intend to do with the result but I would store 0 for a draw incase you want to do totalling later.

As for converting text dates you could try CDATE or else look up Dateserial.

Brian

 

[gemma-the-husky]

blimey - its all greek to me

you actually need two points totals - the home team and the away teams
the goals columns should be numbers, not text

then you need an update query to set the home team points, and the away teams points

THIS IS HOME POINTS - slightly different for away ponits
iif( homegoals>awaygoals, 3, iif(homegoals = awaygoals, 1, 0))

-------------
now i'm not sure about the date - is this already stored as a date - or will your regional settings covert this to an access date time record

---------------
however, where do you get the data from - because it must be stored correctly in your input file?

 

[Brianwarnock]

I've just twigged, the 1 2 X is the pools rating, is that correct, its nothing to do with team points for the league?

I think we need more info.

Brian

 

[gemma-the-husky]

you may be right, Brian

I thought he wanted league points 3,1,0

if he just wants 1,2,X he can just modify the iif statement slightly

iif( homegoals>awaygoals, "1", iif(homegoals = awaygoals, "X", "2"))

------------
1,2,X is pools although UK pools are different now
might be different in Greece

home and away are 1
score draw is X
0-0 draw is 2

---------------------------
editolis

one other point - you shouldnt be storing the team names - ideally, you ought to find a way to store the teams in a lookup table, and store the look up

 

[Brianwarnock]

iif( homegoals>awaygoals, "1", iif(homegoals = awaygoals, "X", "2"))

I suggested this or rather something similar based on 1 field , back in post 4 but he obviously didn't like it, he wanted to use Select Case!!

Brian

 

[editolis]

I Think i am very close to find a solution.

I am doing somethink with code.

I will put my sample db in the next hour.

 

[editolis]

Here is my solution.

Also i have done some translation to help you.

Run qry1.

Any suggestions?

Also do you have solution for the txtDATE?

 

[Brianwarnock]

Also do you have solution for the txtDATE?

Did the methods I suggested not work?

Brian

decided to take a look at your Db and I have to say that that is the craziest solution I would wish to see, did you take no notice of any of the responses posted.

Brian

 

[editolis]

Brian the think is that my problem solved now.

I Store my data in a new table and everything is ok.

Anyway you did not like my code? Do you suggest me
any simpler or better solution?

I am willing to learn from you.

 

[Brianwarnock]

Look at post 12.
I have also suggested that you should change the fields type to numeric and that you do not need to store the result twice.

Brian

 

[editolis]

The truth is that i did not see the post 12.

I Lost 2 hours of my life making the code.

The simplest solution is this:

FP: IIf([Final_HomeTeamScore]>[Final_AwayTeamScore];"1";IIf([Final_HomeTeamScore]=[Final_AwayTeamScore];"X";IIf([Final_HomeTeamScore]<[Final_AwayTeamScore];"2")))

Anyway...

Have a nice day.

 

[Brianwarnock]

I would still advise making the score fields numeric. I know that you said, after Post 4, that the highest score was 9, but these things can come back to haunt you and 10-2 may be a home win , but in a TEXT comparison 10<2

Brian