Results 1 to 10 of 10
  1. #1
    rmoore is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Feb 2020
    Posts
    47

    Change "checks to perform" based in daily, monthly or yearly

    Having trouble getting my head around this. I have a maintenance DB that i want to add routine services (Daily, Monthly, Yearly) i will use the manufacturers guidelines (see attached). When i generate the service I want to choose what ie. daily or monthly and the those checks to be added to the service order.

    Moore V5.zip

    BoomLift.zip

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    each service would have a Recurrence code : M, D, Y,
    then when its time to renew the date, based on the last ServiceDate:

    [NextSvcDate] = DateAdd(sRecurCode,1,[SvcDate])

  3. #3
    rmoore is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Feb 2020
    Posts
    47
    I'm tracking on how to generate the service. I wasn't clear enough on my problem. Let me try again. I have equipment that needs daily, monthly, and yearly services. I have a list of areas to be checked. 1. Boom Assembly 2. Bearings 3. Forks 4. Drive Hubs etc.... I have a list of "Checks" to be performed. 1. Check for proper installation 2. Check for cracked or broken welds 3. Check fluid level 4. Check for leakage etc... What my idea is to have a form that I can select what type of service I want, daily, monthly, yearly then I need a list to display or pop-up so I can select multiple equipment. Once selected a query will pull the correct areas and checks based on daily, monthly or yearly. I cant figure out how to setup the tables and query.
    Click image for larger version. 

Name:	Screenshot 2022-12-03 104448.jpg 
Views:	26 
Size:	130.2 KB 
ID:	49245Click image for larger version. 

