Difficult DMax expression?

[Leslie Isaacs]

Hello All

I have three tables, called [staffs] and [x confirmed] and [practices].

Each record in [staffs] represents a person, and each belongs to a
'practice' (so the field 'practice' in [staffs] is related to the field
'prac_name' in table [practices]. Table [x confirmed] contains lots of
records for each person, and contains the field [staff_name] (related to the
corresponding field in [staffs]) and also a field called [month_name], and
also a date/time field called 'entered'.

I need to show (in a text box on a form) the highest value of the field
'entered' in table [x confirmed] where:
the field [month_name] in table [x confirmed] =
[forms]![frm_x_main]![monthname]
but also ...
I need to further restrict this criteria by only considering those people in
[x confirmed] who belong to the Practice whose name is displayed in
[forms]![frm_x_main]![practicename].

I'm not sure that I can use DMax for this, because I don't see how I can
specify the further criteria for the field Practice, as this field is not in
the table [x confirmed] - which is the table in which I am looking up the
data.

I how I have explained this adequately, and that someone can help.

Many thanks
Leslie Isaacs

 

[Michel Walsh]

Hi,


You can use DMax on a saved query, which, in turns, take into account
the join between the two tables, and the many criteria.

Hoping it may help,
Vanderghast, Access MVP

 

[Jack Isaacs]

Thanks Michel - I get it now!
Les

Hi,


You can use DMax on a saved query, which, in turns, take into account
the join between the two tables, and the many criteria.

Hoping it may help,
Vanderghast, Access MVP

Hello All

I have three tables, called [staffs] and [x confirmed] and [practices].

Each record in [staffs] represents a person, and each belongs to a
'practice' (so the field 'practice' in [staffs] is related to the field
'prac_name' in table [practices]. Table [x confirmed] contains lots of
records for each person, and contains the field [staff_name] (related to
the
corresponding field in [staffs]) and also a field called [month_name], and
also a date/time field called 'entered'.

I need to show (in a text box on a form) the highest value of the field
'entered' in table [x confirmed] where:
the field [month_name] in table [x confirmed] =
[forms]![frm_x_main]![monthname]
but also ...
I need to further restrict this criteria by only considering those people
in
[x confirmed] who belong to the Practice whose name is displayed in
[forms]![frm_x_main]![practicename].

I'm not sure that I can use DMax for this, because I don't see how I can
specify the further criteria for the field Practice, as this field is not
in
the table [x confirmed] - which is the table in which I am looking up the
data.

I how I have explained this adequately, and that someone can help.

Many thanks
Leslie Isaacs