If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Multiple conditions in two separate fields
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 -- An enthusiastic Amateur |
#2
|
|||
|
|||
Multiple conditions in two separate fields
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 wrote: 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 |
#3
|
|||
|
|||
Multiple conditions in two separate fields
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 "John Spencer" wrote: 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 wrote: 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 |
Thread Tools | |
Display Modes | |
|
|