Summarize multiple records

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

My crews check out tools and return them.  Every time they check out or return a tool, they create a new record.  So...the tool is either incoming or outgoing.  The problem arises when the check in/out process is bypassed and the tool is checked out when it was already out.  I then have two records that are Outgoing.  I have converted the incoming to a (-1) and the outgoing to (1).  In a perfect world, this has allowed me to summarize wither it is out or in based on the sum of a 1 or 0 value.  When they don't break the process and don't check it in or out in order, I then have a value of 2 or 3 or even -2 which messes up the sum report.   Bottom line...I need to be able to accurately get a report that looks at the last entered record and tells me if it is "in" or "out."  I just can't seem to find a way to properly summarize.  

Photo of Joshua

Joshua

  • 0 Points

Posted 4 years ago

  • 0
  • 1
Photo of Ceci New York

Ceci New York

  • 0 Points
You didn't mention your table structure - but let's assume that you have the following:

2 tables

- "Tools"

- "Inventory Records" - with the Numeric field [Inventory Action] = 1 (Incoming), or -1 (Outgoing)

Relationship:  1 Tool has many Inventory Records

First - if you have an embedded report of Inventory Records for each Tool - then you can have that sorted oldest to newest (by Date Created, or [Date] of checkout / return) - to see whether the latest Inventory Record was of [Inventory Action] "1" or "-1".

If you want to see that at the Tool level - we have to bring the value of the latest [Inventory Action] up to the Tool level.

1)  From the Tool table - make a summary field of the Maximum [Record ID] of Inventory Record

- call this [Latest Inventory Record - Record ID]


- this is Record ID of the latest Inventory Record for this tool



2)  From the Inventory Records table - make a lookup field of the [Latest Inventory Record - Record ID]

- call this [Latest Inventory Record - Record ID]




3)  From the Tool table - make a summary field of the Total [Inventory Action]


- call this field [Latest Inventory Action]


Total of [Inventory Action - Number] in Inventory Records


where [Record ID#] is equal to the value in the field [Latest Inventory Record - Record ID]



The [Latest Inventory Record - Record ID] will tell you which "Inventory Record" is the latest one for that Tool.


So now - at the Tools table - you can run a report, and see for each Tool, the [Latest Inventory Action] - it'll be -1 for Incoming, and 1 for Outgoing.

There are variations possible on this (using a Latest [Date] instead of the Maximum [Record ID#], etc.) - but hopefully this is close to what you're trying to do.