fantasy sports records and scores database

[pdonoho]

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 :-)

[June7]

Review https://www.reddit.com/r/fantasyfoot...cess_database/

Working with your sample data in Access, consider:

Query1:
SELECT ID, Year, WKTYPE, Week, TeamFFL, PF, PA, OPP, Bowl, IIf([PF]>[PA],[teamffl],[opp]) AS Team, "Win" AS Status FROM FFLGames
UNION SELECT ID, Year, WKTYPE, Week, TeamFFL, PF, PA, OPP, Bowl, IIf([PF]<[PA],[teamffl],[opp]), "Loss" FROM FFLGames;

Query2:
TRANSFORM Count(Query1.ID) AS CountOfID
SELECT Query1.YEAR, Query1.Team
FROM Query1
GROUP BY Query1.YEAR, Query1.Team
PIVOT Query1.Status;

Year	Team	Loss	Win
2022	ABP4B	1
2022	BFM	1
2022	BRW	1	1
2022	CFB	1
2022	Chefs		2
2022	DID		3
2022	FCJ	1	1
2022	GDL	1
2022	HFJ	2	1
2022	MM	1	1

More about CROSSTAB query http://allenbrowne.com/ser-67.html

[mike60smart]

Hi
Can you upload your database?