Multiple Table View with DA
I am building a sporting comp website, where I have a primary table called fixtures which has the following structure:
fixtureID (PK)
roundID (FK) -> Rounds Table
GameField
GameTime
DivisionID (FK) -> Divisions Table
HomeTeamID (FK) -> Team Table
AwayTeamID (FK) -> Team Table
HomeTeamScore
AwayTeamScore
Ref1ID (FK) -> Referees Table
Ref2ID (FK) -> Referees Table
Ref3ID (FK) -> Referees Table
If I use the DA Wizzard and select the Fixtures Table, I can create the Insert / Update / Delete pages etc. fine. The main issue I have is that the Results page will look something like:
fixtureID: 1
roundID: 1
GameField: 2
GameTime: 6:30pm
DivisionID: 1
HomeTeamID: 23
AwayTeamID: 42
HomeTeamScore: 4
AwayTeamScore: 2
Ref1ID: 1
Ref2ID: 2
Ref3ID: 3
Where the other linked Tables would contain this type of information.
Divisions Table ->
DivisionID = 1, Division_Name = Mens
DivisionID = 2, Division_Name = Mixed
Team Table ->
TeamID = 23, Team_Name = Team A
TeamID = 42, Team_Name = Team X
Rounds Table ->
RoundID = 1, Round_Desc = Round 1
...
RoundID = 15, Round_Desc = Qtr Final
Referees Table ->
RefID = 1, RefName = Bob
RefID = 2, RefName = John
RefID = 3, RefName = Michael
Thus I would like to see the results page reflect something like this:-
fixtureID: 1
round: Round 1
GameField: 2
GameTime: 6:30pm
Division: Mens
HomeTeamID: Team A
AwayTeamID: Team X
HomeTeamScore: 4
AwayTeamScore: 2
Ref1: Bob
Ref2: John
Ref3: Michael
In order to do that I would need a multi-table select querey or view to display the results page.
I am able to create views with my MySQL implementation (and I also know that one cant update MySQL Views), so for my In/Up/Del functions I would use the Fixtures Table byitself, but would like to see the Results page in a more readable format.
Is it possible after running the DA Wizzard to revisit the Results admin page and change the recordset from the single Fixtures Table to that of say a View Table (multi-table select) which would then show the more readable content.
Or is there any other way to do this?