Move Range of dates to another field 1

[MICKI0220]

I really wasn't sure how to title this in the Subject field, but here is the problem

I have a report that I use the information that is populated in a work table...I do many different calculation to populate fields in this table....The fields I need to populate are Date1DT through Date7DT....These fields reference 7 different days in a weeks range. I first do a query that gets all the information of Regular Time, Overtime, DoubleTime. Say my date range is 9/4/2011 to 9/10/2011. Each date record has many fields populated with information, such as how many hours were worked that day, hence, Regular, overtime or double time....I need the dates to correlate to 9/4/2011 to Date1 and 9/5/2011 to date2 etc. Each week the days will change but What I need to link is putting The time for that day into the correct sequence. Date1OT = the first dates Overtime field... Date2OT = the second date of that weeks overtime field etc. I Hope I explained it well enough.

Thank you for any help

Micki

 

[dhookom]

Micki,
Could you just provide actual table and field names with data types and relationships?

It would also help if you provided some sample raw data and desired output.

Also, what is the justification for not working with normalized tables? Date1DT, Date2OT, etc is basically a spreadsheet, not a relational database.

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

I'm not sure what you are asking for with your question about justification.

We have a certified report that we use. When we switched to this new software, the certified report doesn't give us what we need. It uses SQL databases so I have a link to the dbo_EmpPayDetailHist table. in this table I query on the week I need to provide detailed work hours on. I query on the field "dtWork_dt and give the criteria of Between the dates I input.

It gives me the following fields I need. employeeID, dtwork_dt, szEarnCode_tr, and dHoursWorked_qty.

employeeID dtwork_dt szEarnCode_tr dHoursWorked_qty

20250 9/4/2011 REG 8
20250 9/4/2011 OVT 2
20250 9/10/2011 DBL 8

employeeID = number(double)
dtwork_dt= date
szEarnCode_tr= text (6)
dHoursWorked_qty= number(double)

I need to update the table Certified
fields Date1REG to Date7REG where szearnCode_tr has dHoursWorked_qty for corresponding Date1 for the corresponding employeeID.

The link between the qry and Certified would be EmployeeID and

Date1-Date7 are text right now because I take the initial table from what the software gives me....they just don't provide Double time information...They use some hardcoded procedure (that I can't see to transpose the dtwork_dt into date1(sun), date2(mon) etc.
Date1REG- Date7REG = Number double
Date1OVT - Date7OVT = Number double
DateDBL - DateDBL = Number double

 

[dhookom]

Assuming you have a text box on a form for entering the week starting date:
[tt][Forms]![frmRptCritDate]![txtWeekStart][/tt]
I would create a crosstab query based on this date and EmpPayDetailHist. This should return one record per employee with 7 sets of REG, OVT, and DBL fields.

You could append this to a blank table with the same fields and EmployeeID as the primary key. Then use and update query to update Certified.

PARAMETERS [Forms]![frmRptCritDate]![txtWeekStart] DateTime;
TRANSFORM Sum(dbo_EmpPayDetailHist.dHoursWorked_qty) AS SumOfdHoursWorked_qty
SELECT dbo_EmpPayDetailHist.EmployeeID
FROM dbo_EmpPayDetailHist
GROUP BY dbo_EmpPayDetailHist.EmployeeID
PIVOT "Date" & DateDiff("d",[Forms]![frmRptCritDate]![txtWeekStart],[dtWork_dt]+1) & [szEarnCode_tr] In ("Date1REG","Date1OVT","Date1DBL","Date2REG","Date2OVT","Date2DBL","Date3REG","Date3OVT","Date3DBL","Date4REG","Date4OVT","Date4DBL","Date5REG","Date5OVT","Date5DBL","Date6REG","Date6OVT","Date6DBL","Date7REG","Date7OVT","Date7DBL");

[tt]
[blue]
EmployeeID Date1REG Date1OVT Date1DBL Date2REG ... Date7DBL
========== ======== ======== ======== ======== ========
20250 8 2 8
[/blue]
[/tt]

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

Thanks, I will give it a try and let you know.

 

[MICKI0220]

My syntax for getting the dates is a Between Criteria on the dtwork_dt.....Between [Forms]![frmCertified]![txtBegin] And [Forms]![frmCertified]![txtEnd]

How do I code that in the parameters...I get an error if I replace what you have with that code?

 

[dhookom]

Don't you always work with a single week (7 days)? If so, you only need txtBegin. You would replace my form/control references with yours.

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

