recordsets help 3

[SMHSleepy]

Unfortunately, my skills with recordsets are little to none.  I should make it a point to learn because I'm finding they are very powerful tools.

That said, can anyone help me with my next (and last) step in this project?

I now have a select query with results which look like this (I've simplified it a bit but for all intents and purposes, works the same):

ID     Date         Code     Hours     Name
1      2011-07-07   D         7.5      John
1      2011-07-08   D         7.5      John
1      2011-07-09   N        11.25     John
2      2011-07-07   D         7.5      Jane
2      2011-07-08   N        11.25     Jane

I can make a form where I enter a date range and select an employee, then have the query select those records meeting the criteria.  However, I want to actually use a looped recordset to look at each record then copy to a temporary table.  The temporary table would have the same column headings but the code and hours may differ. If you could help me set up the looped recordset to do this, I can put in the conditions.  For example, it would look at the first line and see that the code is 'D' so nothing changes and it copies all fields the same.  However, when it sees the 'N' code, it splits it into two rows: e.g.

ID     Date         Code     Hours     Name
1      2011-07-09   N        11.25     John

gets split into:

ID     Date         Code     Hours     Name
1      2011-07-09   E        4.00      John
1      2011-07-10   O        7.25      John

Then I generate a report based on the temporary table and finally clear the temporary table.  Does this make sense?

 

[TheAceMan1]

Howdy SMHSleepy . . .

How is it (in the real world) that your able to suppose 11.25 hours translates to 4hrs on one day and 7.25hrs the next? I could split this up just about anyway I like ... the employee can in late and worked 2hr for [blue]2011-07-09[/blue] ... then worked 9.25hrs on [blue]2011-07-10[/blue] ... see my point!

Please explain ...

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[SMHSleepy]

Good point, but not applicable here. The number of hours is always the same. We don't have a punch clock. In the odd event that such a thing is necessary however, I will just manually change the hours in the report. In the past 15 years, there has never been a need to change the hours so I'm not worried.

 

[kjv1611]

I can make a form where I enter a date range and select an employee, then have the query select those records meeting the criteria. However, I want to actually use a looped recordset to look at each record then copy to a temporary table. The temporary table would have the same column headings but the code and hours may differ. If you could help me set up the looped recordset to do this, I can put in the conditions. For example, it would look at the first line and see that the code is 'D' so nothing changes and it copies all fields the same. However, when it sees the 'N' code, it splits it into two rows: e.g.

That sounds to me like you would be better off with a query - you just have to find the correct critieria. When you can use a query as opposed to a recordset, it'll run faster anyway.

WHY do you need the recordset?

 

[PHV]

A starting point (SQL code)

SELECT ID,[Date],Code,Hours,Name FROM yourTable WHERE Code='D'
UNION SELECT ID,[Date],'E',4.0,Name FROM yourTable WHERE Code='N'
UNION SELECT ID,[Date],'O',7.25,Name FROM yourTable WHERE Code='N'

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

 

[SMHSleepy]

Thank you all. You are absolutely right, I should be using union queries to get the desired result. I didn't realize you could "split" up data this way. I will work with this and let you know if I run into any road blocks. Thanks again!

 

[PHV]

OOps, forgot the 'next day' (O code)

SELECT ID,[Date],Code,Hours,Name FROM yourTable WHERE Code='D'
UNION SELECT ID,[Date],'E',4.0,Name FROM yourTable WHERE Code='N'
UNION SELECT ID,[Date]+1,'O',7.25,Name FROM yourTable WHERE Code='N'

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

 

[SMHSleepy]

No worries, I already picked up on that one. Thanks!

 

[SMHSleepy]

Okay, check it out. I was even able to code for the weekend shift premiums. If the night shift begins on a Saturday or Sunday, it gets paid a weekend premium and codes differently. Thanks again for pointing me in the right direction!

SELECT personID_fk,dtmDate,Shift,hours,explanation,FirstName,LastName FROM tblPayrollTemp WHERE Shift='D'
UNION SELECT personID_fk,dtmDate,'LOAS',hours,explanation,FirstName,LastName FROM tblPayrollTemp WHERE Shift='LD'
UNION SELECT personID_fk,dtmDate,'LOAS',hours,explanation,FirstName,LastName FROM tblPayrollTemp WHERE Shift='LN'
UNION SELECT personID_fk,dtmDate,'LOAS',hours,explanation,FirstName,LastName FROM tblPayrollTemp WHERE Shift='LO'
UNION SELECT personID_fk,dtmDate,'S',hours,explanation,FirstName,LastName FROM tblPayrollTemp WHERE Shift='SD'
UNION SELECT personID_fk,dtmDate,'S',hours,explanation,FirstName,LastName FROM tblPayrollTemp WHERE Shift='SN'
UNION SELECT personID_fk,dtmDate,'S',hours,explanation,FirstName,LastName FROM tblPayrollTemp WHERE Shift='SO'
UNION SELECT personID_fk,dtmDate,'V',hours,explanation,FirstName,LastName FROM tblPayrollTemp WHERE Shift='VD'
UNION SELECT personID_fk,dtmDate,'V',hours,explanation,FirstName,LastName FROM tblPayrollTemp WHERE Shift='VN'
UNION SELECT personID_fk,dtmDate,'V',hours,explanation,FirstName,LastName FROM tblPayrollTemp WHERE Shift='VO'
UNION SELECT personID_fk,dtmDate,'D',hours,explanation,FirstName,LastName FROM tblPayrollTemp WHERE Shift='X'
UNION SELECT personID_fk,dtmDate,'HNE',hours,'New Years Day',FirstName,LastName FROM tblPayrollTemp WHERE Shift='HNE'
UNION SELECT personID_fk,dtmDate,'HFA',hours,'Family Day',FirstName,LastName FROM tblPayrollTemp WHERE Shift='HFA'
UNION SELECT personID_fk,dtmDate,'HGO',hours,'Good Friday',FirstName,LastName FROM tblPayrollTemp WHERE Shift='HGO'
UNION SELECT personID_fk,dtmDate,'HVI',hours,'Victoria Day',FirstName,LastName FROM tblPayrollTemp WHERE Shift='HVI'
UNION SELECT personID_fk,dtmDate,'HCA',hours,'Canada Day',FirstName,LastName FROM tblPayrollTemp WHERE Shift='HCA'
UNION SELECT personID_fk,dtmDate,'HCI',hours,'Civic Day',FirstName,LastName FROM tblPayrollTemp WHERE Shift='HCI'
UNION SELECT personID_fk,dtmDate,'HLA',hours,'Labour Day',FirstName,LastName FROM tblPayrollTemp WHERE Shift='HLA'
UNION SELECT personID_fk,dtmDate,'HTH',hours,'Thanksgiving Day',FirstName,LastName FROM tblPayrollTemp WHERE Shift='HTH'
UNION SELECT personID_fk,dtmDate,'HCH',hours,'Christmas Day',FirstName,LastName FROM tblPayrollTemp WHERE Shift='HCH'
UNION SELECT personID_fk,dtmDate,'HBO',hours,'Boxing Day',FirstName,LastName FROM tblPayrollTemp WHERE Shift='HBO'
UNION SELECT personID_fk,dtmDate,'HJUN',hours,'June SMH Day',FirstName,LastName FROM tblPayrollTemp WHERE Shift='HJUN'
UNION SELECT personID_fk,dtmDate,'HNOV',hours,'Nov SMH Day',FirstName,LastName FROM tblPayrollTemp WHERE Shift='HNOV'

UNION SELECT personID_fk,dtmDate,'WPE',4.00,explanation,FirstName,LastName FROM tblPayrollTemp WHERE Shift='N' and Format(dtmDate, "dddd") = "Saturday"
UNION SELECT personID_fk,dtmDate,'WPE',4.00,explanation,FirstName,LastName FROM tblPayrollTemp WHERE Shift='N' and Format(dtmDate, "dddd") = "Sunday"
UNION SELECT personID_fk,dtmDate+1,'WPN',7.25,explanation,FirstName,LastName FROM tblPayrollTemp WHERE Shift='N' and Format(dtmDate, "dddd") = "Saturday"
UNION SELECT personID_fk,dtmDate+1,'WPN',7.25,explanation,FirstName,LastName FROM tblPayrollTemp WHERE Shift='N' and Format(dtmDate, "dddd") = "Sunday"
UNION SELECT personID_fk,dtmDate,'E',4.00,explanation,FirstName,LastName FROM tblPayrollTemp WHERE Shift='N' and Format(dtmDate, "dddd") <> "Saturday" and Format(dtmDate, "dddd") <> "Sunday"
UNION SELECT personID_fk,dtmDate+1,'N',7.25,explanation,FirstName,LastName FROM tblPayrollTemp WHERE  Shift='N' and Format(dtmDate, "dddd") <> "Saturday" and Format(dtmDate, "dddd") <> "Sunday";

 

[PHV]

You may simplify this:
UNION SELECT personID_fk,dtmDate,'LOAS',hours,explanation,FirstName,LastName FROM tblPayrollTemp WHERE Shift='LD'
UNION SELECT personID_fk,dtmDate,'LOAS',hours,explanation,FirstName,LastName FROM tblPayrollTemp WHERE Shift='LN'
UNION SELECT personID_fk,dtmDate,'LOAS',hours,explanation,FirstName,LastName FROM tblPayrollTemp WHERE Shift='LO'
with this:
UNION SELECT personID_fk,dtmDate,'LOAS',hours,explanation,FirstName,LastName FROM tblPayrollTemp WHERE Shift IN ('LD','LN','LO')

and so on for 'S' and 'V'

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

 

[SMHSleepy]

Cool! Thanks. A little less spaghetti is always a good idea.