Challenging queries query...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Or not as the case may be.

My db holds on-going weekly working hours records for each employee.

I would like to set up a query that will tell me which employees are missing
a particular date (the unmatched query only seems to list those employees
that have NO corresponding weekly records at all).

cheers
 
PARAMETERS [Enter Date] DateTime;
SELECT Employees.EMPID, Employees.HireDate
FROM Employees
WHERE (((Employees.EMPID) Not In (SELECT Employees.EMPID
FROM Employees
WHERE Employees.HireDate=[Enter Date])));

Something like the above will work IF (1) your date field is a date/time
datatype and (2) it only includes the Date portion and no time (except
midnight). It will take a little more work if you have something like
6/28/2006 11:30AM stored.
 
Thanks for the reply.

My weekID is a date/time type without the time portion.

Will give it a whirl tomorrow

Jerry Whittle said:
PARAMETERS [Enter Date] DateTime;
SELECT Employees.EMPID, Employees.HireDate
FROM Employees
WHERE (((Employees.EMPID) Not In (SELECT Employees.EMPID
FROM Employees
WHERE Employees.HireDate=[Enter Date])));

Something like the above will work IF (1) your date field is a date/time
datatype and (2) it only includes the Date portion and no time (except
midnight). It will take a little more work if you have something like
6/28/2006 11:30AM stored.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


scubadiver said:
Or not as the case may be.

My db holds on-going weekly working hours records for each employee.

I would like to set up a query that will tell me which employees are missing
a particular date (the unmatched query only seems to list those employees
that have NO corresponding weekly records at all).

cheers
Click to expand...
Click to expand...
 
Sorry,

I re-read my message and it had a complete lack of clarity.

The two tables have a 1-to-many relationship so each employeeID has many
weekIDs.

My logic tells me that it isn't possible to list the employees who have a
certain date missing.

Jerry Whittle said:
PARAMETERS [Enter Date] DateTime;
SELECT Employees.EMPID, Employees.HireDate
FROM Employees
WHERE (((Employees.EMPID) Not In (SELECT Employees.EMPID
FROM Employees
WHERE Employees.HireDate=[Enter Date])));

Something like the above will work IF (1) your date field is a date/time
datatype and (2) it only includes the Date portion and no time (except
midnight). It will take a little more work if you have something like
6/28/2006 11:30AM stored.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


scubadiver said:
Or not as the case may be.

My db holds on-going weekly working hours records for each employee.

I would like to set up a query that will tell me which employees are missing
a particular date (the unmatched query only seems to list those employees
that have NO corresponding weekly records at all).

cheers
Click to expand...
Click to expand...
 
scubadiver said:
The two tables have a 1-to-many relationship so each employeeID has many
weekIDs.

My logic tells me that it isn't possible to list the employees who have a
certain date missing.
Click to expand...

Create a calendar table (i.e. a permanent auxilary table) containing
dates for all possible 'working' days, weekIDs, etc then use this in
your 'unmatched' query.

Jamie.

--
 
You can do this is two queries.
--First query gets everyone that has a record for the date (save query)
--Second query is an unmatched query that uses the first query and the employees table.

Or you can try the following query


SELECT E.EmpID
FROM Employees as E
LEFT JOIN
(SELECT W.EmpID
FROM WorkHours as W
WHERE W.WorkDate = CDate([Insert Date])) as W2
Sorry,

I re-read my message and it had a complete lack of clarity.

The two tables have a 1-to-many relationship so each employeeID has many
weekIDs.

My logic tells me that it isn't possible to list the employees who have a
certain date missing.

Jerry Whittle said:
PARAMETERS [Enter Date] DateTime;
SELECT Employees.EMPID, Employees.HireDate
FROM Employees
WHERE (((Employees.EMPID) Not In (SELECT Employees.EMPID
FROM Employees
WHERE Employees.HireDate=[Enter Date])));

Something like the above will work IF (1) your date field is a date/time
datatype and (2) it only includes the Date portion and no time (except
midnight). It will take a little more work if you have something like
6/28/2006 11:30AM stored.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


