need help with a multi Select query to show one of two items

[DougP]

Need to get a list of employees that only have one timesheet complete.
We submit payroll every two weeks. So each employee has two timesheets, for each payroll period. I want to create a query that shows only those who submitted one or none yet.
Here is what I have so far.

SELECT [TimeSheet Details].[Employee Name], [TimeSheet Details].PeriodEndDate, [TimeSheet Details].WeekEndDate
FROM [TimeSheet Details]
GROUP BY [TimeSheet Details].[Employee Name], [TimeSheet Details].PeriodEndDate, [TimeSheet Details].WeekEndDate
HAVING ((([TimeSheet Details].PeriodEndDate)=[Forms]![frmCreateAdvantecUploadFile]![cboChoosePeriodEndDate]));

This is what it returns now:
Employee Name PeriodEndDate WeekEndDate
Fred 4/4/2008 3/28/2008
Fred 4/4/2008 4/4/2008
Sally 4/4/2008 3/28/2008
Susie 4/4/2008 3/28/2008
Susie 4/4/2008 4/4/2008

the modified query would show Sally as only having one.
Sally 4/4/2008 3/28/2008

I have a logins table with all employees to compare to if someone had no timesheets yet.
[Employee Name]
Fred
Sally
Susie
Matt
So Matt would show as not having any.
Any help would be appreciated



DougP

 

[Remou]

How about:

SELECT [TimeSheet Details].[Employee Name], [TimeSheet Details].PeriodEndDate, [TimeSheet Details].WeekEndDate
FROM [TimeSheet Details]
WHERE [Employee Name] IN 
 ( SELECT [Employee Name] 
   FROM [TimeSheet Details] As T 
   GROUP BY [Employee Name]
   HAVING Count(*)>1 )

 

[Remou]

Oops. Wrong way round:

SELECT [TimeSheet Details].[Employee Name], [TimeSheet Details].PeriodEndDate, [TimeSheet Details].WeekEndDate
FROM [TimeSheet Details]
WHERE [Employee Name] IN 
 ( SELECT [Employee Name] 
   FROM [TimeSheet Details] As T 
   GROUP BY [Employee Name]
   HAVING Count(*)=1 )

 

[DougP]

It returns nothing.
need to include the Period End Date somehow. I have to run this every two weeks and have all the PE Dates in another table as shown in my Query. I am choosing the PE date from a combo box
((([TimeSheet Details].PeriodEndDate)=[Forms]![frmCreateAdvantecUploadFile]![cboChoosePeriodEndDate]));


DougP

 

[lespaul]

what about this:

SELECT T.[Employee Name], T.PeriodEndDate, T.WeekEndDate
FROM [TimeSheet Details] As T
WHERE T.PeriodEndDate=[Forms]![frmCreateAdvantecUploadFile]![cboChoosePeriodEndDate]
GROUP BY T.[Employee Name], T.PeriodEndDate, T.WeekEndDate
HAVING count(*) < 2

if you need to know who hasn't turned on in at all too, then maybe:

SELECT T.[Employee Name], T.PeriodEndDate, T.WeekEndDate
FROM [TimeSheet Details] As T
LEFT JOIN [Employee Names] As E On E.[Employee Name] = T.[Employee Name]
WHERE T.PeriodEndDate=[Forms]![frmCreateAdvantecUploadFile]![cboChoosePeriodEndDate]
OR T.EmployeeName IS Null
GROUP BY T.[Employee Name], T.PeriodEndDate, T.WeekEndDate
HAVING count(*) < 2

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[DougP]

Getting closer lespaul,
But it returns just one record each time I choose a different date, as a test. There should be 4 records who did not do it last time.


DougP

 

[lespaul]

which query only returns one record? It may be that you need a UNION query in order to get both those that only had one and another to get those that have none.

Leslie

 

[DougP]

both

DougP

 

[lespaul]

ok maybe this:

