Create Temporary Tables in a SQL Statement?

[WAstarita]

I have a payroll application that requires the end users to know if all
neccessary time sheets have been entered.

Basically, there is an employee table. In the employee table there are
7 (Day) fields (Monday, Tuesday, Wednsday, Thursday, ect)

We pay bi-weekly so I have to created a query against the employee
record to generate a table of dates within the 2 week time span.

This table is then checked against the time sheets table per employee
to see what days timesheets were not entered for that employee.

Right now, I have to update a table I created just to house the valid
dates in ADO then insert the query into another query through ado to
return the recordset. I would like to just be able to created on SQL
query that does all of it.

Is there any syntax to lookup information in an inline format. For
example

Select DateWorked From TimeSheets, (Select VaildDates: 10/7/2006,
10/8/2008, 10/9/2009, ect), join Vaild Dates on TimeSheets.DateWorked,
.......

I know this is kind of vague but without any concept of where to start
I cannot show any demo code.

Thanks in advance.

 

[Van T. Dinh]

The structure you described is more suitable for a spreadsheet but certainly
not effcient for a database. You have repeating groups (7 "day" Fields)
which violates the First Normal Form of Relational Database Design Theory.

See the Microsoft Knowledge Base article:

http://support.microsoft.com/kb/234208

 

[WAstarita]

I would disagree, the days are attributes of the employee and are
distinct values Monday, Tuesday, ecetera. To branch that out into a
seperate table would be time consuming, too much overhead and
problematic as you would have to setup constraints both database and
code to make sure the same day is not inserted twice for the same
employee.

I simply want to check against a user generated value set and return
the values not found in the comparison table.

 

[Douglas J. Steele]

Disagree if you like, but Van is correct. Your table as it exists is
denormalized, and will cause you all sorts of problems.

 

[David F Cox]

[DEVIL]

Of course you are right, your table is perfectly normalised, each person has
only one Tuesday, so you can put that in your record. Your problem is that
it is not normalised enough. Each person can only have one Tuesday 24 th
October 2006 so that should be in the record too, along with every other
Tuesday. Life would be so much simpler, wouldn't it?

[/DEVIL]

 

[WAstarita]

I'm all for better ideas, how would i normalize this table structure

EmpID
EmpName
Sunday
Monday
Tuesday
Wednesday
Thursday
Friday
Saturday
* Days are all Boolean Values

The result is I want to be able to compare a dates' weekday verses
whether or not an employee is supposed to work on that week day.

 

[David F Cox]

TblDataDay
IdDataForDay
EmpID Foreign Key
WDate
Field1WithDataForThatDate



SELECT tblDataDay.EmpId, tblDataDay.WDate,
tblDataDay.Field1WithDataForThatDate
FROM tblDataDay
WHERE WDate IN( #10/7/2006#,#10/8/2008#,#10/9/2009#, .......

There would almost certainly be a join with the Employee table to link the
information from there.
You would probably use a Format function, or WEEKDAY(WDate) to extract the
day of the week.

 

[WAstarita]

Here is the tricky part, I want to compare the week days they are
supposed to work against a list of entered time sheets and return those
dates that do NOT exist. Using a Where IN **** would not work even if
I put a NOT infront if it because since the dates do not exist, they
cannot be evaluated. Which is why I need a way to dynamically create a
table of 14 dates in the sql code to compare against.

 

[dbahooker]

I disagree..

these 'normal forms' that they refer to are obsolete concepts.

use the database as you see fit.

any use of a database is inherently more efficient than a spreadsheet

 

[iliace]

Do you have overlapping times? Are you using a job/process costing
system? If so, I would recommend having a separate table/class for
TimeItem. You will want to have some kind of a validation technique
for period (in a bi-weekly payroll it's easiest to use start date of
the time period to identify it). Also, you'll want a validation table
for employee, and possibly clients, programs, cost centers, etc.

You can use a variety of query techniques to determine total payable
hours and their distributions. To get all data for a particular
employee (in this example, consultant), this is fairly simple to do:

strQuery = "SELECT * FROM tblTimeData " & _
"WHERE tblTimeData.TimeDataPeriod=#" &
Me.cmbSelectTimePeriod.Column(2) & _
"# AND tblTimeData.TimeDataConsultant=" & _
mdlUserTools.getCurrentUserId & " AND NOT
tblTimeData.TimeDataCertified"

Another example for selecting distinct hours worked (in our example,
there are "group" sessions applied to multiple clients) thus
eliminating program or client codes will allow you to sum the hours:

strQuery = "SELECT DISTINCT tblTimeData.TimeDataDate,
tblTimeData.TimeStart, " & _
"tblTimeData.TimeEnd, tblTimeData.TimeDataHours " & _
"FROM tblTimeData " & _
"WHERE (tblTimeData.TimeDataConsultant=" &
mdlUserTools.getCurrentUserId & _
") AND (tblTimeData.TimeDataDate BETWEEN #" & _
Me.cmbSelectTimePeriod.Column(2) & "# AND #" & _
Me.cmbSelectTimePeriod.Column(3) & "#)"

Each daily end-user distribution (meaning, for program director's
approval) can be calculated using this query (joining details from
validation tables):

Me("cmbDay" & count1 + 1 & "Info").RowSource = _
"SELECT tblTimeData.TimeDataId, tblTimeData.TimeStart, " &
_
"tblTimeData.TimeEnd, " & _
"tblClients.ClientFirstName+'
'+tblClients.ClientLastName, " & _
"tblTimeData.TimeDataProgram,
tblTimeData.TimeDataDescription " & _
"FROM tblTimeData " & _
"INNER JOIN tblClients ON tblClients.ClientId =
tblTimeData.TimeDataClient " & _
"WHERE tblTimeData.TimeDataConsultant=" & _
mdlUserTools.getCurrentUserId() & _
" AND tblTimeData.TimeDataDate=#" & dtCurrentDay & "# "
& _
"ORDER BY tblTimeData.TimeStart"

Each daily detail contains a combobox (and other row elements) tagged
with a cmbDayXX, where XX is anywhere from 1 to 14 for a two-week
period.

Two more queries that allow you to distribute the time data for the
finance department - the trick here is to tag group sessions at time of
data entry:

strNonGroupQuery = "SELECT * FROM tblTimeData " & _
"WHERE tblTimeData.TimeDataConsultant=" & _

mdlUserTools.getCurrentUserId & _
" AND tblTimeData.TimeDataPeriod=#" &
Me.txtFromDate.Value & "#" & _
" AND tblTimeData.TimeDataGroupSession=False"

strGroupQuery = "SELECT DISTINCT tblTimeData.TimeDataDate,
tblTimeData.TimeStart, " & _
"tblTimeData.TimeDataHours FROM tblTimeData " & _
"WHERE tblTimeData.TimeDataGroupSession" & _
" AND tblTimeData.TimeDataConsultant=" & _

mdlUserTools.getCurrentUserId & _
" AND tblTimeData.TimeDataPeriod=#" &
Me.txtFromDate.Value & "#"

Some ideas - hope it's helpful.

-Ilia