Relationship Issue

  • 1
  • 1
  • Question
  • Updated 2 years ago
  • In Progress
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"

Photo of Hans Hamm

Hans Hamm

  • 670 Points 500 badge 2x thumb

Posted 2 years ago

  • 1
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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?
Photo of Hans Hamm

Hans Hamm

  • 670 Points 500 badge 2x thumb
Matthew,
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....?
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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.
Photo of Hans Hamm

Hans Hamm

  • 670 Points 500 badge 2x thumb
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
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
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)
Photo of Hans Hamm

Hans Hamm

  • 670 Points 500 badge 2x thumb
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.