slow query

  • Thread starter Thread starter jayne
  • Start date Start date
J

jayne

I'm using MS Access as my front-end and MySQL as my back-
end. I have queries named qry1, qry2 and qry3 and two (2)
tables named tbl1 and tbl2. In qry1, I get the maximum of
a field in tbl1 then in qry2, I also get the maximum of a
field in tbl2 and qry3 is the combination of qry1 and
qry2 so series of queries have been made. When I open
each query individually... it opens fast but when it
comes to qry3... it opens very slow. Can u please tell me
on what can be the major problem here and what can be the
possible solution for this. thanks!!
 
Jayne,

You don't need three queries to do this; you can do it in one, or directly
in a form or report or wherever it's required, by using Access built-in
function DMax. The syntax is:

DMax("[FieldName]","TableName","Criteria...")
In your case you don't have any criteria, so it boils down to:
DMax("[FieldName]","TableName")

To do it in a query, open a new one in design view, don't add any tables or
queries, just go to the first line of the grid and type in the first column:

MaxOfWhatever1: DMax("[FieldName]","tbl1")

and then in the second column:

MaxOfWhatever1: DMax("[FieldName]","tbl2")

making sure you change FieldName to the actual name of the field.
MaxOfWhatever1/2 are just aliases, you can change it to whatever you like.

To do it in a form, use the expressions (without aliases) in the controls'
ControlSource property.

HTH,
Nikos
 
Back
Top