aloha all!
i have a fantasy football league now in it's 33rd year. i have all the records for all the head to head games through those years and want to create a database where i can quickly query any number of reports based on that data. some examples would be, basic win/loss/tie records for any given year for any given team; w/l/t records for one team versus another team over a range of years or all years; total points scored for and against; points one team scores against another team over a range of years or all years, etc...
right now i have all of this data stored in an excel spreadsheet and it looks like the following. i have my column headers and then a section of a few sample weeks/games shown here. "wktype" is regular season versus playoffs, "bowl" is what round of the playoffs that game was (so a blank entry for regular season game). "teamffl" and "pf" is one team and their score that week; "opp" and "pa" is their opponent and their score that week. it seems like i should be able to have the database then determine that for instance in week 14 of the regular season DID beat ABP4B 37 to 35 so it's a win for DID and a loss for ABP4B and based on that each teams overall records can be compiled based on any number of possible queries stated above.
YEAR WKTYPE WEEK TEAMFFL PF PA OPP BOWL 2022 RS 14 DID 37 35 ABP4B 2022 RS 14 HFJ 58 68 MM 2022 RS 14 GDL 58 62 BRW 2022 PL 15 CFB 46 54 FCJ FR 2022 PL 15 BFM 49 50 Chefs FR 2022 PL 15 HFJ 43 36 BRW FR 2022 PL 15 DID 77 62 MM FR 2022 PL 16 Chefs 62 48 FCJ FBCS 2022 PL 16 HFJ 39 74 DID FBCS
note, i'd be willing to pay someone to create this awesome database for me :-)