Name:	Screenshot 2022-12-03 104534.jpg 
Views:	23 
Size:	73.2 KB 
ID:	49246

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,858
    I would have a table that shows the Frequency as mentioned by Ranman256, and the FK ID of that code. My ID would be an autonumber, not your numeric code, which could change? Your Table yould hold he code number and description plus the autonumber ID.
    Now you could have a record for each (makes it a little easier), so something that has a Daily, Monthly and Yearly check would have 3 records?, or a more complicated arrangement that indicates a code is Daily and Monthly, but not yearly. For simplicity, I would go with my first option.

    Then you just concatenate the codes for the relevant column.

    http://www.accessmvp.com/thedbguy/co...itle=simplecsv
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    xps35's Avatar
    xps35 is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    Before you start thinking about forms, you need to get your database design right. I think you need at least the following tables:
    1 Main area (e.g. Boom Assembly)
    2 Area (Bearings)
    3 Checks (Fluid level)
    4 Frequency (Daily)
    5 A junction table between Area and Checks (with Area, Check, Frequency)

    The form you are showing is not suitable for entering data. You can possibly make something like this to show information. Your input forms will have to be aligned with the data structure (with main and sub forms).
    Groeten,

    Peter

  6. #6
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I come from that kind of world and have to wonder if by choosing an interval then equipment that you're putting the cart before the horse so to speak. I could go on about how it all worked but I don't think it's anything you'd want to consider building.

    What I don't get about what you've shown is, don't you have to handle that same piece of paper every week for 2 years? Or are you looking for a form for input, a report for output (the grid you show) and supporting tables so that you can reprint along with the historical data? This would require a fair bit of coding to support that because of your csv approach to the data. In all my years of Access only once have I ever needed to have csv data in one field. If that's your goal I'm not sure that it's the best approach for the job here. Have you considered a work order based approach instead of trying to show multiple frequencies for the same equipment/unit?
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  7. #7
    rmoore is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Feb 2020
    Posts
    47
    Quote Originally Posted by Micron View Post
    I come from that kind of world and have to wonder if by choosing an interval then equipment that you're putting the cart before the horse so to speak. I could go on about how it all worked but I don't think it's anything you'd want to consider building.
    I understand what you mean. Choosing the equipment first does make more sense. I was trying to design it by thinking how the users will generate the service order. 95% of the time the all serviceorders will be the same frequency (e.g. today all services will be daily), that's why I said choosing frequency first. I think it will be very time consuming having the user open the serviceorders one by one. I need to be able to choose multiple equipment's.

    Quote Originally Posted by Micron View Post
    What I don't get about what you've shown is, don't you have to handle that same piece of paper every week for 2 years? Or are you looking for a form for input, a report for output (the grid you show) and supporting tables so that you can reprint along with the historical data?
    It should be a report that is generated. The idea is, a user would look at a already existing schedule (excel), open a form that would allow them to choose the equipment to be serviced, choose the frequency (frequency will determine which checks should be on the serviceorder report). Then generate the report for all equipment selected and printout the 10 -15 PMCS's due for the next day.

    The db is workorder based, I'm trying to add PMCS. Currently we are opening regular workorders to do the services. I want to separate the workorders from serviceorders for obvious reasons. I need the serviceorders to have the correct frequency checks on the serviceorder. Right now we are attaching the service checklist to each workorder to show that it was a "service" and to document what was done.
    Last edited by rmoore; 12-04-2022 at 05:02 AM.

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Things are not as obvious as you might think. We probably know of the same things, just by different names. I have no idea what a PMCS is by that name. I think the main thing to understand is that if the db structure is correct, you can retrieve information in practically any fashion you want. If I wanted to find equipment where daily checks would be applicable, that list would be anything whose last service wasn't done today, yes? So easy enough. Where I don't see how that makes sense is, for today why would I want to try and return a list of equipment where the frequency is annual? Perhaps you're not saying only daily would be handled this way.

    I think it will be very time consuming having the user open the serviceorders one by one.
    Why would they have to do that? Wouldn't you use a query to list equipment whose frequency is daily? I'm probably not following the methodology being used.

    Anyway, I don't mean to turn your thread into a conversation about how to build a db for maintenance. The direct answer to your original question could be in post 2.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  9. #9
    xps35's Avatar
    xps35 is online now Competent Performer
    Windows 10 Office 365
    Join Date
    Jun 2022
    Location
    Schiedam, NL
    Posts
    229
    In my earlier response I was not aware that you had already sent a database. I have now checked the database.
    I think I pretty much understand what you write in post #3. However, I can't quite relate that to the database. In your post you talk about equipment. Is that what's in the AssetT table? You also mention areas in your post. I see something similar in the database, namely the AssetType table. However, the content of that table does not correspond to the list of areas. For example, you name areas Boom Assembly and Boom Weldments. In the table I only see Boom Lift. Moreover, in your post you suggest a layering that I do not see in the table.
    In your model, you can specify only one asset type for an asset. I wonder if that is correct. Can an asset not contain all kinds of parts?
    I can't find the checks (= performance codes?) you mention in the database at all.
    However, the principle of my post (#5) still stands. You have to get your structure in order first. The main lines of my structure also remain intact, although the names I used are not correct in relation to your database.
    Groeten,

    Peter

  10. #10
    rmoore is offline Advanced Beginner
    Windows XP Access 2016
    Join Date
    Feb 2020
    Posts
    47
    Quote Originally Posted by xps35 View Post
    In your post you talk about equipment. Is that what's in the AssetT table?
    Yes

    Quote Originally Posted by xps35 View Post
    I see something similar in the database, namely the AssetType table. However, the content of that table does not correspond to the list of areas. For example, you name areas Boom Assembly and Boom Weldments. In the table I only see Boom Lift.
    No, that table is for grouping assets by types, since each boom lift might have different manufacturers and be different models.

    Quote Originally Posted by xps35 View Post
    Moreover, in your post you suggest a layering that I do not see in the table.
    Not sure what you mean by layering.

    Quote Originally Posted by xps35 View Post
    In your model, you can specify only one asset type for an asset. I wonder if that is correct. Can an asset not contain all kinds of parts?
    The ModelT is to identify which model a asset is. e.g. Caterpillar Loader 980H - manufacturer/asset type/model

    Quote Originally Posted by xps35 View Post
    I can't find the checks (= performance codes?) you mention in the database at all.
    I havent added that table yet, but the (Areas) are in PmcsT and BoomLiftPmcsT. I was just trying different things to see if i could get what i needed. It didnt make sense to me so .... I came to the experts

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 01-30-2022, 11:05 PM
  2. Replies: 32
    Last Post: 03-05-2019, 05:53 PM
  3. Replies: 2
    Last Post: 04-12-2016, 12:58 PM
  4. Replies: 6
    Last Post: 09-04-2014, 02:03 PM
  5. Replies: 1
    Last Post: 10-19-2009, 02:37 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums