Document Transmittal

  • Thread starter Thread starter Greg McLandsborough
  • Start date Start date
G

Greg McLandsborough

I have a table [Document Revision] which has a Foriegn key to [Document
Register]. I am trying to sort for the latest Revision_ID by date as noted
below works fine.

SELECT [Document Revision].Document_ID, Max([Document
Revision].Revision_Date) AS MaxOfRevision_Date
FROM [Document Register] INNER JOIN [Document Revision] ON [Document
Register].Document_ID = [Document Revision].Document_ID
GROUP BY [Document Revision].Document_ID;

However if I add [Document Revision].Revision_ID to the statement, the query
returns all revision for a particular Document. How Do I return the last
Revision_ID by latest date.

Appreciate any help

Regards

Greg
 
Need some answers first:

Can there be more than one [Document Revision].Revision_ID on a date for the
same Document?

Does the [Document Revision].Revision_ID always increase for the same
Document or can the numbers be scattered around?

Is the [Document Revision].Revision_ID an Autonumber by chance? That would
answer the second question with a 99.9% of the time reply which is probably
not good enough.
 
Jerry

No there wont be more than one Revision_ID for a given date per document. We
revise the documents and then Issue them, this happens say weekly.

The Revision_ID is an Autonumber (soon to be replicated)

Does this help?

Jerry Whittle said:
Need some answers first:

Can there be more than one [Document Revision].Revision_ID on a date for
the
same Document?

Does the [Document Revision].Revision_ID always increase for the same
Document or can the numbers be scattered around?

Is the [Document Revision].Revision_ID an Autonumber by chance? That would
answer the second question with a 99.9% of the time reply which is
probably
not good enough.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Greg McLandsborough said:
I have a table [Document Revision] which has a Foriegn key to [Document
Register]. I am trying to sort for the latest Revision_ID by date as
noted
below works fine.

SELECT [Document Revision].Document_ID, Max([Document
Revision].Revision_Date) AS MaxOfRevision_Date
FROM [Document Register] INNER JOIN [Document Revision] ON [Document
Register].Document_ID = [Document Revision].Document_ID
GROUP BY [Document Revision].Document_ID;

However if I add [Document Revision].Revision_ID to the statement, the
query
returns all revision for a particular Document. How Do I return the last
Revision_ID by latest date.

Appreciate any help

Regards

Greg
Click to expand...
Click to expand...
 
Greg,

Assuming the Revision_ID is an increasing number, does this do it?...

SELECT [Document Revision].Document_ID, Max([Document
Revision].Revision_Date) AS MaxOfRevision_Date, Max([Document
Revision].Revision_ID) AS MaxOfRevision_ID
FROM [Document Revision]
GROUP BY [Document Revision].Document_ID

If so, how is this different from what you had already tried? If not,
can you explain a bit more about what you want?
 
I'm Probably going to confuse things now.

The Revision_ID is an Autonumber, which if Replicated will become random.

I also have a Revision_Letter, which ultimately is what I require for my
[Document Transmittal]. The Revision_Letter increments from "A" to "B" and
so on, which indicate Documents issued for Approval, thence "0" to "1" and
so on for Documents issued for Constuction. Unfortunately sorting the
Revision_ID returns "0", "1", "A", "B" and on, not "A", "B", "0", "1".

To further add to the confusion, the data entered into the [Document
Revision] is done by a set of functions in Autocad.

So can I stop a replicated Database from assigning Autonumber Randomly or
should add another Feild which counts the Revision per Document ?

Any hints welcome

Cheers

Greg


Steve Schapel said:
Greg,

Assuming the Revision_ID is an increasing number, does this do it?...

SELECT [Document Revision].Document_ID, Max([Document
Revision].Revision_Date) AS MaxOfRevision_Date, Max([Document
Revision].Revision_ID) AS MaxOfRevision_ID
FROM [Document Revision]
GROUP BY [Document Revision].Document_ID

If so, how is this different from what you had already tried? If not, can
you explain a bit more about what you want?

--
Steve Schapel, Microsoft Access MVP


Greg said:
I have a table [Document Revision] which has a Foriegn key to [Document
Register]. I am trying to sort for the latest Revision_ID by date as
noted below works fine.

SELECT [Document Revision].Document_ID, Max([Document
Revision].Revision_Date) AS MaxOfRevision_Date
FROM [Document Register] INNER JOIN [Document Revision] ON [Document
Register].Document_ID = [Document Revision].Document_ID
GROUP BY [Document Revision].Document_ID;

However if I add [Document Revision].Revision_ID to the statement, the
query returns all revision for a particular Document. How Do I return the
last Revision_ID by latest date.

Appreciate any help

Regards

Greg
Click to expand...
Click to expand...
 
Greg,

No, a replicated database's Autonumber fields will always be Random.

You can do it in a 2-step process. First make a query that gives the
most recent Revision_Date for each Document_ID...
SELECT [Document Revision].Document_ID, Max([Document
Revision].Revision_Date) AS LatestRevision
FROM [Document Revision]
GROUP BY [Document Revision].Document_ID
Save and name this query.
Then make a second query, which joins this first query to your Document
Revsion table on both the Document_ID and the Revision_Date fields.
This will give you the most recent Revision_ID, even if it is random.
 
Back
Top