Pull latest date from Multiple entries

[MarieG]

Hello all.

I'm linking 2 tables, looking for the LATEST date that corresponds to one
record, but that record is listed everytime the record is updated.. EX:


Table "Activity Log"
Record Date
501 05/01/09
501 06/01/09
501 07/01/09


Currently, my query only pulls the earliest date... I need it to pull the
latest date.. any ideas?

SELECT [HOLD LIST SUMMARY QUERY1].Client, [HOLD LIST SUMMARY QUERY1].Count,
[HOLD LIST SUMMARY QUERY1].Patient, [From To List
Query].CurrentInsuranceCarrier, [HOLD LIST SUMMARY QUERY1].TicketNumber, ""
AS [Ticket To Work], [HOLD LIST SUMMARY QUERY1].DOS, [HOLD LIST SUMMARY
QUERY1].[Billing Visit Description], [From To List Query].LastFiledDate,
(Date()-[LastFiledDate]) AS [Days Since Last Filed], [HOLD LIST SUMMARY
QUERY1].vBalance, [HOLD LIST SUMMARY QUERY1].vInsBalance, [HOLD LIST SUMMARY
QUERY1].vPatBalance, IIf([Days Since Last Filed]<31,"<30",IIf([Days Since
Last Filed] Between 31 And 45,"31 - 46",IIf([Days Since Last Filed] Between
46 And 60,"46 - 60",IIf([Days Since Last Filed] Between 61 And 90,"61 -
90",IIf([Days Since Last Filed]>90,"Over 90 Days","NONE"))))) AS [Over Days
Since Last Filed Date], [From To List Query].Value1, [From To List
Query].Value2
FROM [From To List Query] INNER JOIN [HOLD LIST SUMMARY QUERY1] ON ([From To
List Query].TicketNumber = [HOLD LIST SUMMARY QUERY1].TicketNumber) AND
([From To List Query].Client = [HOLD LIST SUMMARY QUERY1].Client) AND ([From
To List Query].LastModified = [HOLD LIST SUMMARY QUERY1].LastModifiedDate)
ORDER BY [HOLD LIST SUMMARY QUERY1].Client, [HOLD LIST SUMMARY
QUERY1].Patient, IIf([Days Since Last Filed]<31,"<30",IIf([Days Since Last
Filed] Between 31 And 45,"31 - 46",IIf([Days Since Last Filed] Between 46 And
60,"46 - 60",IIf([Days Since Last Filed] Between 61 And 90,"61 -
90",IIf([Days Since Last Filed]>90,"Over 90 Days","NONE"))))) DESC;

 

[Jeff Boyce]

Marie

If this were mine, I'd back off from the complex query and start out simple.

I'd add the one table that has the dates in it, the field with the dates,
and click the Totals button. I'd change "GroupBy" to "Maximum". If there's
a field that hold some "grouping" ID, I'd include that and use "GroupBy" on
that field.

Once I had these (the maximum or latest date for each ID), I'd create
another query that joined that first query to whatever else I needed.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[MarieG]

That will work perfectly!!! K-I-S-S!! Thanks!! Thanks!! Thanks!!

Marie

If this were mine, I'd back off from the complex query and start out simple.

I'd add the one table that has the dates in it, the field with the dates,
and click the Totals button. I'd change "GroupBy" to "Maximum". If there's
a field that hold some "grouping" ID, I'd include that and use "GroupBy" on
that field.

Once I had these (the maximum or latest date for each ID), I'd create
another query that joined that first query to whatever else I needed.

JOPO (just one person's opinion)

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hello all.

I'm linking 2 tables, looking for the LATEST date that corresponds to one
record, but that record is listed everytime the record is updated.. EX:


Table "Activity Log"
Record Date
501 05/01/09
501 06/01/09
501 07/01/09


Currently, my query only pulls the earliest date... I need it to pull
the
latest date.. any ideas?

SELECT [HOLD LIST SUMMARY QUERY1].Client, [HOLD LIST SUMMARY
QUERY1].Count,
[HOLD LIST SUMMARY QUERY1].Patient, [From To List
Query].CurrentInsuranceCarrier, [HOLD LIST SUMMARY QUERY1].TicketNumber,
""
AS [Ticket To Work], [HOLD LIST SUMMARY QUERY1].DOS, [HOLD LIST SUMMARY
QUERY1].[Billing Visit Description], [From To List Query].LastFiledDate,
(Date()-[LastFiledDate]) AS [Days Since Last Filed], [HOLD LIST SUMMARY
QUERY1].vBalance, [HOLD LIST SUMMARY QUERY1].vInsBalance, [HOLD LIST
SUMMARY
QUERY1].vPatBalance, IIf([Days Since Last Filed]<31,"<30",IIf([Days Since
Last Filed] Between 31 And 45,"31 - 46",IIf([Days Since Last Filed]
Between
46 And 60,"46 - 60",IIf([Days Since Last Filed] Between 61 And 90,"61 -
90",IIf([Days Since Last Filed]>90,"Over 90 Days","NONE"))))) AS [Over
Days
Since Last Filed Date], [From To List Query].Value1, [From To List
Query].Value2
FROM [From To List Query] INNER JOIN [HOLD LIST SUMMARY QUERY1] ON ([From
To
List Query].TicketNumber = [HOLD LIST SUMMARY QUERY1].TicketNumber) AND
([From To List Query].Client = [HOLD LIST SUMMARY QUERY1].Client) AND
([From
To List Query].LastModified = [HOLD LIST SUMMARY QUERY1].LastModifiedDate)
ORDER BY [HOLD LIST SUMMARY QUERY1].Client, [HOLD LIST SUMMARY
QUERY1].Patient, IIf([Days Since Last Filed]<31,"<30",IIf([Days Since Last
Filed] Between 31 And 45,"31 - 46",IIf([Days Since Last Filed] Between 46
And
60,"46 - 60",IIf([Days Since Last Filed] Between 61 And 90,"61 -
90",IIf([Days Since Last Filed]>90,"Over 90 Days","NONE"))))) DESC;