Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jan 2004
    Location
    Derry, Derry, Ireland, Northern
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    No obvious join (2003)

    OK, two tables relating to machines. Table1 is machine operators logging on and off the machine. Fields and data types:
    OperatorID - Long
    MachineID - Long
    Timestamp - Date/Time
    LogOn - Boolean (On / off)

    The operator logging on to and off the machine are recorded in table1

    Then there are machine events recorded in table2

    Timestamp
    MachineID
    EventDesc
    Duration

    The field that links the two is MachineID

    I want to produce a recordset of

    MachineID
    OperatorID
    Timestamp (from table 2)
    EventDesc
    Duration

    All entries in the tables are from the same day. Any ideas on how to achieve this, given that the timestamp fields from each table will only ever be equal by chance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: No obvious join (2003)

    Do you want to take the duration into account when determining which operator(s) to list? In other words, if an event for the Coffee Percolator starts at a TimeStamp during the shift of Evans, but ends (TimeStamp + Duration) during the shift of Jones, should only Evans be listed, or both Evans and Jones?

  3. #3
    Lounger
    Join Date
    Jan 2004
    Location
    Derry, Derry, Ireland, Northern
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No obvious join (2003)

    Thanks for the question.
    No, only the operator logged in at the timestamp point. Duration is disregarded.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: No obvious join (2003)

    I'd start by creating a series of queries to combine log on time and log off time in one record.
    You can then create a query that selects the OperatorID for which the event timestamp is between log on time and log off time.
    See the attached demo.

  5. #5
    Lounger
    Join Date
    Jan 2004
    Location
    Derry, Derry, Ireland, Northern
    Posts
    35
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No obvious join (2003)

    Hi Hans,

    Many thanks, just the direction I needed!

Posting Permissions

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