First game of the season

[Bengtsson]

Hi.
I am a database and Access rookie.
The baseball season in Sweden getting closer. Here is a table with all game results from 2003-2019. Now I want to know the result of the first home game of the season for each team. Any suggestions?

[CJ_London]

what is the start of the season? you just said it's getting near - 1st May?

you also appear to have duplicates - team 5 played team 2 on twice on 2003 -05-10, same for team 6 v team 4 and team 3 v team 7

would also help if you clarified what gametime_f and gametime_t mean

also the construction of gameID - I can see it is year and a number incrementing from 1001 - what happens in 2004? continue incrementing or start again at 1001? And can this value be used to determine first?

[Bengtsson]

what is the start of the season? you just said it's getting near - 1st May?

you also appear to have duplicates - team 5 played team 2 on twice on 2003 -05-10, same for team 6 v team 4 and team 3 v team 7

would also help if you clarified what gametime_f and gametime_t mean

also the construction of gameID - I can see it is year and a number incrementing from 1001 - what happens in 2004? continue incrementing or start again at 1001? And can this value be used to determine first?

Hi, thanks for your reply. The season starts 4 / 25-2020. It looks like duplicates but it's actually doubleheader games. The scores are different as you can see. gametime_f is the start time of the matches and can be excluded. The pattern in the game_id column is 20031001 to 20031096 in 2003, 20041001 to 20041096 in 2004, 20051001 to 20051509 in 2005, and so on. The problem is that teams can start their first home game with xxxx1005 for example.


http://www.heatstat.se/heatstat/main...scoreboard_new

[pbaldy]

Post 3 was moderated, I'm posting to trigger email notifications.

[CJ_London]

not being conversant with baseball games - what does 'doubleheader' mean? for the purposes of your requirement does that count as one game or two? i.e. for team 5 do you want to see 2 or 4 returned - and regards first game - you say ignore the start time, but that is critical in this situation to determine first game? Or do you want to use the '1001 to 1096' to determine the order (and therefore first), even if the start times are transposed?

[Bullschmidt]

Hope this isn't a homework assignment LOL!

How about creating a totals subquery perhaps called qryFirstHomeGameResults_Part1 something like this:
SELECT game.gamedate, game.hometeam, game.homescore, game.awayteam, game.awayscore
FROM qryFirstHomeGameResults_Part1 INNER JOIN game ON (qryFirstHomeGameResults_Part1.gamedatemin = game.gamedate) AND (qryFirstHomeGameResults_Part1.hometeam = game.hometeam)
ORDER BY game.year, game.hometeam;

And then create the final query perhaps called qryFirstHomeGameResults_Part2 based on the qryFirstHomeGameResults_Part query and the game table something like this:
SELECT game.gamedate, game.hometeam, game.homescore, game.awayteam, game.awayscore
FROM qryFirstHomeGameResults_Part1 INNER JOIN game ON (qryFirstHomeGameResults_Part1.gamedatemin = game.gamedate) AND (qryFirstHomeGameResults_Part1.hometeam = game.hometeam)
ORDER BY game.year, game.hometeam;

And then the results might end up looking something like this (using some sample data I just tried):
gamedate hometeam homescore awayteam awayscore
4/1/2019 3 6 5 2
4/2/2019 5 10 6 1
4/3/2019 6 4 3 1
4/1/2020 3 6 5 3
4/2/2020 5 10 6 1
4/3/2020 6 4 3 1