Consulting

Page 1 of 3 1 2 3 LastLast
Results 1 to 20 of 50

Thread: Access time help

  1. #1

    Access time help

    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

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    I am sure I can help you but I am not quite sure what you want to do.
    Do you work for Corus?

  3. #3
    Quote Originally Posted by OBP
    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

  4. #4
    [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

  5. #5
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    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.

  6. #6
    Quote Originally Posted by 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.
    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
    Last edited by mercmannick; 06-26-2009 at 07:52 AM.

  7. #7
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    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?

  8. #8
    Quote Originally Posted by 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?
    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

  9. #9
    OBP

    as requested

    Regards

    Nick

  10. #10
    OBP

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

    Nick

  11. #11
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    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?
    Last edited by OBP; 06-27-2009 at 08:33 AM.

  12. #12
    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

  13. #13
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    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?
    Last edited by OBP; 06-27-2009 at 10:33 AM.

  14. #14
    yes please OBP

    Thanks

    Nick

  15. #15
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    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?
    Last edited by OBP; 06-28-2009 at 06:42 AM.

  16. #16
    Quote Originally Posted by 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?
    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

  17. #17
    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

  18. #18
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    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?

  19. #19
    Quote Originally Posted by 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?
    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

  20. #20
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,297
    Location
    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"?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •