Revision Field

[Karen L]

I am creating a database which requires a unique number to be assigned to
each record. Called Schedule Number.
However, records are revised periodically and the same record has to be
shown with again with minor adjustements and the same Schedule Number. Called
Revision Number.
I want access to assign each Schedule Number in order automatically, but
still allow the same Schedule number to be used with different revision
numbers.
Shechule 1 (automated number) revision 0
Schedule 1 Revision 1
Can anyone help

 

[Ken Sheridan]

It sounds to me like it would be best to decompose the table into two tables,
Schedule and Revisions say, related on the Schedule Number. The Schedule
table would retain any columns which cannot be changed in a revision (this
could be just the Schedule Number of course) and the Revisions table would
have the columns which can be changed along with the foreign key Schedule
Number and Revision Number. These two columns in combination would be the
primary key of the Revisions table.

For the Schedule Number you could use an autonumber, but that's designed to
guarantee uniqueness rather than sequence, so I'd advise against it, but that
the number be computed when a new record is inserted via a form. You'll find
a demo of how this can be done at:

http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23839&webtag=ws-msdevapps

For data entry the normal setup for this sort of thing would be a main
Schedule form with a Revisions subform embedded within it, linked on the
Schedule Number fields. For reports you would join both tables in a query.
If you wanted just the latest revision to each Schedule record you'd restrict
the result set by means of a subquery like so:

SELECT Schedule.[Schedule Number], <more columns>
FROM Schedule INNER JOIN Revisions AS R1
ON Schedule.[Schedule Number] = R1.Schedule Number]
WHERE [Revision Number] =
(SELECT MAX([Revison Number])
FROM Revisions AS R2
WHERE R2.[Schedule Number] = R1.Schedule Number]);

Ken Sheridan
Stafford, England

 

[Karen L]

Ken,

Thanks for your prompt reply. I will try your suggestions and let you know
how I get on. Many thanks