Hello,
I am trying to streamline a warehouse inventory count process with location sensitive inventory.
In that process I want to be able to have several teams be able to record data of the physical count via form to be recorded in Table A. As there many teams, there will be several instances of the same product being recorded, sometimes even by different teams.
I will also have a Table B from which I will have my “expected” counts generated by CSV from 3rd party inventory software. Each product will only have 1 instance in this table.
The step I am having a hard time figuring out is getting, essentially a pivot table from Table A that would feed the data from each team into the “Reconcilliation” table so that each time a product is counted in Table A, it updates the total count for that team, for that product in the “Reconcilliation” rather than making a new record for each instance.
for example, say I count 10 eggs in Table A.
-1 egg is counted in location A and 2 in location B by Team 1,
-2 are in location C by Team 2,
- and 2 are counted in location D, 2 in Location E and 1 in Location F by Team 3.
My expected amount was 11 eggs in Table B
In Reconcilliation I want it to read
| T-1 | T-2 | T-3 | Act.Count | Exp. Count (Table B) | % Diff. |
| 3 | 2 | 5 | 10 | 11 | 1.1 |