SELECT T.[Employee Name], T.PeriodEndDate, T.WeekEndDate
FROM [TimeSheet Details] As T
WHERE T.PeriodEndDate=[Forms]![frmCreateAdvantecUploadFile]![cboChoosePeriodEndDate]
GROUP BY T.[Employee Name], T.PeriodEndDate, T.WeekEndDate
HAVING count(*) < 2
UNION
SELECT T.[Employee Name], T.PeriodEndDate, T.WeekEndDate
FROM [TimeSheet Details] As T
LEFT JOIN [Employee Names] As E On E.[Employee Name] = T.[Employee Name]
WHERE T.PeriodEndDate=[Forms]![frmCreateAdvantecUploadFile]![cboChoosePeriodEndDate]
AND T.EmployeeName IS Null

Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[PHV]

you may try this:

SELECT A.[Employee Name], A.PeriodEndDate, A.WeekEndDate
FROM [TimeSheet Details] AS A INNER JOIN (
SELECT [Employee Name], PeriodEndDate FROM [TimeSheet Details]
WHERE PeriodEndDate=[Forms]![frmCreateAdvantecUploadFile]![cboChoosePeriodEndDate]
GROUP BY [Employee Name], PeriodEndDate HAVING Count(*)<2
) AS B ON A.[Employee Name]=B.[Employee Name] AND A.PeriodEndDate=B.PeriodEndDate

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[DougP]

Thanks both of you.
Still only returns one, But...
I'm sure its something I am leaving out in my explanation to you.
I wrote this code to make it all work.

    Dim Conn2 As ADODB.Connection
    Dim Rs1, Rs2, Rs3 As ADODB.Recordset
    Dim SQLCode, SQLCode2, SQLCode3 As String
    Set Conn2 = CurrentProject.Connection
    Set Rs1 = New ADODB.Recordset
    Set Rs2 = New ADODB.Recordset
    Set Rs3 = New ADODB.Recordset
    
    SQLCode3 = "Delete From tmpDontHaveTimesheets"
    Rs2.Open SQLCode3, Conn2, adOpenStatic, adLockOptimistic
    
    Me.[tmpDontHaveTimesheets subform].Requery
    ' open the logins table and get each employee
    SQLCode = "Select * From Logins"
    Rs1.Open SQLCode, Conn2, adOpenStatic, adLockOptimistic
    TotRecs = Rs1.RecordCount
    For a = 1 To TotRecs
        ' check to see if each employee has 1 2 or no timesheets for the period
        SQLCode2 = "SELECT [Employee Name], PeriodEndDate, WeekEndDate " & _
                   "FROM [TimeSheet Details] " & _
                   "GROUP BY [Employee Name], PeriodEndDate, WeekEndDate " & _
                   "HAVING ((([Employee Name])='" & Rs1![Employee Name] & "')" & _
                   "AND ((PeriodEndDate)=#" & Me![cboChoosePeriodEndDate] & "#));"

        Rs2.Open SQLCode2, Conn2, adOpenStatic, adLockOptimistic
        Debug.Print Rs1![Employee Name]
        Select Case Rs2.RecordCount
            
            Case 0 
                'no records (no timesheets at all)
                SQLCode3 = "INSERT INTO tmpDontHaveTimesheets ( [Employee Name]) " & _
                           "SELECT '" & Rs1![Employee Name] & "' AS Name;"
                Rs3.Open SQLCode3, Conn2, adOpenStatic, adLockOptimistic
            Case 1 
                'only have one timesheet
                SQLCode3 = "INSERT INTO tmpDontHaveTimesheets ( [Employee Name], WeekEndDate ) " & _
                           "SELECT '" & Rs2![Employee Name] & "' AS Name, #" & Rs2!WeekEndDate & "# AS TheDate;"
                Rs3.Open SQLCode3, Conn2, adOpenStatic, adLockOptimistic
            Case 2
                ' do nothing got both sheets
                SQLCode3 = ""
        End Select

        Rs2.Close
        Rs1.MoveNext  'get the next login
        Me.[tmpDontHaveTimesheets subform].Requery
    Next
    
Alldone:

'  close it this way
    Set Rs1 = Nothing
    Set Rs2 = Nothing
    Set Rs3 = Nothing
    Set Conn2 = Nothing
    
    Me.[tmpDontHaveTimesheets subform].Requery

DougP