Expand all | Collapse all

Relationship Issue

  • 1.  Relationship Issue

    Posted 04-18-2017 20:36
    For this question I have 3 Tables: Associates, Travel and Oracle. What I need to do is the following. The Associates to Travel is fully functional, so I have travel going to City ABC and X,Y,Z associates are traveling. 
    What I can't seem to do is get the Oracle Table to tell me how many hours associates X,Y, and Z worked in city ABC.

    Master Table - Associates
    Employee ID (Key)

    Details Table - Travel
    Related Associates (Reference)

    I must be obtuse today because I cannot seem to figure out to get the Oracle Table in the "mix"

  • 2.  RE: Relationship Issue

    Posted 04-18-2017 20:50
    The type of relationship really depends on what you are looking to do with the data and how you want to display it.

    Can you explain your goal with the "Oracle" table?  What kinds of reports are you looking for, user experience, etc?

  • 3.  RE: Relationship Issue

    Posted 04-19-2017 12:32
    I was trying to get this "out the door" before I left for the day, so I will try to clarify and address your response.
    The process begins with Field Management creating a Reset (this is a job at a specific location, specific date etc...). At some point during the planning phase they will complete the Travel portion (this is where they assign the associates (from the Associates Table) to work the Reset)
    At the end of each pay period I receive an Oracle Report, that I want to upload into the table called Oracle. The columns are: Employee ID, this is a distinctive number in the Associates Table, but is not in the Oracle Table. As an associate can have multiple listings each day; clock in, meal out, meal in, clock out etc... Date, Element (type of pay), Hours.

    What I would like to achieve is; for the hours from the Oracle Table to "rollup" to the Reset Table - this would have to "pass-through" the travel table as it holds the EmpID's. 
    The relationship I have are:

    Does this help in explaining....?

  • 4.  RE: Relationship Issue

    Posted 04-19-2017 15:48
    This is going to be tricky for several reasons;

    1- It has to be data specific, so it applies to the appropriate "Reset".
    Does the Oracle report have day to day reporting, every punch, or just a summary?
    (Each Reset --> (has many) Oracle Entries)

    2- It also has to be specific the the Employee ID.
    Which can be related to the associate no problem, and might even be automatic if your key field is the ID, but trying to tag those Oracle lines to the travel also presents a few problems, as it has to be conditional on "ID" AND "Date" AND (possibly) a "Reset".
    Does the Oracle report have the "Reset" listed?  Is there a Unique ID for the Reset as well.
    ( each Associate --> Oracle Entries)

    3- if you can create some concatenated field on the "Travel" table, you could say each "Travel --> Oracle Entries".  Then make that connection conditional, but this would still be a manual process.
    (i.e. Combine Associate ID and travel Date, to compare to a similar field on the Oracle table)

    Basically you will connect the Oracle table as a child to all 3 tables,  however making the connection automatic greatly depends on your data.  It might require some type of script to connect it all the right way.

  • 5.  RE: Relationship Issue

    Posted 04-24-2017 13:33
    Finally able to get back to this; the other part of work got in the way...
    Master Table - Associates is connected to the Details Table - Travel.
    EmpID is common denominator
    Embedded report that provides me with all the travel an associate has made... so I know this is good to go.

    Master Table - Associates is connected to the Details Table - Oracle.
    EmpID is once again the common denominator.
    Embedded report that provides me with all the time punches an associate has made... so I know this is good to go.

    What I can't seem to figure out is how to get the three tables to really talking to one another in order to provide the reporting that is necessary.
    The "Time" is the "Oracle" table
    I can do this in Excel with this formula; =SUMPRODUCT((Time!EmpID=Travel!EmpID)*(Time!ElementName=Travel!ElementName)*(Time!Date>=Travel!Date)*(Time!Date<=Travel!Date),Time!Hours).
    But for the life of me I cannot get it to work in QB

  • 6.  RE: Relationship Issue

    Posted 04-25-2017 03:42
    If each Reset only had one day, you could use a combination (concatenation) of the Date & EmpID to automatically associate the time entires... But because you have a multi day span, you would basically need to create "Reset Days" as a child record.  So if the reset went 5 days, you'd need a button or a script that would create 5 child records.

     Then you can associate them all automatically.

    Most things QB does great, but sometimes Excel wins. (I can't believe I just said that)

  • 7.  RE: Relationship Issue

    Posted 04-27-2017 12:37
    Matthew, appreciate the help here. I have a lot more on my plate than just this one issue and have decided for now I will keep it in Excel... sure I will revisit this again in the future. Once again thank you for the input.