Can anyone help me on this task please

mercmannick

mercmannick

Well-known Member
Joined
Jan 21, 2005
Messages
730
<CENTER><TABLE cellSpacing=0 align=center cellpadding=2px width=50% ><TR ><TD CLASS="AccTBMain" border-left=1px solid colSpan= 9 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccTBInner" align=Left ><A HREF="javascript: void(0)" *******="myFunc('153240383','RTsml : ','Select Query, Record Count : 47');" title="Select Query, Record Count : 47"><font color="White">RTsml : Select Query</font></A></TD><TD CLASS="AccTBInner" align=right >Access 2000</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccDataElem" colSpan= 9 ><B>SELECT</B> <BR> [Sql Man Plan].[Roll No]<BR>, [Sql Man Plan].[Customer]<BR>, [Sql Man Plan].[Man Targ]<BR>, [Sql Man Plan].[Week No] AS [Wk No]<BR>, [Sql Man Plan].[Grade] AS Mat<BR>, [Sql Man Plan].[CW Drg] AS CW<BR>, [Sql Man Plan].[Weight] AS [Fin Wght]<BR>, Sum([FN Routes].[hours]) AS [Est Hrs]
<BR><B>FROM</B> <BR> WC INNER JOIN ([Sql Man Plan] INNER JOIN [FN Routes] ON [Sql Man Plan].[File No]=[FN Routes].[file no]) ON [WC].[opref]=[FN Routes].[opref]
<BR><B>GROUP BY</B> <BR> [WC].[WC]<BR>, [Sql Man Plan].[Roll No]<BR>, [Sql Man Plan].[Customer]<BR>, [Sql Man Plan].[Man Targ]<BR>, [Sql Man Plan].[Week No]<BR>, [Sql Man Plan].[Grade]<BR>, [Sql Man Plan].[CW Drg]<BR>, [Sql Man Plan].[Weight]<BR>, [FN Routes].[opref]<BR>, [FN Routes].[description]<BR>, [FN Routes].[sort order]<BR>, [Sql Man Plan].[Status]
<BR><B>HAVING</B> <BR> (((WC.WC) Like "RT WR*") AND (([Sql Man Plan].[Week No])="26") AND (([Sql Man Plan].Status)<>"INSPECTED"))
<B>ORDER BY</B> <BR> [Sql Man Plan].[Roll No]<BR>, [Sql Man Plan].[Customer]<BR>, [FN Routes].[sort order];
</TD></TR><TR ><TD CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" *******="myFunc('153240383','Roll No : ','Text(255)');" title="Text(255)">Roll No</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" *******="myFunc('153240383','Customer : ','Text(255)');" title="Text(255)">Customer</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" *******="myFunc('153240383','Man Targ : ','Date/Time');" title="Date/Time">Man Targ</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" *******="myFunc('153240383','Wk No : ','Text(255)');" title="Text(255)">Wk No</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" *******="myFunc('153240383','Mat : ','Text(255)');" title="Text(255)">Mat</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" *******="myFunc('153240383','CW : ','Text(255)');" title="Text(255)">CW</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" *******="myFunc('153240383','Fin Wght : ','Double');" title="Double">Fin Wght</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" *******="myFunc('153240383','Est Hrs : ','Double');" title="Double">Est Hrs</A></TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><img src='AccArr.gif' width='7' height='7'/></TD><TD CLASS="AccDataElem" >101371 </TD><TD CLASS="AccDataElem" >Rautaruukki Oyj </TD><TD CLASS="AccDataElem" >15/06/2009</TD><TD CLASS="AccDataElem" >26 </TD><TD CLASS="AccDataElem" >RX9X</TD><TD CLASS="AccDataElem" >6405</TD><TD CLASS="AccDataElem" >11.4</TD><TD CLASS="AccDataElem" >1.5</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >101371 </TD><TD CLASS="AccDataElem" >Rautaruukki Oyj </TD><TD CLASS="AccDataElem" >15/06/2009</TD><TD CLASS="AccDataElem" >26 </TD><TD CLASS="AccDataElem" >RX9X</TD><TD CLASS="AccDataElem" >6405</TD><TD CLASS="AccDataElem" >11.4</TD><TD CLASS="AccDataElem" >1.5</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >101371 </TD><TD CLASS="AccDataElem" >Rautaruukki Oyj </TD><TD CLASS="AccDataElem" >15/06/2009</TD><TD CLASS="AccDataElem" >26 </TD><TD CLASS="AccDataElem" >RX9X</TD><TD CLASS="AccDataElem" >6405</TD><TD CLASS="AccDataElem" >11.4</TD><TD CLASS="AccDataElem" >11</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >101384 </TD><TD CLASS="AccDataElem" >Corus Port Talbot </TD><TD CLASS="AccDataElem" >15/06/2009</TD><TD CLASS="AccDataElem" >26 </TD><TD CLASS="AccDataElem" >RX9</TD><TD CLASS="AccDataElem" >6998</TD><TD CLASS="AccDataElem" >9.81</TD><TD CLASS="AccDataElem" >1.5</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >101384 </TD><TD CLASS="AccDataElem" >Corus Port Talbot </TD><TD CLASS="AccDataElem" >15/06/2009</TD><TD CLASS="AccDataElem" >26 </TD><TD CLASS="AccDataElem" >RX9</TD><TD CLASS="AccDataElem" >6998</TD><TD CLASS="AccDataElem" >9.81</TD><TD CLASS="AccDataElem" >1.5</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >101384 </TD><TD CLASS="AccDataElem" >Corus Port Talbot </TD><TD CLASS="AccDataElem" >15/06/2009</TD><TD CLASS="AccDataElem" >26 </TD><TD CLASS="AccDataElem" >RX9</TD><TD CLASS="AccDataElem" >6998</TD><TD CLASS="AccDataElem" >9.81</TD><TD CLASS="AccDataElem" >8</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >101405 </TD><TD CLASS="AccDataElem" >Corus Port Talbot </TD><TD CLASS="AccDataElem" >15/07/2009</TD><TD CLASS="AccDataElem" >26 </TD><TD CLASS="AccDataElem" >RX9</TD><TD CLASS="AccDataElem" >6253</TD><TD CLASS="AccDataElem" >8</TD><TD CLASS="AccDataElem" >1.5</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >101405 </TD><TD CLASS="AccDataElem" >Corus Port Talbot </TD><TD CLASS="AccDataElem" >15/07/2009</TD><TD CLASS="AccDataElem" >26 </TD><TD CLASS="AccDataElem" >RX9</TD><TD CLASS="AccDataElem" >6253</TD><TD CLASS="AccDataElem" >8</TD><TD CLASS="AccDataElem" >1.5</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >101405 </TD><TD CLASS="AccDataElem" >Corus Port Talbot </TD><TD CLASS="AccDataElem" >15/07/2009</TD><TD CLASS="AccDataElem" >26 </TD><TD CLASS="AccDataElem" >RX9</TD><TD CLASS="AccDataElem" >6253</TD><TD CLASS="AccDataElem" >8</TD><TD CLASS="AccDataElem" >7</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >101444 </TD><TD CLASS="AccDataElem" >Corus Trostre </TD><TD CLASS="AccDataElem" >15/06/2009</TD><TD CLASS="AccDataElem" >26 </TD><TD CLASS="AccDataElem" >18VC</TD><TD CLASS="AccDataElem" >6293</TD><TD CLASS="AccDataElem" >3.55</TD><TD CLASS="AccDataElem" >1.5</TD></TR><TR ><TD ID="IE" CLASS="AccIEOnlyMain" colSpan= 9 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccIEOnlyInnerRight" > Record: </TD><TD CLASS="AccButton" ><img src='AccNavL.jpg' width='40' height='15'/></TD><TD CLASS="AccIEOnlyData" > 1 </TD><TD CLASS="AccButton" ><img src='AccNavR.jpg' width='40' height='15'/></TD><TD CLASS="AccIEOnlyInnerLeft" > of 47</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccIEOnlyMain" colSpan= 9 ><TABLE cellSpacing=0 cellPadding=0 width="100%" align=left VALIGN="TOP" ><TR ><TD ID="Data153240383" CLASS="AccInfoBarInnerRight" >RTsml</TD><TD ID="Info153240383" CLASS="AccInfoBarData" >Select Query, Record Count : 47</TD></TR></TABLE></TD></TR><TR ><TD CLASS="tpkrow" colSpan = 9 ><CENTER><font size = 1>Access HTML Add-in provided by theprimarykey.com</font></CENTER></TD></TR></TABLE></CENTER>

