Access time help

[mercmannick]

:banghead: Hi i have been trying to work out this from a x post on http://www.mrexcel.com/forum/showthread.php?p=1980836#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:banghead:

[mercmannick]

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;


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"?

[mercmannick]

hi OBP

can do anything i/you need to do to it no restrictions atall ,

Thanks

Nick

[OBP]

Nick, I have created the StatusLevel Table as well.
I am moving on to the Forms now.
What data do you need to see form tblSQL for what you want to do?
Do you have a separate Customer details Table?
Do the Area & Delivery Terms go with the Order or with the Customer?

[mercmannick]

Nick, I have created the StatusLevel Table as well.
I am moving on to the Forms now.
What data do you need to see form tblSQL for what you want to do?
Do you have a separate Customer details Table?
Do the Area & Delivery Terms go with the Order or with the Customer?

Hi OBP

Area and Del terms go with Order, Op comment and OUT HT are just comment cols Seq will just be order they are scheduled,

Seq Roll Customer Man Month Wk No Mat CW No FIN WGHT Operation Comment OUT HT Est Hours


Hope this helps

And Many Thanks for your help OBP

Nick

[OBP]

Nick, have a look at this version, I have split out a lot of the tblSQL fields into their own tables as I think they should be.
I think that WO Ref should also be in it's own table but as I don't know where it comes from I am not sure.

Is the part in the VBA Window the fields that you need?
Can the SQL database be set up like this?

[mercmannick]

Nick, have a look at this version, I have split out a lot of the tblSQL fields into their own tables as I think they should be.
I think that WO Ref should also be in it's own table but as I don't know where it comes from I am not sure.

Is the part in the VBA Window the fields that you need?

Can the SQL database be set up like this?

Hi OBP yes VBA is the feilds req, i cant open this in work as we have off 2000 in here till friday when we will get 2007

Thanks

Nick

[OBP]

What about the WO Ref field where does that come from?

[mercmannick]

What about the WO Ref field where does that come from?

hi OBP it comes from SQL man plan

Regards

Nick

[OBP]

So is it a Generated Value, can it be in another table and selected?

[mercmannick]

So is it a Generated Value, can it be in another table and selected?

hi OBP,
yes it can be put on another table and selected

many thanks

nick

[mercmannick]

Hi OBP , When i get my updated SQL man plan , can i just import into same table , also if i had a BOM table with FN , Part Desc , Per Roll is there a way in to enable once it hits milling stage to give me a list or report of BOM reqd, and to keep record so it dosent get ordered twice

Many Thanks

Nick

[OBP]

Nick, no you won't be able to Import it directly, how often do you get a new one?

We can build in the Milling check.

[OBP]

Nick, this is what the Table Structure now looks like.

I am working on the Forms now.

[OBP]

Nick, how big can an Access form be on your Computers?

[mercmannick]

Nick, no you won't be able to Import it directly, how often do you get a new one?

We can build in the Milling check.

Hi OBP

at moment it is every cpl days , but in future when they sort out security on SQL servers i hope to be able to link to it or similarso we have live data,

thats brilliant if we can build in Milling check , basically if any roll hits milling and has a BOM then print or anything list or form of what we require BOM wise at momen i have table in ACCESS that i pull from manually when i know what is going through milling on a daily basis , can this milling check also record whatever has been ordered and date so it dosent get ordered twice .

i really appreciate all your help on this OBP , i have one other question in DB did you see table named tblMachCap i have restrictions on what can go in each machine size wise is there anyway of also having msgbox or anything similar if we try and do a roll thats to big or too heavy to just warn .


Thanks again

Nick:beerchug:

[mercmannick]

Nick, how big can an Access form be on your Computers?
OBP

can be full screen i would prefer that

Nick

[mercmannick]

Nick, this is what the Table Structure now looks like.

I am working on the Forms now.

OBP that looks great thanks , now im thinking if we are working on in and out times then most machines are diffrent shifted , can we work around this somehow ,

Thanks

Nick

[OBP]

Keep it in mind for when we get there.
What size is your screen?

[mercmannick]

Keep it in mind for when we get there.
What size is your screen?

OBP

mine is 17" and work is the same , thanks

Nick

:thumb

[OBP]

Nick, I haven't forgotten you I have just been busy on a couple of other databases.

[mercmannick]

Nick, I haven't forgotten you I have just been busy on a couple of other databases.

Hiya OBP

thats no problem , thank you for all your help

Nick

[mercmannick]

Hi OBP

now have a list of all BOM

Many Thanks

Nick

[OBP]

Nick, how does this BOM fit in with the SQL table?
Do you need to see Old Rolls on the main data form, or can they go on a History form?

[mercmannick]

Nick, how does this BOM fit in with the SQL table?
Do you need to see Old Rolls on the main data form, or can they go on a History form?

Hi OBP

old rolls can go in history form yes, BOM is all rolls that have any BOM associated with them , what i do is a vlookup at moment to bom table and when i enter rolls into MILL it give me a list and then i print it out and order from Stores

Hope this helps

Nick

[OBP]

So is the BOM table going to be in the Access database as well?

[mercmannick]

So is the BOM table going to be in the Access database as well?

Yes OBP it will, it should never change unless a new order comes in and it requires BOM

Thanks

Nick

[OBP]

Nick, I am pulling out the Part Numbers from the BOM, should the same Part number have different descriptions?
Like
CW 6172DDK = KEY Ref:CW 6172DDK & CW 6172DDK DUMMY KEY

[mercmannick]

Nick, I am pulling out the Part Numbers from the BOM, should the same Part number have different descriptions?
Like
CW 6172DDK = KEY Ref:CW 6172DDK & CW 6172DDK DUMMY KEY

Hi OBP

CW No would be 6172
Drawing No would be CW6172
Part No would be CW6172DDK
Type would be DUMMY KEY

Hope This helps

Thanks

Nick

[OBP]

So should the table be laid out like that?
As at the moment you have a lot of parts with different descriptions like the one that I have shown.
But we have another big problem.
The new BOM table has 8907 Rolls that are not in the SQL table. Should they be in there as well or are they old Information?

[mercmannick]

So should the table be laid out like that?
As at the moment you have a lot of parts with different descriptions like the one that I have shown.
But we have another big problem.
The new BOM table has 8907 Rolls that are not in the SQL table. Should they be in there as well or are they old Information?

Hi OBP

yes if the BOM table can be laid out like that, and yes they should all be in the SQL table too

Thanks

Nick

[OBP]

Nick, I think the database is going to be too large to post on here, so I will need your email address, can you private mail it to me?
I need for you to have a look at it and also do a shedfull of work on it.
All those Rolls in the BOM that are not in the SQL need all the other data added, like Customer, Delivery Terms, WORef, Grades, areas etc etc.