Page 2 of 3 FirstFirst 123 LastLast
Results 16 to 30 of 36
  1. #16
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664


    I thought that it would be appropriate to create a table for customers who run up high operating hours on the forklifts.
    So this is something you (the collective "you") would manually enter into/delete from the table?


    The principal is that a forklift should be serviced after every 250 hours of operation.
    OK, So the customers/clients notify you when each forklift reaches an additional 250 hrs operating time?
    Do they report RTH at the end of the month? Daily? On a some schedule?

    What this is leading to is "What is the report going to look like?". If the report is monthly and a customer doesn't report RTH for the month (things are slow), what then?


    I have a couple of other questions about the tables, but they can wait a while.



    I appologies for my incometance
    You should not apologize. And I would not say "incometance".......... how about "inexperience". We all have to start somewhere.


    What I have learned is to start designing the tables/relationships on paper, whiteboard, the window, cardboard, even sticky notes.
    Try and determine what data you will need for reports to ensure you capture all required data. Try and not store calculated data.

    Once you have drawn the tables/relationships on the medium (ie Whiteboard, etc), try walking through the steps to enter data. Once that seems to be working ok, then get into Access.
    Create the tables (Do not use Look up FIELDS, calculated FIELDS or multi-value FIELDS), form queries and forms.
    Enter test data to make sure all works correctly and smoothly.
    Then create the report queries and the reports.



    So back to calculating run time hours. When you print a report, what will be the time frame?

  2. #17
    PeterS is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    45
    Quote Originally Posted by ssanfu View Post
    So this is something you (the collective "you") would manually enter into/delete from the table?

    Yes....when we monitor the hours each customer logs onto the forklift we will deceide who must go onto the "watch list". It is not a table that will be used to capture data on a frequent basis. We will only make changes once a customer is recognised as a high operating hours customer.

    OK, So the customers/clients notify you when each forklift reaches an additional 250 hrs operating time?
    Do they report RTH at the end of the month? Daily? On a some schedule?

    We contact each customer or visit each customer on a monthly basis and take the readings of the forklift hour metre. We then calculate the hours and can see which forklifts need to be serviced.

    What this is leading to is "What is the report going to look like?". If the report is monthly and a customer doesn't report RTH for the month (things are slow), what then?

    At the end of the month, we want to have a form where we enter:
    1. Customer Name
    2. The forklift number
    3. The date range
    and we want to see something like this:

    CUSTOMER FORKLIFTNUMBER READINGDATE HOURS
    Barnlab.......Forklift15...............01/09/2018...........1000...
    ........Forklift15...............03/10/2018............1130...130
    ........Forklift15...............31/10/2018............1299...169
    ........Forklift15...............30/11/2018............1420...121



    I have a couple of other questions about the tables, but they can wait a while.




    You should not apologize. And I would not say "incometance".......... how about "inexperience". We all have to start somewhere.


    What I have learned is to start designing the tables/relationships on paper, whiteboard, the window, cardboard, even sticky notes.
    Try and determine what data you will need for reports to ensure you capture all required data. Try and not store calculated data.

    Once you have drawn the tables/relationships on the medium (ie Whiteboard, etc), try walking through the steps to enter data. Once that seems to be working ok, then get into Access.
    Create the tables (Do not use Look up FIELDS, calculated FIELDS or multi-value FIELDS), form queries and forms.
    Enter test data to make sure all works correctly and smoothly.
    Then create the report queries and the reports.



    So back to calculating run time hours. When you print a report, what will be the time frame?
    If I understand the question correctly, I want to be able to select the date range for the report. I want to be able to set the date range for a particular customer and a forklift number and get a report along the lines of what I did above.

  3. #18
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I think this is set up wrong.

    Your service table should contain the foreign key to your forklift table, not just the customer table.

    Let's say you have Forklift 1 currently at customer A.
    Customer A releases Forklift 1 because they don't need it anymore
    Customer B starts leasing Forklift 1. You still want to reference all the records for the forklift regardless of which customer the machine was with at the time, the way you have it set up you would never be able to tell where that forklift had been operated. only where it currently is.

    Secondly I wouldn't have a table to define whether a customer is a high usage you could define that in your data. Let's say if a customer had an average usage across the forklifts they lease of more than 100 hours per month. That should be easy to calculate. This type of monitoring should be based on data not whether or not someone updates a flag (which can be forgotten)

    Finally, I think I have a solution to make this much more doable without doing anything crazy with domain functions (dlookup, dsum) as I'd previously suggested. It would involve populating your forklift service table with a sequence number so every forklift had a record from 1 to x in sequential order. As long as you never enter records out of order that should also be very simple to accomplish as part of your data entry.

    I would also change your 'area' to be a foreign key to an area table, to prevent people from typing in the name of the area incorrectly.

    So this was my example table:

    FKS_ID Forklift_ID Service_Date Operation_Hours Sequence
    1 1 1/1/2018 250 1
    2 1 2/1/2018 300 2
    3 1 3/1/2018 450 3
    4 1 4/1/2018 500 4
    5 1 5/1/2018 600 5
    6 2 1/1/2018 100 1
    7 2 2/1/2018 125 2
    8 2 3/1/2018 150 3
    9 2 4/1/2018 200 4
    10 2 5/1/2015 225 5

    I created this query:

    Code:
    SELECT tblForkliftService.Forklift_ID, tblForkliftService.Sequence, IIf([sequence]=1,Null,[sequence]-1) AS NextSequence
    FROM tblForkliftService;
    and named it qryForkliftServiceJunction

    Then I created this query:

    Code:
    SELECT ForkliftTbl.ForkliftMake, ForkliftTbl.ForkliftModel, ForkliftTbl.ForkliftNumber, tblForkliftService.Service_Date, tblForkliftService.Operation_Hours AS CurrentHours, tblForkliftService_1.Operation_Hours AS PreviousHours, [currenthours]-[previoushours] AS HoursUsedFROM ((tblForkliftService LEFT JOIN qryForkliftServiceJunction ON (tblForkliftService.Forklift_ID = qryForkliftServiceJunction.Forklift_ID) AND (tblForkliftService.Sequence = qryForkliftServiceJunction.Sequence)) LEFT JOIN tblForkliftService AS tblForkliftService_1 ON (qryForkliftServiceJunction.Forklift_ID = tblForkliftService_1.Forklift_ID) AND (qryForkliftServiceJunction.NextSequence = tblForkliftService_1.Sequence)) INNER JOIN ForkliftTbl ON tblForkliftService.Forklift_ID = ForkliftTbl.ForkliftID_Pk;
    Based on your existing table ForkliftTbl

    I know the structure in your current forklift service is different than mine but this seems a simple way to get the sequencing you want with the correct calculation.

    EDIT: This was the result of the query.

    ForkliftMake ForkliftModel ForkliftNumber Service_Date CurrentHours PreviousHours HoursUsed
    RHINO-HELI CPCD35 1 1/1/2018 250

    RHINO-HELI CPCD35 1 2/1/2018 300 250 50
    RHINO-HELI CPCD35 1 3/1/2018 450 300 150
    RHINO-HELI CPCD35 1 4/1/2018 500 450 50
    RHINO-HELI CPCD35 1 5/1/2018 600 500 100
    TOYOTA 7FBE18 2 1/1/2018 100

    TOYOTA 7FBE18 2 2/1/2018 125 100 25
    TOYOTA 7FBE18 2 3/1/2018 150 125 25
    TOYOTA 7FBE18 2 4/1/2018 200 150 50
    TOYOTA 7FBE18 2 5/1/2015 225 200 25

  4. #19
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by PeterS View Post
    At the end of the month, we want to have a form where we enter:
    1. Customer Name
    2. The forklift number
    3. The date range
    and we want to see something like this:

    CUSTOMER FORKLIFTNUMBER READINGDATE HOURS
    Barnlab.......Forklift15...............01/09/2018...........1000...
    ........Forklift15...............03/10/2018............1130...130
    ........Forklift15...............31/10/2018............1299...169
    ........Forklift15...............30/11/2018............1420...121


    If I understand the question correctly, I want to be able to select the date range for the report. I want to be able to set the date range for a particular customer and a forklift number and get a report along the lines of what I did above.
    Yes, this is possible.
    So each forklift will have 1 or more readings per month?

  5. #20
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Background - was a diesel mechanic for many years before working in equipment reliability. Did a few unrelated things after that, one of which was to work with Purchasing and Contracting departments to transition forklift maintenance out of the company maintenance department and contract it out since I was in contracting services at the time. No, the guys who had a gravy train ride were reassigned to the main garage where they came from, so nobody lost a job. My employer had 230 or so units spread all around the plant.

    I had a look at ssanfu db since it was the latest one and can see some work was put into a good effort. For all I know, it is something that served him well elsewhere, so I'm not saying it wasn't good for anyone in particular, just that I would change a few things and I commend the effort and respect the author. These are the things I would put out there for consideration and are opinions, which doesn't make them right.

    ContactName in CustomerTbl limits you to only one contact person per customer. Not only could there be a need for more than one contact for a simple customer arrangement, a customer could have several departments, each having their own contact person. Not sure of the purpose for Area field; probably doesn't mean different areas within a company because that would require different PK values for the same customer as the customer name would have to be repeated. Should have

    tblContacts
    ContactPK
    CustomerIDfk
    ContactName
    ContactPhone
    ContactExt
    This way, there can be multiple contacts for a customer.

    For customers with multiple areas
    tblForkliftAreas
    ForkliftAreaPK
    CustomerArea (text)
    CustomerIDfk
    ForkliftIDfk
    and maybe even Gate and Door numbers

    this would allow
    - the loaning of unit to a different area within the company (assuming they bothered to tell you it was loaned)
    - service provider loaning a machine to any given area of said company. They can be there own customer as they likely have their own units that they use
    - moving machine from one customer to another without losing history
    An updatable notes field might be sufficient to document the temporary loaning from one area to another.

    NextServiceHours in the sample db is a calculated target and shouldn't be stored IMHO. Typically, it's usage based but sometimes goes by date. The decision to include both would be db user driven, unless you were marketing this db. Then it would be foolish to leave out the option. Reason being, some inspections are govt mandated and are based on a calendar year. Regardless, a form should calculate the next meter reading or date target by adding the interval to the last meter reading or inspection date. In fact, there are likely different PM services required by the accumulation of hours, thus a table of inspections (e.b. PM1, PM2, PM3...) and their intervals might be more appropriate. And yes, sometimes the calculation results in a negative number, such as when the last meter reading is 1150 hours before it dies and it gets replaced (starts over at zero). 1150 ends up getting subtracted from some small number at or before the next service, e.g. 75-1150 when you expect 1400 - 1150. It's a common misconception that the last reading is always higher than the one before.

    I wouldn't put costs in the forklift table. First, it's not an attribute of a forklift, but of an agreement/contract. Second, if used in tracking costs vs revenue, all past calculations will be affected by a price change. Pricing can even impacted by area. You take the unit out of the steel making area and put it in a lab and the damage/wear and tear goes way down. It belongs in a PO or contract table - again, that's only my opinion. So the above is a mish-mash of stuff that may pertain more to a supplier of rental & maintenance services as opposed to a company that wants a db for in house. I'm not even sure if we know which this is. In my typical fashion, I might have jumped on some moving train of thought when the only thing needed is a way to subtract 2 numbers for in house maintenance

    At first, I thought this was possibly a simple matter of grouping by unit, sorting ascending on past inspection dates, and selecting the TOP 2 rows. Then transposing the hour meter values (cross tab query) so that the 2 values could be subtracted. After all the conversation and suggestions about what the original db is lacking, I must admit I don't know if the db is in a state that it's viable even if it could work. So now what? I certainly don't want to take over this thread and make it a development project.
    Last edited by Micron; 12-06-2018 at 06:36 PM.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #21
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    @Micron
    Happy (and glad) to have you jump in, especially with your background.
    I just took the OP dB and did some tweaks to be able to write a UDF to calculate the RTH.
    I also has questions about the Area and Contract fields. And also wanted to add a table for additional contacts.

    Another question was going to be about the Technician field in the ForkliftServiceTbl table. The TechnicianTbl table has the technician first name, but the ForkliftServiceTbl.Technician field has two initials. Wouldn't work so well with two technicians named Jim Smith and John Simmons. I would add fields TechFirstName, TechLastName, TechInitials and maybe TechEmpID_Number. Then the ForkliftServiceTbl.Technician field would be changed to a Long.


    All the above was going to be suggested later. The main focus was to be able to calculate the hours between readings. But first had to tweak the design.

  7. #22
    PeterS is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    45
    New Microsoft Access Database0612.zip

    Hi rpeare
    Ssunfa
    micron

    You guys are awesome and I wish I was on the same level as you. What you have described is actually 100%, now I just need to get it done. I am sorry for my incompetance but I am battling to translate your information into practice and implement into my database. I am getting myself totally confused. I am not sure which tables to change and what to change them to. Also my knowledge of SQL is almost non existant especially on the relationships.

    Should I start over? I mean right from the start from the very first table?

  8. #23
    PeterS is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    45
    The qyrForkliftServiceJunction runs, however I am battling with the second query....this is the SQL Statement

    SELECT ForkliftTbl.ForkliftMake, ForkliftTbl.ForkliftModel, ForkliftTbl.ForkliftNumber, ForkliftServiceTbl.ServiceDate, ForkliftServiceTbl.OperationHours AS CurrentHours, ForkliftService_1.OperationHours AS PreviousHours, [currenthours]-[previoushours] AS HoursUsed FROM ForkliftServiceTbl LEFT JOIN qryForkliftServiceJunction on (ForkliftServiceTbl.ForkliftID_Fk=qryForkliftServi ceJunction.ForkliftID_Fk) AND (ForkliftServiceTbl.Sequence = qryForkliftServiceJunction.Sequence)) LEFT Join ForkliftServiceTbl AS ForkliftServiveTbl_1 ON (qryForkliftServiceJunction.ForkliftID_Fk = ForkliftServiceTbl_1.ForkliftID_Fk) AND (qryForkliftServiceJunction.NextSequence = ForkliftServiceTbl_1.Sequence)) INNER JOIN ForkliftTbl ON ForkliftServiceTbl.ForkliftID_Fk = ForkliftTbl.ForkliftID_Fk;

    I get an error that says "Syntax error in FROM clause"

  9. #24
    PeterS is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    45
    Ok...THANK YOU THANK YOU

    I got it to work, but I had to start from scratch and build up table by table and then study the SQL code and implement what I was extracting. I am still not competent with SQL but at least now I am starting to understand the basic code structure. If I post what I have done would you mind taking a look and give me pointers on where you can see where my weakness is?

  10. #25
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    No real need to ask permission to post; just post and request someone look at whatever it is you're wanting comment on. Then it's possible for anyone to comment.
    And here's one reason why we ask for long code/sql statements or numerous rows of code to be posted within code tags:
    qryForkliftServi ceJunction.ForkliftID_Fk)
    It's likely that someone would pick up on the space between i and c as a problem. However, the problem as I've interpreted it, is that the forum automatically injects spaces around the 50 character mark of an unbroken string. Thus it becomes a waste of time/effort as it never relates to the issue.

    But don't apologize, I might be coming across too strong. No admonition intended

  11. #26
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You really should fix the data before you create the queries.
    Why do you have blank records in the Customer table?


    Code:
    SELECT DISTINCT jnctCustForkliftTbl.CustForkliftID_Pk
    FROM jnctCustForkliftTbl
    ORDER BY jnctCustForkliftTbl.CustForkliftID_Pk;
    This query is on table "jnctCustForkliftTbl". If you execute this query, you will see there are a total of 46 unique PK numbers and the numbers between 2 and 76 are missing.



    You have a LOT of orphan records in table "ForkliftServiceTbl".
    Code:
    SELECT DISTINCT ForkliftServiceTbl.CustForkliftID_Fk
    FROM ForkliftServiceTbl
    ORDER BY ForkliftServiceTbl.CustForkliftID_Fk;
    This query is based on table "ForkliftServiceTbl". There are a total of 46 unique records for the CustForkliftID_Fk field. Note the FK numbers.

    There is only 1 record in "ForkliftServiceTbl.CustForkliftID_Fk" that matches a record in "jnctCustForkliftTbl.CustForkliftID_Pk"; this is the record with a value of 1. The other records have values between 2 and 69: ALL of these 45 records in "ForkliftServiceTbl" are ORPHAN records.



    Have you ever heard of the phrase "Garbage In, Garbage Out"??? You should have valid data in the tables. Really hard to evaluate query results if you have garbage data.

  12. #27
    PeterS is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    45
    99% success. Thank you for all your patience and help. I have attached the database for you to see and make suggestions if you dont mind. If this is workable then I have a question regarding the sequencing.

    When I create the form to populate the ForkliftServiceTbl, how can I ensure that whoever is capturing the data will use the correct sequence number? Can I set up the form to show the last sequence number used for that forklift number?
    Attached Files Attached Files

  13. #28
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Any comments I make might be influenced by my experience on the subject, plus the oft quoted mantra "Normalize until it hurts, denormalize until it works" (or something like that).

    Regarding Entity/Attribute relations for forklifts:
    Forklift attributes are capacity, motive power, serial number, unit number, make, model, etc.
    Pricing data, frequency (3 fields) are not forklift attributes(?). They are attributes of a contract. As I think was previously mentioned, such data in entity tables will affect all historical calculations.
    Scenarios:
    - unit gets older, you drop the rental price
    - unit is placed in high risk location, you up the price
    - customer gets a volume discount, you drop each price
    - etc.
    Any historical calculations are affected by these price changes. You either redesign tables or do work-arounds to prevent it.

    Similarly, I'd say Contract is not a customer attribute; at least not if you adopt the former suggestion and have a contract table with pricing details. Each time a unit moves to another customer, it's a new contract. Historical pricing is maintained.

    Memo fields - they often cause lots of empty space in the main entity table. To avoid that I'd consider memo tables linked to the entity PK field. It's also keeping with the notion that the note is not a forklift attribute, but I suppose that's debatable. Eliminating table empty space being the main benefit.

    If the above makes sense, then HireAgreement stuff isn't an attribute of either the forklift or the customer as much as it is related to a contract table. A junction table is the "marriage" of two other tables. It's purpose is to combine many-to-many records so that what may be a unique field in one table can appear multiple times along with similar data from another table. The HireAgreement fields aren't in either primary table thus logic dictates they don't belong in a junction table? Again, likely belong in a contract table. Seeing the need for one yet?

    I seem to recall posting that a solution might be to join the service table to itself in a query and get the TOP 1 when grouped by unit and ordered by service date. While your sequence solution seems to work, I have no idea how difficult it will be to ensure the correct order. It's also why I'm not addressing your sequence questions as you might elect to abandon that approach seeing as how you already have an order - the service date. Someone else might jump in on that, but in the meantime, here's a sql statement that illustrates my point. Dump it into a new query in your db and see what you think. I did some line wrapping in the posted sql so make sure words didn't get jammed together. These work for me:

    Code:
    SELECT ForkliftServiceTbl.ForkliftID_Fk, ForkliftServiceTbl.ServiceDate, ForkliftServiceTbl.OperationHours, 
    (SELECT TOP 1 OperationHours FROM forkliftservicetbl as T2 WHERE T2.serviceDate > forkliftserviceTbl.serviceDate) 
    AS OperationHrs2, Format([operationhrs2]-[forkliftservicetbl].[operationhours],"Fixed") AS [Usage]
    FROM ForkliftServiceTbl INNER JOIN ForkliftServiceTbl AS T2 ON ForkliftServiceTbl.ForkliftServiceID_Pk = T2.ForkliftServiceID_Pk
    ORDER BY ForkliftServiceTbl.ForkliftID_Fk, ForkliftServiceTbl.ServiceDate;
    Also, here is a free lesson on using table aliases to shorten long table names that are repeated numerous times:
    Code:
    SELECT T1.ForkliftID_Fk, T1.ServiceDate, T1.OperationHours, (SELECT TOP 1 OperationHours FROM ForkliftServiceTbl AS T2 
    WHERE T2.serviceDate > T1.serviceDate) AS OperationHrs2, Format([operationhrs2]-T1.[operationhours],"Fixed") AS [Usage]
    FROM ForkliftServiceTbl AS T1 INNER JOIN ForkliftServiceTbl AS T2 ON T1.ForkliftServiceID_Pk = T2.ForkliftServiceID_Pk
    ORDER BY T1.ForkliftID_Fk, T1.ServiceDate;

  14. #29
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Consider something like: (based on comments by Micron)

    Click image for larger version. 

Name:	Relationship2.png 
Views:	11 
Size:	94.9 KB 
ID:	36518

  15. #30
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Looks nice, but only allows one contact in one area per customer? If that's even wanted, I think I see that as tblAreaContact joined on ForkliftIDfk. Not sure if CustomerIDfk also required or if you can go through junction table to get customer data from CustomerTbl. As mentioned, my paradigm is that one supplier could be interacting with one customer who has many areas, each with a machine and a related contact.

    Just about ready to post that work of art?

Page 2 of 3 FirstFirst 123 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 9
    Last Post: 09-10-2018, 02:24 PM
  2. Replies: 7
    Last Post: 06-02-2016, 08:23 AM
  3. Calculate difference from last value
    By dilbert in forum Reports
    Replies: 4
    Last Post: 01-17-2016, 08:06 AM
  4. Calculate time difference
    By desireemm1 in forum Access
    Replies: 19
    Last Post: 10-27-2014, 12:06 PM
  5. Replies: 8
    Last Post: 07-07-2014, 11:21 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