Page 3 of 3 FirstFirst 123
Results 31 to 36 of 36
  1. #31
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Oops. I forgot about that. I wasn't sure what the "Area" field was for. Is it for where the customer (company) is located or where the forklift will be located?


    The dB is just empty tables, so......... here it is... FWIW.
    Attached Files Attached Files

  2. #32
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    We seem to be coming up with a great design - one that might not even fit the needs of the user!
    From my viewpoint, the customer/area thing can be described as follows:
    I worked for a steel manufacturer. The plant had forklifts in Purchasing, Hot Mill, Cold Mills (several), Casters (2), Research, Steelmaking, Blast Furnaces, Coke Ovens, Scrap Handling, Tin Mill, Utilities... you probably get the point. Each area had phone numbers and names for at least 1 primary and 1 secondary contact person. The area info was primarily related to the forklift unit, and this info included gate and door numbers as well. This way, if the unit moved to another area, it was relatable to the gate, door and contact info rather than just the parent company name, which as you can see, wouldn't have worked very well. A junction table for this would keep the historical location data and I suppose, permit different contract rates that could be adjusted as the unit moved around. I remember that they pretty much beat the crap out of the units at the blast furnace. Not because people were destructive, but because it was an extremely rugged/hot/dirty environment. The contract was with the parent company.

    I'll take a look at your db.
    P.S. - on my second one at the moment.

  3. #33
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    All this started because in the original design, it was almost impossible to calc run time hours. (I think)

    Long day, time to crawl to bed......


    starkle, starkle, little twink
    who the heck you are you think?
    I'm not under the alcholflunance of inkahol like some think people I am
    but the more I drunk, the sitter I get
    so starkle, starkle, little twink!

  4. #34
    PeterS is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Posts
    45
    Quote Originally Posted by Micron View Post
    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;

    Thank you for that, I think it would be much simpler doing it this way rather than using a sequence number. I have played with the SQL statement as I have noticed that it only produces correct results for the first three rows on forkliftID 15...there after it seems to duplicate the results

  5. #35
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    @Micron, or just Micron - would be much better than "Reply With Quote" most of the time. Especially when you're quoting a novel.
    If your post relates to more data than was in the sample table, then rather than me create my own data, post some of what you're referring to. The problem may be the data. It might also be the query. Me providing my own data may not allow me to reproduce the effect. Please don't post a picture of it. You can copy from a table or spreadsheet and paste into a post which will create a table in your post.

    EDIT - Never mind; I see that there was enough data in the last db you posted. I also see that the solution was not in the way I joined or didn't join fields. What looks much better:
    Code:
    SELECT T1.ForkliftID_Fk, T1.ServiceDate, T1.OperationHours, (SELECT TOP 1 OperationHours FROM ForkliftServiceTbl AS T2 
    WHERE T1.ForkliftID_Fk = T2.ForkliftID_Fk AND 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;
    Sorry for not paying more attention

  6. #36
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I started a new thread related to a db design for this as it seems to be drifting off of the original issue

    https://www.accessforums.net/showthread.php?t=74855

Page 3 of 3 FirstFirst 123
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