Hi i have got a Query which pulls back data above, i would like to be able to add some columns after EST Hours which will be in 6hr blocks so 4 columns would equal a day, and go out a week starting 7am sat to 7am sat 28 columns in total, with these columns i would like to be able to highlight strat and finish times in these 28 columns ,(IE: if first job starts 7am Sat and runs for 48 hours it will highlight up until monday 7am , then next job would start at 7am + 1 hr for setuptime etc)

Any help or any ideas where to start on this would be gratefull

Thanks

Nick
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
<CENTER><TABLE cellSpacing=0 align=center cellpadding=2px width=50% ><TR ><TD CLASS="AccTBMain" border-left=1px solid colSpan= 11 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccTBInner" align=Left ><A HREF="javascript: void(0)" *******="myFunc('247977853','RTSmall : ','Select Query, Record Count : 47');" title="Select Query, Record Count : 47"><font color="White">RTSmall : Select Query</font></A></TD><TD CLASS="AccTBInner" align=right >Access 2000</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccDataElem" colSpan= 11 ><B>SELECT</B> <BR> [Sql Man Plan].[Roll No]<BR>, [Sql Man Plan].Customer<BR>, [Sql Man Plan].[Man Targ]<BR>, [Sql Man Plan].[Week No] AS [Wk No]<BR>, [Sql Man Plan].Grade AS Mat<BR>, [Sql Man Plan].[CW Drg] AS CW<BR>, [Sql Man Plan].Weight AS [Fin Wght]<BR>, Sum([FN Routes].hours) AS [Est Hrs]<BR>, CDate(Now()) AS [START TIME]<BR>, CDate(Now())+2 AS [END TIME]
<BR><B>FROM</B> <BR> WC INNER JOIN ([Sql Man Plan] INNER JOIN [FN Routes] ON [Sql Man Plan].[File No] = [FN Routes].[file no]) ON WC.opref = [FN Routes].opref
<BR><B>GROUP BY</B> <BR> [Sql Man Plan].[Roll No]<BR>, [Sql Man Plan].Customer<BR>, [Sql Man Plan].[Man Targ]<BR>, [Sql Man Plan].[Week No]<BR>, [Sql Man Plan].Grade<BR>, [Sql Man Plan].[CW Drg]<BR>, [Sql Man Plan].Weight<BR>, WC.WC<BR>, [FN Routes].opref<BR>, [FN Routes].[sort order]<BR>, [Sql Man Plan].Status<BR>, CDate(Now())<BR>, CDate(Now())+2
<BR><B>HAVING</B> <BR> ((([Sql Man Plan].[Week No])="26") AND ((WC.WC) Like "RT WR*") AND (([Sql Man Plan].Status)<>"INSPECTED"))
<B>ORDER BY</B> <BR> [Sql Man Plan].[Roll No]<BR>, [Sql Man Plan].Customer<BR>, [FN Routes].[sort order];
</TD></TR><TR ><TD CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" *******="myFunc('247977853','Roll No : ','Text(255)');" title="Text(255)">Roll No</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" *******="myFunc('247977853','Customer : ','Text(255)');" title="Text(255)">Customer</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" *******="myFunc('247977853','Man Targ : ','Date/Time');" title="Date/Time">Man Targ</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" *******="myFunc('247977853','Wk No : ','Text(255)');" title="Text(255)">Wk No</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" *******="myFunc('247977853','Mat : ','Text(255)');" title="Text(255)">Mat</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" *******="myFunc('247977853','CW : ','Text(255)');" title="Text(255)">CW</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" *******="myFunc('247977853','Fin Wght : ','Double');" title="Double">Fin Wght</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" *******="myFunc('247977853','Est Hrs : ','Double');" title="Double">Est Hrs</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" *******="myFunc('247977853','START TIME : ','Date/Time');" title="Date/Time">START TIME</A></TD><TD CLASS="AccHDRMain" align=left ><A HREF="javascript: void(0)" *******="myFunc('247977853','END TIME : ','Date/Time');" title="Date/Time">END TIME</A></TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><img src='AccArr.gif' width='7' height='7'/></TD><TD CLASS="AccDataElem" >101371 </TD><TD CLASS="AccDataElem" >Rautaruukki Oyj </TD><TD CLASS="AccDataElem" >15/06/2009</TD><TD CLASS="AccDataElem" >26 </TD><TD CLASS="AccDataElem" >RX9X</TD><TD CLASS="AccDataElem" >6405</TD><TD CLASS="AccDataElem" >11.4</TD><TD CLASS="AccDataElem" >1.5</TD><TD CLASS="AccDataElem" >26/06/2009 11:50:33</TD><TD CLASS="AccDataElem" >28/06/2009 11:50:33</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >101371 </TD><TD CLASS="AccDataElem" >Rautaruukki Oyj </TD><TD CLASS="AccDataElem" >15/06/2009</TD><TD CLASS="AccDataElem" >26 </TD><TD CLASS="AccDataElem" >RX9X</TD><TD CLASS="AccDataElem" >6405</TD><TD CLASS="AccDataElem" >11.4</TD><TD CLASS="AccDataElem" >1.5</TD><TD CLASS="AccDataElem" >26/06/2009 11:50:33</TD><TD CLASS="AccDataElem" >28/06/2009 11:50:33</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >101371 </TD><TD CLASS="AccDataElem" >Rautaruukki Oyj </TD><TD CLASS="AccDataElem" >15/06/2009</TD><TD CLASS="AccDataElem" >26 </TD><TD CLASS="AccDataElem" >RX9X</TD><TD CLASS="AccDataElem" >6405</TD><TD CLASS="AccDataElem" >11.4</TD><TD CLASS="AccDataElem" >11</TD><TD CLASS="AccDataElem" >26/06/2009 11:50:33</TD><TD CLASS="AccDataElem" >28/06/2009 11:50:33</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >101384 </TD><TD CLASS="AccDataElem" >Corus Port Talbot </TD><TD CLASS="AccDataElem" >15/06/2009</TD><TD CLASS="AccDataElem" >26 </TD><TD CLASS="AccDataElem" >RX9</TD><TD CLASS="AccDataElem" >6998</TD><TD CLASS="AccDataElem" >9.81</TD><TD CLASS="AccDataElem" >1.5</TD><TD CLASS="AccDataElem" >26/06/2009 11:50:33</TD><TD CLASS="AccDataElem" >28/06/2009 11:50:33</TD></TR><TR ><TD ID="currRec" CLASS="AccHDRLeftElem" ><BR></TD><TD CLASS="AccDataElem" >101384 </TD><TD CLASS="AccDataElem" >Corus Port Talbot </TD><TD CLASS="AccDataElem" >15/06/2009</TD><TD CLASS="AccDataElem" >26 </TD><TD CLASS="AccDataElem" >RX9</TD><TD CLASS="AccDataElem" >6998</TD><TD CLASS="AccDataElem" >9.81</TD><TD CLASS="AccDataElem" >1.5</TD><TD CLASS="AccDataElem" >26/06/2009 11:50:33</TD><TD CLASS="AccDataElem" >28/06/2009 11:50:33</TD></TR><TR ><TD ID="IE" CLASS="AccIEOnlyMain" colSpan= 11 ><TABLE width="100%" align=left VALIGN="TOP" ><TR ><TD CLASS="AccIEOnlyInnerRight" > Record: </TD><TD CLASS="AccButton" ><img src='AccNavL.jpg' width='40' height='15'/></TD><TD CLASS="AccIEOnlyData" > 1 </TD><TD CLASS="AccButton" ><img src='AccNavR.jpg' width='40' height='15'/></TD><TD CLASS="AccIEOnlyInnerLeft" > of 47</TD></TR></TABLE></TD></TR><TR ><TD CLASS="AccIEOnlyMain" colSpan= 11 ><TABLE cellSpacing=0 cellPadding=0 width="100%" align=left VALIGN="TOP" ><TR ><TD ID="Data247977853" CLASS="AccInfoBarInnerRight" >RTSmall</TD><TD ID="Info247977853" CLASS="AccInfoBarData" >Select Query, Record Count : 47</TD></TR></TABLE></TD></TR><TR ><TD CLASS="tpkrow" colSpan = 11 ><CENTER><font size = 1>Access HTML Add-in provided by theprimarykey.com</font></CENTER></TD></TR></TABLE></CENTER>

Hi Can anyone show me how to on my start time column i can have first entry now, end time will be start time + est Hrs , then next start time if same roll no will be last end time if its new roll no it will be last end time + 1 hour, would i be better off adding another Column as sequence No to run this with ?

Many Thanks

Nick
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question
Web Scraping using VBA
Replies
4
Views
9K
atr140
A
C
  • Question
Need help on javascript web server for macro automation
Replies
1
Views
322
John_w
J
M
  • Question
Need help with IE automation
Replies
1
Views
353
manonique
M
Alphacsulb
  • Question
Save File Name as Record 1 Column 7
Replies
2
Views
534
Alphacsulb
Alphacsulb
P
  • Question
My first pivot table
Replies
2
Views
530
petereddy
P

Forum statistics

Threads
1,215,137
Messages
6,123,253
Members
449,093
Latest member
Vincent Khandagale

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top