Multiple conditions in two separate fields

[Cameron]

I am creating a training database. One of the modules has three sub modules
all of which have to be done before the module is regarded as completed. We
record the dates the the sub modules are completed and wind up with data that
looks like this:-

StaffName ProgrameName FinishDate
Joan Smith Sub-Module 1 01/01/2007
Joan Smith Sub-Module 2 10/01/2007
Joan Smith Sub-Module 3 17/01/2007
Peter Jones Sub-Module 1 01/01/2007
Peter Jones Sub-Module 2 22/01/2007
Peter Jones Sub-Module 3

I am trying to write a query which will update the master table with the the
Total Module name and show the last of the three dates.

Is this doable or am i dreaming?

regards

 

[John Spencer]

The SQL for such a query MIGHT look like the following. All field and
table names are guesses. Your table structure is unclear. Are you
recording the sub-modules and modules in the same table or do you have a
separate table for the submodules?

How do you relate specific sub-modules to a specific module?

Insert into [MasterTable] (StaffName, ModuleName, FinishDate)
SELECT StaffName, "TotalModuleName", Max(FinishDate)
FROM [SomeOtherTable]
WHERE ProgrameName In ( "Sub-Module 1","Sub-Module 2","Sub-Module 3")
GROUP BY StaffName, "TotalModuleName"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Cameron]

John,

thanks for the response.

Over the weekend I had actually realsied that I had not really provided a
clear description of the problem. So here goes

Database structure - There are three tables

Employee - holds employee data
Emplyee_Programmes - holds employee names and details of their training
Employee_Induction - holds employee names and details of the induction sub
modules

I am trying to construct a query which will evaluate Employee_Induction and :-

a) identify whichj emplyees have completed ALL THREE modules and then
b) update Employee_Programmes record to show that the individual completed
the induction programem on the LAST of the three dates.

So in this example

StaffName ProgrameName FinishDate
Joan Smith Sub-Module 1 01/01/2007
Joan Smith Sub-Module 2 10/01/2007
Joan Smith Sub-Module 3 17/01/2007
Peter Jones Sub-Module 1 01/01/2007
Peter Jones Sub-Module 2 22/01/2007
Peter Jones Sub-Module 3


Joan Smith's record in the Employee_programmes tabel would be updated to
show that she had completed the induction on the last date 17/01/2007.

Peter Jones records on the other hand, would not be updated because he has
not completed all three modules.

Hope this makes my problem clearer.

Regards


Cameron





--
An enthusiastic Amateur

The SQL for such a query MIGHT look like the following. All field and
table names are guesses. Your table structure is unclear. Are you
recording the sub-modules and modules in the same table or do you have a
separate table for the submodules?

How do you relate specific sub-modules to a specific module?

Insert into [MasterTable] (StaffName, ModuleName, FinishDate)
SELECT StaffName, "TotalModuleName", Max(FinishDate)
FROM [SomeOtherTable]
WHERE ProgrameName In ( "Sub-Module 1","Sub-Module 2","Sub-Module 3")
GROUP BY StaffName, "TotalModuleName"

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

I am creating a training database. One of the modules has three sub modules
all of which have to be done before the module is regarded as completed. We
record the dates the the sub modules are completed and wind up with data that
looks like this:-

StaffName ProgrameName FinishDate
Joan Smith Sub-Module 1 01/01/2007
Joan Smith Sub-Module 2 10/01/2007
Joan Smith Sub-Module 3 17/01/2007
Peter Jones Sub-Module 1 01/01/2007
Peter Jones Sub-Module 2 22/01/2007
Peter Jones Sub-Module 3

I am trying to write a query which will update the master table with the the
Total Module name and show the last of the three dates.

Is this doable or am i dreaming?

regards