Oh, okay. I'll try it again with a different form control.

 

[MICKI0220]

I corrected the syntax and all the fields appeared as they should but it did not put any of the data in the appropriate field. for example. I did get date1Reg, but did not get any value for the Reg department....the field with the hours is dhoursworked_qty. Any thoughts.

 

[dhookom]

Any thoughts" yes, provide your SQL view of the query. It would also help if you provided the value entered into the text box as well as some sample data.

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

This is the SQL

PARAMETERS [Forms]![frmCertified]![txtBegin] DateTime;
TRANSFORM Sum(dbo_EmpPayDetailHist.dHoursWorked_qty) AS SumOfdHoursWorked_qty
SELECT dbo_EmpPayDetailHist.szEmployeeId_tr AS Expr1
FROM dbo_EmpPayDetailHist
GROUP BY dbo_EmpPayDetailHist.szEmployeeId_tr
PIVOT "Date" & DateDiff("d",[Forms]![frmCertified]![txtBegin],[dtWork_dt]+1) & [szEarnCode_tr] In ("Date1REG","Date1OVT","Date1DBL","Date2REG","Date2OVT","Date2DBL","Date3REG","Date3OVT","Date3DBL","Date4REG","Date4OVT","Date4DBL","Date5REG","Date5OVT","Date5DBL","Date6REG","Date6OVT","Date6DBL","Date7REG","Date7OVT","Date7DBL");

The value entered into [Forms]![frmCertified]![txtBegin] was 9/4/2011

My original table looks like this

dtworked _dt szEmployeeID_tr szEarnCode_tr dHoursWorked_qty

9/4/2011 20250 REG 8
9/5/2011 20250 REG 8
9/6/2011 20250 REG 8
9/7/2011 20250 REG 8
9/8/2011 20250 REG 8
9/8/2011 20250 OVT 2
9/9/2011 20250 DBL 8

The Earning code (szEarnCode_tr) dictates whether it is REGULAR,OVERTIME OR DOUBLE TIME hours and the dHoursWorked_qty is how many hours worked of that code assigned to the record....As you can see they could have more than one earning code that day based on when overtime kicks in.....The code you gave me does give me Date1Rt, date1ot, date1db based on the date I just need to know how to get the right quantity hours worked into those dates. Thanks again for your help.

 

[dhookom]

I duplicated your data and form/control name and got this as a sample of the columns.
[tt]
szEmployeeID_tr Date1REG Date1OVT Date1DBL Date2REG Date2OVT Date2DBL Date3REG Date3OVT Date3DBL
20250 8 8 8
[/tt]

What do you see with this SQL

TRANSFORM Sum(dbo_EmpPayDetailHist.dHoursWorked_qty) AS SumOfdHoursWorked_qty
SELECT dbo_EmpPayDetailHist.szEmployeeID_tr
FROM dbo_EmpPayDetailHist
WHERE (((dbo_EmpPayDetailHist.dtWork_dt) Between #9/4/2011# And #9/9/2011#))
GROUP BY dbo_EmpPayDetailHist.szEmployeeID_tr
PIVOT "Date" & DateDiff("d",#9/4/2011#,dtWork_dt +1) & [szEarnCode_tr];

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

I need to throw an other request with this. I realized that a person can work on more than one job a week so can we break it down to be grouped by szemployee_id and Ijob_id. I seem to have hours in the columns now but because they could have worked on more than one job it is hard for me to verify.....Thanks again for all that you have already shown me.

 

[dhookom]

What happens if you drag Ijob_id to the grid and set it as Row Heading (like szEmployeeID_tr) in the crosstab query?

Duane
Hook'D on Access
MS Access MVP

 

[MICKI0220]

I think I figured it out how to add the Job Number. I will have my users verify the numbers and let you know if it worked....Thanks alot

 

[MICKI0220]

dhookom

In your query you use a static begin date :

PIVOT "Date" & DateDiff("d",#9/4/2011#,dtWork_dt +1) & [szEarnCode_tr];

Is there a way to get that date from a forms text box. For example the begin dates text box name is.....frmCertified!txtbegin

I am trying to automate this as much as possible and right now I have to manually change this query each week. I'd like it to pull that date fromt he form like everything else does.

Thanks again.

 

[dhookom]

Didn't you already use a reference to the text box on the form in a previous query? It should work the same. I had only asked you to use a static date for testing.

Duane
Hook'D on Access
MS Access MVP