scubadiver said:
Or not as the case may be.

My db holds on-going weekly working hours records for each employee.

I would like to set up a query that will tell me which employees are missing
a particular date (the unmatched query only seems to list those employees
that have NO corresponding weekly records at all).

cheers
Click to expand...
Click to expand...
Click to expand...
 
So how would this work then?

Is it just a table with one 'date' field? How would this work in a query?

Cheers

:
 
scubadiver said:
Is it just a table with one 'date' field? How would this work in a query?
Click to expand...

That's correct. it could work in a query like this:

Sub CalendarTest()
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\DropMe.mdb"
With .ActiveConnection

' Create Calendar table for June 2006
.Execute _
"CREATE TABLE Calendar (dt DATETIME NOT" & _
" NULL PRIMARY KEY)"
.Execute _
"INSERT INTO Calendar (dt)" & _
" VALUES (#2006-06-01#);"
.Execute _
"INSERT INTO Calendar (dt)" & _
"SELECT DATESERIAL(2006, 6, Units.nbr + Tens.nbr)" & _
" AS dt FROM (SELECT nbr FROM ( SELECT 0" & _
" AS nbr FROM Calendar UNION ALL SELECT 1" & _
" FROM Calendar UNION ALL SELECT 2 FROM Calendar" & _
" UNION ALL SELECT 3 FROM Calendar UNION" & _
" ALL SELECT 4 FROM Calendar UNION ALL SELECT" & _
" 5 FROM Calendar UNION ALL SELECT 6 FROM" & _
" Calendar UNION ALL SELECT 7 FROM Calendar" & _
" UNION ALL SELECT 8 FROM Calendar UNION" & _
" ALL SELECT 9 FROM Calendar ) AS Digits" & _
" ) AS Units, ( SELECT nbr * 10 AS nbr FROM" & _
" ( SELECT 0 AS nbr FROM Calendar UNION ALL" & _
" SELECT 1 FROM Calendar UNION ALL SELECT" & _
" 2 FROM Calendar UNION ALL SELECT 3 FROM" & _
" Calendar UNION ALL SELECT 4 FROM Calendar" & _
" UNION ALL SELECT 5 FROM Calendar UNION" & _
" ALL SELECT 6 FROM Calendar UNION ALL SELECT" & _
" 7 FROM Calendar UNION ALL SELECT 8 FROM" & _
" Calendar UNION ALL SELECT 9 FROM Calendar" & _
" ) AS Digits) AS Tens WHERE Units.nbr +" & _
" Tens.nbr BETWEEN 2 AND 30 "

' Create test table
.Execute _
"CREATE TABLE Test (date_col DATETIME" & _
" NOT NULL);"

' Create test data rows
.Execute _
"INSERT INTO Test (date_col) VALUES" & _
" (#2006-06-05#);"
.Execute _
"INSERT INTO Test (date_col) VALUES" & _
" (#2006-06-10#);"
.Execute _
"INSERT INTO Test (date_col) VALUES" & _
" (#2006-06-15#);"
.Execute _
"INSERT INTO Test (date_col) VALUES" & _
" (#2006-06-20#);"
.Execute _
"INSERT INTO Test (date_col) VALUES" & _
" (#2006-06-25#);"
.Execute _
"INSERT INTO Test (date_col) VALUES" & _
" (#2006-06-30#);"

' Create VIEW ('Query')
.Execute _
"CREATE VIEW TestMissingDates AS SELECT dt" & _
" FROM Calendar AS C1 LEFT JOIN Test AS T1" & _
" ON C1.dt = T1.date_col WHERE T1.date_col" & _
" IS NULL;"

' Show dates that are not present
' in the test table
Dim rs
Set rs = .Execute( _
"SELECT dt FROM TestMissingDates;")
MsgBox rs.GetString
rs.Close

End With
Set .ActiveConnection = Nothing
End With
End Sub

Jamie.

--
 
Back
Top