Updateable max query?

[Guest]

Hello,
I'm completely lost at this stuff. I'm trying to base a query on a form,
where I will only see details for the most recent revision to a paper. I
achieved that, but I couldn't update the status of that revision. So now I'm
back at square one.

This is my query without any max functions. Field RevisionLevel is a number,
and I need the max revisionlevel for each PaperID, and be able to update
PaperStatus.


SELECT DISTINCT tblPaperRevision.PaperID, tblPaperRevision.RevisionLevel,
tblPaperRevision.PaperStatus, tblPaperRevision.DateDone,
tblPaperRevision.DateDue, tblPeople.FirstName, tblPapers.Discipline,
tblPeople.LastName, tblPapers.P1Author, tblPapers.PrimaryID,
tblPapers.SecondaryID, tblRevisions.RevisionDefinition, tblPapers.Subject,
tblPapers.PaperTitle
FROM (tblPeople RIGHT JOIN tblPapers ON tblPeople.PrimaryID =
tblPapers.PrimaryID) RIGHT JOIN (tblPaperRevision LEFT JOIN tblRevisions ON
tblPaperRevision.RevisionLevel = tblRevisions.RevisionLevel) ON
tblPapers.PaperID = tblPaperRevision.PaperID;

 

[Guest]

If you are using a Totals query where you see a lot of Group By's, you can't
update data through that query. Also you have a left join with often makes it
unable to update.

One of the rules of a relational database is that you should not store
derived data. Unless you are saving data at a point in time, such as an
invoice, you should NOT store data in a table that you can derive from
existing data. Instead you should run a query that will get the most up to
date info.

For example, in an Employee table you have two fields: EmpDateOfBirth and
Age. In this case the EmpAge field is redundent and should not be in the
table as you can figure out the Employee's age by calculationg it from the
DateOfBirth when needed.

If that sounds like a lot of extra work consider:

1. Having derived data takes up space in the tables.

2. Derived data tends to result in inconsistant data. For example I have an
Employee table that includes both the EmpDateOfBirth and EmpAge. After a year
or so, all the data in the Age field is incorrect.

3. Increase workload. If you change the EmpDateOfBirth data, then you need
to also change the EmpAge data.

 

[Guest]

I am not storing any derived data. A paper can have several versions, and the
completion date for each version needs to be tracked. So RevisionLevel 1 for
paper A could be in status "Complete" with completion date today and then I
could add RevisionLevel2 with status "In Work." There could be 10 revisions
of each paper, and each revision needs a due date and date of completion.

The problem is, I want a screen for a person to see all paper they are in
charge of, but only the latest version of each paper. (Revisions do not roll
at the same time) So doing a max query to return the max revision seemed
logical to me--I'm not sure I understand what you're saying.

 

[Guest]

Let me also add that I want the user to see a screen of the latest revision
of all papers they are in charge of but they can update the status of any one
of these revisions. Currently they can see a list of the latest revisions but
they have to open the form for each individual paper to update the status of
a given revision.