MS Access Query Help

thePITman · 01-08-2008, 11:23 PM

The goal of this query is to display the same table as on the following page:
DIII Wooster District Web Site Home

I am trying to convert all the pages therein to data-driven databases instead of updating every page, record, and calculation by hand. I want to be able to enter the score for a given game (in the "Schedule" table below) and have the queries do the rest. I have a couple tables, but the following table is the one actually being used in the query on which I am working.

Table: Schedule
Field: "TeamName" with text for Team's Name
Field: "Opp" with text for Opponent's Name (irrelevant)
Field: "OppDiv" with number value for Opponent's Division
Field: "LgGame" with Yes/No whether or not it is a league game (irrelevant)
Field: "Date" (irrelevant)
Field: "Loc" for Location (irrevelant)
Field: "TeamScore" with number value for TeamName's score
Field: "OppScore" with number value for Opponent's score

The Schedule table above contains all games for all teams for which I am tracking. For instance, TeamName would be "Chippewa" for the first 20 records (all regular season games) with each of their games' information held in the other fields, then the next 20 records would have "Hillsdale" at TeamName, etc. etc.

I want the query to group by TeamName so that each team's name only shows up once. I then want 5 additional columns, 4 for the team's record for each of the four possible OppDiv (1, 2, 3, or 4, with blank also an option), and then a 5th column for overall record including games against opponents who are not in a division. Bascially what I want is the following, but I don't know how to set up the query. I get "aggregate function" errors and "data type mismatch" errors whenever I try to tweak things.

Field1: Team Name
Field2: D I (Record against Division 1 teams)
COUNT(TeamScore>OppScore) & "-" & COUNT(TeamScore<OppScore) WHERE OppDiv = 1
Field3: D II (Record against Division 2 teams)
COUNT(TeamScore>OppScore) & "-" & COUNT(TeamScore<OppScore) WHERE OppDiv = 2
Field4: D III (Record against Division 3 teams)
COUNT(TeamScore>OppScore) & "-" & COUNT(TeamScore<OppScore) WHERE OppDiv = 3
Field5: D IV(Record against Division 4 teams)
COUNT(TeamScore>OppScore) & "-" & COUNT(TeamScore<OppScore) WHERE OppDiv = 4
Field6: Overall (Overall record, even including opponents with no Division)
COUNT(TeamScore>OppScore) & "-" & COUNT(TeamScore<OppScore)

Any help is greatly appreciated. Is this possible with just one query going off of the Schedule table? If not, what do I need to add/create? Thank you very much.

thePITman · 01-09-2008, 10:38 PM

Attached is my MDB file in a ZIP if it helps. Thanks!

01-08-2008, 11:23 PM	#1 (permalink)
thePITman Registered User Join Date: Apr 2006 Posts: 32 OS: WinXp	MS Access Query Help The goal of this query is to display the same table as on the following page: DIII Wooster District Web Site Home I am trying to convert all the pages therein to data-driven databases instead of updating every page, record, and calculation by hand. I want to be able to enter the score for a given game (in the "Schedule" table below) and have the queries do the rest. I have a couple tables, but the following table is the one actually being used in the query on which I am working. Table: Schedule Field: "TeamName" with text for Team's Name Field: "Opp" with text for Opponent's Name (irrelevant) Field: "OppDiv" with number value for Opponent's Division Field: "LgGame" with Yes/No whether or not it is a league game (irrelevant) Field: "Date" (irrelevant) Field: "Loc" for Location (irrevelant) Field: "TeamScore" with number value for TeamName's score Field: "OppScore" with number value for Opponent's score The Schedule table above contains all games for all teams for which I am tracking. For instance, TeamName would be "Chippewa" for the first 20 records (all regular season games) with each of their games' information held in the other fields, then the next 20 records would have "Hillsdale" at TeamName, etc. etc. I want the query to group by TeamName so that each team's name only shows up once. I then want 5 additional columns, 4 for the team's record for each of the four possible OppDiv (1, 2, 3, or 4, with blank also an option), and then a 5th column for overall record including games against opponents who are not in a division. Bascially what I want is the following, but I don't know how to set up the query. I get "aggregate function" errors and "data type mismatch" errors whenever I try to tweak things. Field1: Team Name Field2: D I (Record against Division 1 teams) COUNT(TeamScore>OppScore) & "-" & COUNT(TeamScore<OppScore) WHERE OppDiv = 1 Field3: D II (Record against Division 2 teams) COUNT(TeamScore>OppScore) & "-" & COUNT(TeamScore<OppScore) WHERE OppDiv = 2 Field4: D III (Record against Division 3 teams) COUNT(TeamScore>OppScore) & "-" & COUNT(TeamScore<OppScore) WHERE OppDiv = 3 Field5: D IV(Record against Division 4 teams) COUNT(TeamScore>OppScore) & "-" & COUNT(TeamScore<OppScore) WHERE OppDiv = 4 Field6: Overall (Overall record, even including opponents with no Division) COUNT(TeamScore>OppScore) & "-" & COUNT(TeamScore<OppScore) Any help is greatly appreciated. Is this possible with just one query going off of the Schedule table? If not, what do I need to add/create? Thank you very much.