Hello,
I am trying to help get a query to work in Access 2003.
There is one table, Engineering Document Register.
In it are various columns, but only three are needed for this query.
Doc No (Document Number) XMTL Ref (Transmittal reference number) and
Issued.

The database will have the same Doc No used several times, and the
XMTL Ref number is used for more than one document. But each document
will only be listed in XMTL Ref only once.
Issued is the date that the XMTL Ref went out, so the date is not
unique.

What I need to do is make a query that will pull the latest date that
the document was transmitted.
Can this be done?

Thanks,
Rodjk #613

Rodjk #613 wrote:

Query/Design/New, add table Engineering Document Register, then press Close.

Drag down from table to a column the field DocNumber. Drag Issued to
another column. Click from the menu View/Totals. Under Issued column
select Max. Run the query.

On Jan 11, 6:56*pm, Salad <o... (AT) vinegar (DOT) com> wrote:
Thanks for the reply.
When I try this, it brings up duplicate Doc No entries.

What I am trying to do is set this so that when someone clicks on the
query, they get a pop up window asking for a Doc No. They put in the
number and get the most recent XMTL number and date.

Thanks again, any and all help is appreciated.

Rodjk #613

On Jan 11, 6:53*pm, "Rodjk #613" <rjka... (AT) gmail (DOT) com> wrote:
So any one combination of XMLT and DocNo will be unique, but
that's irrelevant because all you really want is the latest
IssuedDate
for a DocNo.

SELECT dr.DocNo, dr.XMTLRef, dr.DateIssued
FROM DocRegister AS dr
WHERE dr.DateIssued In
(SELECT Max(DateIssued) From DocRegister
WHERE DocNo = dr.DocNo)
AND dr.DocNo = ?

On Jan 12, 9:07*am, rkc <r... (AT) rkcny (DOT) com> wrote:
or...

SELECT Top 1 DocNo, XMTLRef, DateIssued
FROM DocRegister
WHERE DocNo = ?
Order By DateIssued Desc