asked on

I need to see only the latest record in an address book table.

I am working on a query to clean up our JD Edwards address book table. The ALAN8 field is the unique, numeric, Associate address number (primary key). The ALUPMJ field is a 6-digit, numeric, Julian date field.

The F0116 table can have multiple entries for each individual associate (ALAN8) based upon the last date something in the table was updated (ALUPMJ).

I only want to see a single record for each Address Number (ALAN8) based upon on the last updated date (ALUPMJ). I have been scratching my head trying different functions, such as MAX, but I continue to see multiple records listed for each employee number. How should I modify my query in order to see only the last modified record per associate number regardless of the number of modifications in the remaining fields?
_____________________________________________________________________________
      Table F0116
Field      Description      Type      Length
      ALAN8      Address Number      Numeric      8
      ALEFTB      Date - Beginning Effective      Date      6
      ALEFTF      Effective Date Existence (1/0)      Character      1
      ALADD1      Address Line 1      String      40
      ALADD2      Address Line 2      String      40
      ALADD3      Address Line 3      String      40
      ALADD4      Address Line 4      String      40
      ALADDZ      Postal Code      String      12
      ALCTY1      City            String      25
      ALCOUN      County            String      25
      ALADDS      State            String      3
      ALCTR      Country            String      3
_____________________________________________________________________________

SQL View of Query:
SELECT PRODDTA_F0116.ALAN8, Max(PRODDTA_F0116.ALUPMJ) AS MaxOfALUPMJ, PRODDTA_F0116.ALADD1, PRODDTA_F0116.ALADD2, PRODDTA_F0116.ALADD3, PRODDTA_F0116.ALADD4, PRODDTA_F0116.ALCTY1, PRODDTA_F0116.ALCOUN, PRODDTA_F0116.ALADDS, PRODDTA_F0116.ALADDZ, PRODDTA_F0116.ALCTR
FROM PRODDTA_F0116
GROUP BY PRODDTA_F0116.ALAN8, PRODDTA_F0116.ALADD1, PRODDTA_F0116.ALADD2, PRODDTA_F0116.ALADD3, PRODDTA_F0116.ALADD4, PRODDTA_F0116.ALCTY1, PRODDTA_F0116.ALCOUN, PRODDTA_F0116.ALADDS, PRODDTA_F0116.ALADDZ, PRODDTA_F0116.ALCTR
ORDER BY Max(PRODDTA_F0116.ALUPMJ) DESC;
_____________________________________________________________________________

I have included a screen shot ss an example using a single ALAN8 that has three (3) records. I only want to see the latest Julian date, 107109.

Thanks in advance for your assistance.

TW

Helen Feddema

Can you post the database, or a cut-down version of it? Most likely the problem is that (apart from the Date field) there is unique data in some records, thus preventing you from seeing just the record with the most recent date.

Helen Feddema

You might need to create a separate query to get the unique ID of the record with the most recent date, and then use the ID to filter the form or subform for just that record.

Rey Obrero (Capricorn1)

try this query

select * from F0116
where ALUPMJ in (select max([ALUPMJ]) from F0116 as F where F.ALAN8=F0116.ALAN8)

Norie

Try this:

SELECT *
FROM F0116 AS A

WHERE ALUPMJ = (SELECT MAX (ALUPMJ) FROM F0116 WHERE A.ALAN8 = ALAN8 GROUP BY ALAN8)

Rey Obrero (Capricorn1)

this shuld run faster

SELECT T.*
FROM
PRODDTA_F0116 AS T
INNER JOIN
(SELECT
Max(T2.[ALUPMJ]) as MaxALUPMJ, [ALAN8]
FROM
PRODDTA_F0116 T2
Group By T2.ALAN8
) T1
ON T.[ALUPMJ]=T1.MaxALUPMJ and T.ALAN8=T1.ALAN8

Tom Winslow

ASKER

I have been requested to upload a portion of the table. Please see attached.
tblF0116Project.csv

Rey Obrero (Capricorn1)

Tom,

you uploaded an incomplete file..

where is column for ALUPMJ ?

have you tried the query i posted at http:#a36574475 ?

Norie

There is no date field at all and no field called ALUPMJ.

Tom Winslow

ASKER

OOPS! Uploading another file.
tblF0116Project.csv

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)