Selecting rows 2

[DajOne]

Our IT department sends me a data dump of our operating system for reporting purpose and the data needs cleaning up.

I queried the fields I needed for my reports and made another table.

Fields are : application_no, reporting_one, Unit, Process_Status, Decision, Sales_rep, Lease_No and Credit_date.

Each application_no may have several records with different data in each field thus grouping is difficult.

I need to select records where Credit_date is 'last' (last date) per Appplication_no with all fields on that row... I tried grouping and querying several times but when I try to get all fields of the same row, I get duplicates.

I hope this is clear enough for you to guide me with the correct idea or function...

Thanks in advance

 

[lespaul]

Try this:

SELECT * FROM TABLE T1 WHERE CREDITDATE = (SELECT MAX(CREDITDATE) FROM TABLE T2 WHERE T1.APPLICATION_NO = T2.APPLICATION_NO)

Leslie

 

[PHV]

You may try something like this:
SELECT A.application_no, A.reporting_one, A.Unit, A.Process_Status, A.Decision, A.Sales_rep, A.Lease_No, A.Credit_date
FROM theTable A
INNER JOIN
(SELECT B.application_no, Max(B.Credit_date) AS maxDate
FROM theTable B GROUP BY B.application_no) C
ON A.application_no=C.application_no AND ON A.Credit_date=C.maxDate;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DajOne]

Thanks for the solutions I am greatfull> I learned something new so it is a good day.

As I did most of the data validating/tansformation/ exchange rate changes before this step, is is possible to do this from one table only? I mean that all my celean data is now in one table. The solutions refer to 2 tables.

Thanks

 

[lespaul]

No, I'm only using one table, but I'm are referencing it twice.

SELECT * FROM TABLE T1 WHERE CREDITDATE = (SELECT MAX(CREDITDATE) FROM TABLE T2 WHERE T1.APPLICATION_NO = T2.APPLICATION_NO)

the blue query finds the max credit date in your table where the application number is the same as the application number in the red query. The red query returns each record for each application number where the credit date equals the max credit date found for that application number in the blue query.

For you to get this query to work, you should just change TABLE to your tablename.

HTH


Leslie

 

[DajOne]

Leslie,

Thanks for the replies...

The query (below) returns all 'rows' with a credit date of 9/9/2003 which is 60 +... It should return 13,000+...

I changed MAX for LAST to no avail.

SELECT * FROM EB_Query_Two WHERE [CREDIT DATE] = (SELECT MAX([CREDIT DATE]) FROM EB_Query_Two WHERE [EB_Query_Two.EB_App_No] = [EB_Query_Two.EB_App_no])

Where did I err? I tried several combinations from the 'help' in Access with no success.

 

[PHV]

DajOne, just to know, have you tried my suggestion ?

 

[lespaul]

You didn't include the alias names. Paste this into your query:

SELECT * FROM EB_Query_Two E2A WHERE E2A.[CREDIT DATE] = (SELECT MAX(E2B.[CREDIT DATE]) FROM EB_Query_Two E2B WHERE E2A.[EB_App_No] = E2B.[EB_App_no])

PHV's query should work as well. He's doing something a little different. Here's his with your names:

SELECT *
  FROM EB_Query_Two A
  INNER JOIN
(SELECT B.[EB_App_No], Max(B.[CREDIT DATE]) AS maxDate
   FROM EB_Query_Two B GROUP BY B.[EB_App_No]) C
  ON A.[EB_App_No]=C.[EB_App_No] AND ON A.[CREDIT DATE]=C.maxDate;

try those. Depending on how many records are in the table, you may see a noticable difference (PHV's should run faster) But you may need Access2000 in order for that to work.

HTH

Leslie

 

[DajOne]

PHV,

Yes I was trying your solution but did nto understand the logic behind thus was doing some research..

This is not my profession, sorry if I dont try or reply as soon as you provide your help...I will let you know as soon as I get results...

Thanks again for your help.. Your knowledge and kindness is greatly appreciated...

 

[PHV]

did not understand the logic behind
[tt]SELECT *
FROM EB_Query_Two A
INNER JOIN
(SELECT B.EB_App_No, Max(B.[CREDIT DATE]) AS maxDate
FROM EB_Query_Two B GROUP BY B.EB_App_No) C
ON A.EB_App_No=C.EB_App_No AND ON A.[CREDIT DATE]=C.maxDate; [/tt]
Consider a separate query (named C) returning the last credit date (maxDate) per application no (EB_App_No):
this is the select statement inside parens.

Now, to get all the infos of an application no related to its last date, you join the two tables/queries on exact match against this 2 fields (App_no and date):
Select * From EB_Query_Two INNER JOIN C ON ...

The select * is not mine, as it will return the app_no and date twice.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DajOne]

I tried this formula as Leslie suggested...

The query returned several rows where EB_App_No were the same. Most of the fields included in the EB_Query_Two table have different data per row... As an example, the query returned 4 rows of the same EB_App_no with a field called Lease_no equal to 790-3117030-001, 790-3089870-001, 790-3151900-001, 790-3152030-001. There are other fields that have also different data.

SELECT * FROM EB_Query_Two E2A WHERE E2A.[CREDIT DATE] = (SELECT MAX(E2B.[CREDIT DATE]) FROM EB_Query_Two E2B WHERE E2A.[EB_App_No] = E2B.[EB_App_no])

The PHV solution gave me an error (missing operator)... I am not familiar with aliases.

SELECT *
FROM EB_Query_Two A
INNER JOIN
(SELECT B.[EB_App_No], Max(B.[CREDIT DATE]) AS maxDate
FROM EB_Query_Two B GROUP BY B.[EB_App_No]) C
ON A.[EB_App_No]=C.[EB_App_No] AND ON A.[CREDIT DATE]=C.maxDate;

I will try removing fields that are not essential...Maybe this will help..

Thanks

 

[lespaul]

From what you said I would guess that you have a SINGLE application number (1000) with a max credit date of 6/15/2004 and four different lease numbers. If that's correct (that the four different lease number records all have the same date), then you need more criteria. Which lease number do you want?

Leslie

 

[PHV]

I guess you're with ac97.
So save the grouping query (C) and do the join in an another query.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DajOne]

Upon review of the data presented with the query, the multiple lease numbers will help me greatly... I can link the lease field to the main data for additional information..

I want to thank you very much... it works very well!!!Now I gotta learn it!!