Access time help

[mercmannick]

Hi i have been trying to work out this from a x post on http://www.mrexcel.com/forum/showthr...36#post1980836

Can anyone point me in right direction please

Thanks

Nick

[OBP]

I am sure I can help you but I am not quite sure what you want to do.
Do you work for Corus?

[mercmannick]

I am sure I can help you but I am not quite sure what you want to do.
Do you work for Corus?

hi OBP i work for a company that supplies CORUS yes,

i am trying to create a schedule by workcentre in access , i have est hours , start time and end time which is going to be if roll no is same then start time will just carry on from previous op (est hours) if its new job it will be last end time + 1 hour setup etc

Not sure best way to do this

Thanks

Nick

[mercmannick]

[VBA]SELECT [Sql Man Plan].[Roll No], [Sql Man Plan].Customer, [Sql Man Plan].[Man Targ], [Sql Man Plan].[Week No] AS [Wk No], [Sql Man Plan].Grade AS Mat, [Sql Man Plan].[CW Drg] AS CW, [Sql Man Plan].Weight AS [Fin Wght], Now() AS [START TIME], [START TIME]+Hour([Hours]) AS [END TIME], [FN Routes].Hours as [EST Hrs]
FROM [Sql Man Plan] INNER JOIN (WC INNER JOIN [FN Routes] ON WC.opref = [FN Routes].opref) ON [Sql Man Plan].[File No] = [FN Routes].[file no]
GROUP BY [Sql Man Plan].[Roll No], [Sql Man Plan].Customer, [Sql Man Plan].[Man Targ], [Sql Man Plan].[Week No], [Sql Man Plan].Grade, [Sql Man Plan].[CW Drg], [Sql Man Plan].Weight, Now(), [START TIME]+Hour([Hours]), WC.WC, [Sql Man Plan].Status, [FN Routes].Hours
HAVING ((([Sql Man Plan].[Week No])="26") AND ((WC.WC) Like "RT WR*") AND (([Sql Man Plan].Status)<>"INSPECTED"))
ORDER BY [Sql Man Plan].[Roll No], [Sql Man Plan].Customer;
[/VBA]

Is my query at moment

Nick

[OBP]

Nick, actually the best way is to use VBA with a recordset when the data is actually being entered.
You open the table with the same Roll No., if there are records then you know it is the same roll and you Increment the Time based on the (set hours) if there are no records you know it is a new Roll.

My Son Works at Corus Trostre Plant.

[mercmannick]

Nick, actually the best way is to use VBA with a recordset when the data is actually being entered.
You open the table with the same Roll No., if there are records then you know it is the same roll and you Increment the Time based on the (set hours) if there are no records you know it is a new Roll.

My Son Works at Corus Trostre Plant.

HeHe we supply to him then

how would i do this OBP , as currently i have table FN Routes which has fields:
1.File No
2.Sort Order
3.opref
4.OP Desc
5.Est Hours

i have another table WC which has fields:
1.opref , which is linked to field 3 FN Route
2.OP Desc
3.WC

i have another table SQL Man Plan which is all current live orders has fields:
1.Roll No 2.Customer 3.Wo Ref 4.Grade 5.Status 6.Operation 7.Cast Date 8.Due Date 9.Man Targ 10.Weight 11.Area 12.Delivery Term 13.Value 14.
Week No 15.CW Drg 16.Cw Rev 17.File No , which is linked to 1.File No on FN Routes 18.barrel diameter 19.barrel length 20.overall length 21.F7 22.Profit 23.S/C


i use these to create a days schedule ,

Thanks Nick

[OBP]

Nick, any chance that you can post a zipped copy in Access 2003 format, it doesn't need any data in it, I just need to see how you are relating your data and Inputting it to decide the best place for the VBA code to go.
Are you using the File No. to relate the files as in your Other Forum post of the SQL?

[mercmannick]

Nick, any chance that you can post a zipped copy in Access 2003 format, it doesn't need any data in it, I just need to see how you are relating your data and Inputting it to decide the best place for the VBA code to go.
Are you using the File No. to relate the files as in your Other Forum post of the SQL?

Hi OBP

yes file number is main identifier , for that order it then has a Cw no allocated and it then gets allocated if it has say ten rolls on order it will get ten individual roll No's which will follow it to completion, will zip a copy up now for you

Thanks

Nick

[mercmannick]

OBP

as requested

Regards

Nick

[mercmannick]

OBP

is there any way to rationalize this DB too whilst you are looking at it

Nick

[OBP]

Nick, I will take a look, I am working on another database at the moment, but will get to yors as soon as possible.
BOM - Bill of Material?

Any particular reason for having the Tabels in Excel?
Can you import them so that I can see what they are like?

[mercmannick]

OBP

this is better format have removed all useless stuff and renamed a few things , yes i used an old BOM (bill of Mat DB)

Nick

[OBP]

Nick, you have a bit of a problem with the tblFNRts & tblCWtoFN tables, the tblFNRts has 5688 File nos that aren't in the tblCWtoFN table. You also have the same problem with the tblSQL as well, although it is only 28 File Nos.
Which means that you can't set up the proper Relationship.
Do you want me to add the missing File nos to the tblCWtoFN table?

[mercmannick]

yes please OBP

Thanks

Nick

[OBP]

nick, how do you currently get your Roll Nos?
You also have a problem in t tblWC table, the descriptions have been truncated for some reason.
Do you Forms created?

[mercmannick]

nick, how do you currently get your Roll Nos?
You also have a problem in t tblWC table, the descriptions have been truncated for some reason.
Do you Forms created?

Hi OBP originally the desc text was cut short but now i have full text in tblFNRts, yes to the forms please ,what i am trying to do eventually is to create a WC schedule on a daily basis, based on weeknumber and wc, will also need a way to remove from schedule if operation has been completed , i would love in end to be able to show 24hrs worth or more on a gannt chart

Thanks

Nick

oops forgot Roll Nos question, this is from SQL plan that has all live orders on and run every morning and dropped in an excel file at moment.

Thanks

Nick

[mercmannick]

OBP , at the moment the SQL PLAN will be in excel format i meant to say that in the future once the security issues are sorted will be able to pull direct from SQL Server, IT Guy reckons 3 to 4 weeks at moment , they have only just moved all our stuff to SQL 2005 from using foxpro etc

Thanks

Nick

[OBP]

So should the description be removed form the WC table?
Can you just run through the sequence of events for me from start to finish, does it start with a "proposal" or an Order?

[mercmannick]

So should the description be removed form the WC table?
Can you just run through the sequence of events for me from start to finish, does it start with a "proposal" or an Order?

It will be a proposal , then into a order , then loaded onto casting plan and onto Heat treatments and onto machine shop where i use it

Thanks

Nick

Yes Desc can be removed it was purely for my benefit only

Nick

[OBP]

So initially you will be starting with a Company in the tblSQL table?
The tblSQL table should have 2 subsidiary tables to supply data for selection of the Status & Operation fields, I have developed the Operation one, but are you in a position to change the way the SQL table works?
Or is it "fixed"?