Using Inner Join With MIN function

[AccessHelp123]

Hi I am trying to use Inner join combined with a min function to update a table, for some reason it does not work. The query is posted below, any help is appreciated. Thanks.

UPDATE tableFinalResult SET tableFinalResult.[FIRST CUT DATE IN CLUSTER] = MIN([tableNortel].[cut]) INNER JOIN tableNortel ON tableFinalResult.CLUSTER = tableNortel.Cluster;

 

[alvechurchdata]

I can't see what you're trying to do with this SQL. Could you explain what you need to achieve?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[AccessHelp123]

I have a table "tableFinalResult" which has a field called cluster(has distinct values). The same field exists in the table "tableNortel"(similar "cluster" value exists in several rows in this table). Now I need to need to update the field "FIRST CUT DATE IN CLUSTER" in tableFinalResult with the minimum value in the field "cut" in tableNortel for every cluster that is in tableFinalresult. Please let me know if you have any further question. Thanks.

 

[alvechurchdata]

I can't think how to do this in a query. All I can suggest is to scan through tableFinalResult record by record, use DMin() to find the minimum cut in tableNortel which matches that cluster and then update the field in tableFinalResult.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[AccessHelp123]

Yeah.. I was just trying to see if we can somehow do it through a query. thanks for you help.

 

[PHV]

UPDATE tableFinalResult SET [FIRST CUT DATE IN CLUSTER] = DMin("cut", "tableNortel", "Cluster='" & [CLUSTER & "'")

If Cluster is defined as numeric, get rid of the single quotes.

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