I have a form bound to a non updatable query and am trying to work out how to make the form updatable, or more likely, find another way to achieve my ends.
A simplified version of the problem is as follows:
1:M relationship
Owner = tblTournament Table (TournamentID, TournamentName)
Member = tblMatch Table (MatchID, TournamentID, MatchDate)
The problem lies in wanting to sort the tournaments by date. The tournament date is decreed to be the oldest matchdate owned by that tournament.
So, the form’s recordsource is:
SELECT tbltournament.tournamentid, tblTournament.TournamentName, min(tblMatch.MatchDate) AS TournamentDate
FROM tblTournament INNER JOIN tblMatch ON tblTournament.TournamentID=tblMatch.TournamentID
GROUP BY tbltournament.tournamentid, tblTournament.TournamentName
ORDER BY min(tblMatch.MatchDate);
Which brings up the data I want, except that it is of course not updatable and so the form isn’t updatable either.
I could get around this by having a field TournamentDate on the Tournament table and populating it whenever the matchdate field is updated, but I don’t want to duplicate data on the database.
If I can get this to work I will then try to tackle a similar problem, namely a calculated field called MatchSequence which will represent that this match is the nth match in this tournament. I will again probably have the same sort of updatability problem.
I’ve looked at a few forums and many suggest that most things can be achieved with bound forms, but I suspect that what I want to do is impossible with bound forms or that I am missing something.
A simplified version of the problem is as follows:
1:M relationship
Owner = tblTournament Table (TournamentID, TournamentName)
Member = tblMatch Table (MatchID, TournamentID, MatchDate)
The problem lies in wanting to sort the tournaments by date. The tournament date is decreed to be the oldest matchdate owned by that tournament.
So, the form’s recordsource is:
SELECT tbltournament.tournamentid, tblTournament.TournamentName, min(tblMatch.MatchDate) AS TournamentDate
FROM tblTournament INNER JOIN tblMatch ON tblTournament.TournamentID=tblMatch.TournamentID
GROUP BY tbltournament.tournamentid, tblTournament.TournamentName
ORDER BY min(tblMatch.MatchDate);
Which brings up the data I want, except that it is of course not updatable and so the form isn’t updatable either.
I could get around this by having a field TournamentDate on the Tournament table and populating it whenever the matchdate field is updated, but I don’t want to duplicate data on the database.
If I can get this to work I will then try to tackle a similar problem, namely a calculated field called MatchSequence which will represent that this match is the nth match in this tournament. I will again probably have the same sort of updatability problem.
I’ve looked at a few forums and many suggest that most things can be achieved with bound forms, but I suspect that what I want to do is impossible with bound forms or that I am missing something.
