Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

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

Posted on 2011-09-21
12
748 Views
Last Modified: 2012-05-12
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

 Screen Shot Of Executed Query for a Single ALAN8
0
Comment
Question by:Tom Winslow
  • 4
  • 4
  • 2
  • +1
12 Comments
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 36574316
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.
0
 
LVL 31

Expert Comment

by:Helen_Feddema
ID: 36574323
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36574418
try this query

select * from F0116
where ALUPMJ in (select max([ALUPMJ]) from F0116 as F where F.ALAN8=F0116.ALAN8)
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 33

Expert Comment

by:Norie
ID: 36574435
Try this:

SELECT *
FROM F0116 AS A

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

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36574475
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


0
 

Author Comment

by:Tom Winslow
ID: 36575697
I have been requested to upload a portion of the table. Please see attached.
tblF0116Project.csv
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 36575757
Tom,

you uploaded an incomplete file..

where is column for ALUPMJ ?

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

Expert Comment

by:Norie
ID: 36575798
There is no date field at all and no field called ALUPMJ.
0
 

Author Comment

by:Tom Winslow
ID: 36575863
OOPS! Uploading another file.
tblF0116Project.csv
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 36575917
test this, run query1



DB-Q-27319964.mdb
0
 

Author Comment

by:Tom Winslow
ID: 36582511
I am experimenting with the answers and will get back as soon as possible.
0
 

Author Closing Comment

by:Tom Winslow
ID: 36817440
Thanks. I learned a lot from studying this query.

tw
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

790 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question