Hello,
Having a problem running a query. My initial problem is that in the licensing database that I created last year when printing a license it is not bringing up the last master driver and issuing officer on the license. To be more clear I will put the tables and structures below along with the SQL for the license query. I am trying to use a top 1 of group query from an article http://support.microsoft.com/kb/153747 (method two) which was suggested on utteraccess.com and this is when I get the run-time error when I try to run the query.
TblEmpInfo:
EmpBadgeNum Txt (PK)
EmpLastName Txt
EmpFirstName Txt
EmpMidInital Txt
EmpDOB Date/Time-Medium
EmpMyID Txt
EmpActive Yes/No
EmpCompany Txt
EmpSubcontractor Yes/No
EmpDivision Txt
EmpSectionNum Number Double-General
TblEmpLicInfo:
EmpBadgeNum Txt (PK)
EmpAACDate Date/Time-Medium
EmpStateLicExpDate Date/Time-Medium
EmpStateLicClass Txt
EmpClassAReq Yes/No
EmpBPEndorsmentReq Yes/No
EmpNGLicIssDate Date/Time-Medium
EmpMedExamDate Date/Time-Medium
IOBadgeNum Number (FK from TblIssueOfficialInfo) (this isthe clerk inputting info)
MasterDriver Yes/No
TblEmpTraining:
ID Autonumber (PK)
EmpBadgeNum Txt (FK from TblEmpInfo)
EquipmentType Number (FK from TblEquip)
Forklift Yes/No
EmpTrainBypass Yes/No
TrainHrsCompleted Number
TrainDateComplete Date/Time-Medium
MDBadgeNum Number (FK from TblMasterDriver)
TrainArchive Yes/No
TrainArchiveDate Date/Time-Medium
TblEquip:
ID Autonumber (PK)
InputDate Date/Time-Medium
EquipmentType Txt
TrainingHoursReq Number
Remarks Txt
Archive Yes/No
ArchiveDate Date/Time-Medium
TblIssueOfficialInfo:
ID Autonumber (PK)
IOBadgeNum Txt
IOFirstName Txt
IOLastName Txt
IOMidInital Txt
IODOB Date/Time-Medium
IOMyID Txt
IOActive Yes/No
IOCompany Txt
IOSubcontractor Yes/No
IODivision Txt
IOSectionNumb Number
TblMasterDriver:
ID Autonumber (PK)
MDBadgeNum Txt
MDFirstName Txt
MDLastName Txt
MDMidInital Txt
MDDOB Date/Time-Medium
MDMyID Txt
MDActive Yes/No
MDCompany Txt
MDSubcontractor Yes/No
MDDivision Txt
MDSectionNumb Number
QryOperatorsIDCard SQL:
SELECT TblEmpInfo.EmpBadgeNum, TblEmpInfo.EmpLastName,TblEmpInfo.EmpFirstName, [EmpLastName] & ", " &[EmpFirstName] AS EmpNameCombined, TblEmpInfo.EmpMidInital, TblEmpInfo.EmpDOB,TblEmpInfo.EmpMyID, TblEmpLicInfo.EmpAACDate, QryNGLicExpDate.EmpAACExpDate,TblEmpLicInfo.EmpNGL icIssDate, QryNGLicExpDate.NGLicExpDate,TblEmpLicInfo.EmpStat eLicExpDate, TblEmpLicInfo.EmpStateLicClass,TblEmpLicInfo.EmpCl assAReq, TblEmpLicInfo.EmpBPEndorsmentReq,TblIssueOfficialI nfo.IOBadgeNum, TblIssueOfficialInfo.IOFirstName,TblIssueOfficialI nfo.IOLastName, [IOLastName] & ", " &[IOFirstName] AS IONameCombined, TblMasterDriver.MDBadgeNum,TblMasterDriver.MDFirst Name, TblMasterDriver.MDLastName, [MDLastName] &", " & [MDFirstName] AS MDNameCombined,TblEmpTraining.EquipmentType, TblEmpTraining.TrainArchive,TblEmpInfo.EmpActive, TblEmpInfo.EmpSubcontractor, TblEmpInfo.EmpSectionNum,TblEmpLicInfo.MasterDrive r
FROM TblMasterDriver INNER JOIN (TblIssueOfficialInfo INNERJOIN (TblEquip INNER JOIN ((((TblEmpInfo INNER JOIN QryNGLicExpDate ONTblEmpInfo.EmpBadgeNum = QryNGLicExpDate.EmpBadgeNum) INNER JOINQryForkliftTrainExpDate ON TblEmpInfo.EmpBadgeNum =QryForkliftTrainExpDate.EmpBadgeNum) INNER JOIN TblEmpLicInfo ONTblEmpInfo.EmpBadgeNum = TblEmpLicInfo.EmpBadgeNum) INNER JOIN TblEmpTrainingON TblEmpInfo.EmpBadgeNum = TblEmpTraining.EmpBadgeNum) ON TblEquip.ID =TblEmpTraining.EquipmentType) ON TblIssueOfficialInfo.ID = TblEmpLicInfo.IOBadgeNum)ON TblMasterDriver.ID = TblEmpTraining.MDBadgeNum
GROUP BY TblEmpInfo.EmpBadgeNum, TblEmpInfo.EmpLastName,TblEmpInfo.EmpFirstName, [EmpLastName] & ", " &[EmpFirstName], TblEmpInfo.EmpMidInital, TblEmpInfo.EmpDOB, TblEmpInfo.EmpMyID,TblEmpLicInfo.EmpAACDate, QryNGLicExpDate.EmpAACExpDate,TblEmpLicInfo.EmpNGL icIssDate, QryNGLicExpDate.NGLicExpDate,TblEmpLicInfo.EmpStat eLicExpDate, TblEmpLicInfo.EmpStateLicClass,TblEmpLicInfo.EmpCl assAReq, TblEmpLicInfo.EmpBPEndorsmentReq,TblIssueOfficialI nfo.IOBadgeNum, TblIssueOfficialInfo.IOFirstName,TblIssueOfficialI nfo.IOLastName, [IOLastName] & ", " &[IOFirstName], TblMasterDriver.MDBadgeNum, TblMasterDriver.MDFirstName,TblMasterDriver.MDLast Name, [MDLastName] & ", " & [MDFirstName],TblEmpTraining.EquipmentType, TblEmpTraining.TrainArchive,TblEmpInfo.EmpActive, TblEmpInfo.EmpSubcontractor, TblEmpInfo.EmpSectionNum,TblEmpLicInfo.MasterDrive r
HAVING (((TblEmpTraining.TrainArchive) Like False) AND((TblEmpInfo.EmpActive) Like True))
ORDERBY [EmpLastName] & ", " & [EmpFirstName];
Here is the SQL for the query that will not run:
SELECT TblEmpInfo.EmpBadgeNum, TblEmpInfo.EmpLastName, TblEmpInfo.EmpFirstName, TblEmpTraining.TrainDateComplete, TblEmpTraining.MDBadgeNum
FROM TblEmpInfo INNER JOIN TblEmpTraining ON TblEmpInfo.EmpBadgeNum = TblEmpTraining.EmpBadgeNum
WHERE (((TblEmpTraining.TrainDateComplete)>=NthInGroup([TblEmpTraining].[MDBadgeNum],1)))
ORDER BY TblEmpInfo.EmpLastName, TblEmpTraining.TrainDateComplete DESC;
When I click debug it brings up the VBA:
Option Explicit
Function NthInGroup(GroupID, N)
' Returns the Nth Item in GroupID for use as a Top N per group
' query criteria.
Static LastGroupId, LastNthInGroup
Dim ItemName, GroupIDName, GDC, SearchTable
Dim SQL As String, rs As Recordset, db As Database
If (LastGroupId = GroupID) Then
' Returned saved result if function is called with the
' same GroupID more than once in a row.
NthInGroup = LastNthInGroup
Else
' Set the SQL statement parameters. These are the only items
' that need to be customized in this function.
' Set to Item field name.
ItemName = "TrainDateComplete"
' Set to Group ID field name.
GroupIDName = "MDBadgeNum"
' GroupID Delimiter Character:
' For Text use "'" (Note that this is a quotation mark, a space,
' an apostrophe, a space, and then a quotation mark. The spaces
' are necessary for SQL statements), Date "#", Numeric ""
GDC = "'"
' Set to search table.
SearchTable = "TblEmpTraining"
' Build a Top N SQL statement dynamically given N and
' GroupID as parameters. Note that the sort is by the
' item in descending order, in order to get the Top N
' largest items.
SQL = "Select Top " & N & " [" & ItemName & "] "
SQL = SQL & "From [" & SearchTable & "] "
SQL = SQL & "Where [" & GroupIDName & "]=" & GDC & GroupID & GDC _
& " ' "
SQL = SQL & "Order By [" & ItemName & "] Desc"
' Open up recordset on Top N SQL statement and read the
' last record to get the smallest item in the Top N.
Set db = CurrentDb()
Set rs = db.OpenRecordset(SQL)
If (rs.BOF And rs.EOF) Then
' No matches found, return a null.
LastNthInGroup = Null
LastGroupId = GroupID
NthInGroup = LastNthInGroup
Else
' Return the smallest Top N item in the group.
rs.MoveLast
LastNthInGroup = rs(ItemName)
LastGroupId = GroupID
NthInGroup = LastNthInGroup
End If
End If
End Function
(This is what I used from the microsoft link). (The bold line is where it takes me when I click debug).
I would appreciate any help with this.
Attaching relationship pdf
Relationships for Licensing Database.pdf