Date

[Michael Hanlon]

I Have a form that show all the courses the company as sent its emp on.
what i am trying to do is find the last course we sent them out on so emp
9456 went on 5 course
10 nov 98 to 15 dec 98
10 aug 03 to 20 sep 03
21 sep 01 to 01 apr 02
24 may 03 to 10 aug 03
08 jan 98 to 12 jul 98

so how can i set up this to find the last course the selected user was on.
Which in this case would be the date 20 sep 03.

the form setup is Course name, Course location, Fromdate, Todate
form name is empcourse

any help would be great thanks in advance

 

[Allen Browne]

See:
Queries: Getting a related field from a GroupBy (total) query
at:
http://www.mvps.org/access/queries/qry0020.htm
for 4 ways to do this.

 

[Michael Hanlon]

ok allen i tryed to understand this but not getting it.

what i need to do is from my course query under the To field read all the
date for that one user and place the latest date in a text box on the form
and on a report.


thanks for your help

 

[John Vinson]

ok allen i tryed to understand this but not getting it.

what i need to do is from my course query under the To field read all the
date for that one user and place the latest date in a text box on the form
and on a report.

Change the query to a Totals query. Group by the User and use Max on
the "Totals" line for the date field.

Or, use

=DMax("[To]", "[tablename]", "[User] = " & [User])

as the Control Source for the textbox on the form or report.

 

[Michael Hanlon]

Thank you john i tryed the one on the form txt box control source as

=dmax("[to]", "[cname]","[empid]="&[empid])

and all i get is #error

any help would be great thanks

ok allen i tryed to understand this but not getting it.

what i need to do is from my course query under the To field read all the
date for that one user and place the latest date in a text box on the form
and on a report.

Change the query to a Totals query. Group by the User and use Max on
the "Totals" line for the date field.

Or, use

=DMax("[To]", "[tablename]", "[User] = " & [User])

as the Control Source for the textbox on the form or report.