Dmax criteria in Query

[vljones]

I have the following in Db:-

tbl_Est - Num_ClaimNo
- Date_Date
- Num_Estimate

I need this query to link to data in another table - tbl_Bord05, in which there is also a field called Num_Estimate and Num_ClaimNo.

I need to query tbl_Est to return the highest Date_Date for each Num_ClaimNo appearing in tbl_Est, where Num_ClaimNo also appears in tbl_Bord05.

I have had it working insomuch as the query (which I have now deleted) was returning the Num_ClaimNo, Datae_Date and Num_Estimate but only for one record - the one with the highest date!

Any assistance would be greatly appreciated. My limited success so far has come using the DMax function in the query criteria.

Happy to get any SQL that will do the trick.

Cheers in advance.
VJ

[puppydogbuddy]

Try this and see if it helps:

Select tbl_Est.[Num_ClaimNo], tbl_Est.[Date_Date], tbl_Est.[Num_Estimate], Max("tbl_Est.[Date_Date]") as MaxClaimDate, tbl_Bord05.[Num_ClaimNo]
From tbl_Est, tbl_Bord05
Where tbl_Est.[Num_ClaimNo] = tbl_Bord05.[Num_ClaimNo] And tbl_Est.[Date_Date] =  Max("tbl_Est.[Date_Date]") 
Group By tbl_Est.[Num_ClaimNo] 
Order By tbl_Est.[Num_ClaimNo]

[puppydogbuddy]

If the above code does not get it try it this way:

Select tbl_Est.[Num_ClaimNo], tbl_Est.[Date_Date], tbl_Est.[Num_Estimate], Max("tbl_Est.[Date_Date]") as MaxClaimDate, tbl_Bord05.[Num_ClaimNo]
From tbl_Est, tbl_Bord05
Where tbl_Est.[Num_ClaimNo] = tbl_Bord05.[Num_ClaimNo] 
Group By tbl_Est.[Num_ClaimNo] 
Having tbl_Est.[Date_Date] =  Max("tbl_Est.[Date_Date]")
Order By tbl_Est.[Num_ClaimNo]

[vljones]

Try this and see if it helps:

Select tbl_Est.[Num_ClaimNo], tbl_Est.[Date_Date], tbl_Est.[Num_Estimate], Max("tbl_Est.[Date_Date]") as MaxClaimDate, tbl_Bord05.[Num_ClaimNo]
From tbl_Est, tbl_Bord05
Where tbl_Est.[Num_ClaimNo] = tbl_Bord05.[Num_ClaimNo] And tbl_Est.[Date_Date] =  Max("tbl_Est.[Date_Date]") 
Group By tbl_Est.[Num_ClaimNo] 
Order By tbl_Est.[Num_ClaimNo]

Unfortunately that did not work. Cannot have aggregate function in WHERE clause <clause>. (Error 3096) is displayed.

To make it a little clearer, tbl_Bord05 is a query made table which populates some fields with a zero value. These fields are the are then to be updated with the query I am trying to build here.

tbl_Estimate stores multiple records per Num_ClaimNo.

I need to retrieve from tbl_Est, the data in fields Num_ClaimNo, Date_Date and Num_Estimate, but only where Num_ClaimNo in tbl_Est also exists in tbl_Bord05. If this is true then Date_date from tbl_Estimate needs to be the highest date for each Num_ClaimNo appearing in tbl_Estimate, as well as the corresponding Num_estimate value.

Once I have the select working properly I will then be updating these fields in tbl_Bord05 from the data retrieved from this query.

Sorry SQL is not my strongest point, am getting better but it still confuses me sometimes.

Thanks so much (again) for your help.

VJ

[vljones]

If the above code does not get it try it this way:

Select tbl_Est.[Num_ClaimNo], tbl_Est.[Date_Date], tbl_Est.[Num_Estimate], Max("tbl_Est.[Date_Date]") as MaxClaimDate, tbl_Bord05.[Num_ClaimNo]
From tbl_Est, tbl_Bord05
Where tbl_Est.[Num_ClaimNo] = tbl_Bord05.[Num_ClaimNo] 
Group By tbl_Est.[Num_ClaimNo] 
Having tbl_Est.[Date_Date] =  Max("tbl_Est.[Date_Date]")
Order By tbl_Est.[Num_ClaimNo]

Unfortunately this did not work either, producing the following error.

You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)

VJ

[vljones]

Unfortunately this did not work either, producing the following error.

You tried to execute a query that does not include the specified expression <name> as part of an aggregate function. (Error 3122)

VJ

Have figured it out another way that works better, but thanks anyway for your help.

VJ

[puppydogbuddy]

Have figured it out another way that works better, but thanks anyway for your help.

VJ

VJ,
It would be helpful to others if you would post your solution. You would get the above mentioned errors if nulls were encountered in the date or claim no fields. So maybe if the above syntax provided for nulls, it may have worked.