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?

You are on the rite track. To get the info to display the values from the other tables rather than their id's you would need to alter the recordset that is on the results page. This recordset is independent of the search on the page so you can alter this without altering the search.

If you have a query that will get at the info like this for you then you should try using that. I think it may be easiest to just start with the existing query and alter it by adding in some JOIN statements to link the tables together. I really think that this would be the easiest way to get the results showing how you would like them.

Hi

I had the same problem and found it easier to create the query and test it outside of the recordset/dreamweaver. I am new to MySQL, but I have found SQLyog (www.webyog.com) a really useful tool for this and there is a 30 day free trial, which I am using (but may end up actually getting the program as it saves so much time and typing). Their QueryBuilder is a very similar format to Microsoft Access if you have ever used that.

Once I have it working in SQLyog, I copy and paste the query into my recordset. Oh, but don't forget to remove the ";" from the end of the query in SQLyog as it really mucks things up if you keep it in there (took me ages to figure out why this was happening).

Hope this is of some help.

Jo

Thanks for the suggestion, this is great advice for anyone wanting to alter a recordset or create a new custom one. After confirming that your query works adding it to the page should ensure you get the same result.