Combining data from multiple fields into Pivot table

[Os]

I have two pivot tables that pull data from Access dB.
This is for a warehouse productivity and accuracy. Each
team consists of two employees: Picker and Loader. We
need to track employees accuracy when it comes to loading
cases. Both members of a team receive the same percentag
when working together. But many times different teams
have different members. I set up one pivot table that
shows cases by Picker, and another table that shows cases
by Loader. But I need to show the employee overall
accuracy. In other words I need to combine all their data
together and get one perecntage whether they picking or
loading. By the way, the data entered into two different
fields in the table (pickerCases and LoaderCases)
Any help is greatly appreciated.

 

[Bernie Deitrick]

Os,

In your database, you should use a data setup like this, where you and I
have worked together twice, once each as Picker and once as Loader:

EmployeeName Job Accuracy
Bernie Picker 100%
Os Loader 100%
Os Picker 95%
Bernie Loader 95%

Then you could get the overall averages from one pivot table.

The other way is to create your two pivot tables to etract percentages and
counts, then copy and combine the pivot tables into one database, which you
can then use as the source of a third pivto table.

HTH,
Bernie
MS Excel MVP

 

[Os]

Thanks Bernie. Actually I have a Form in Access that
contains over 20 controls. Some are comboboxes with
dropdown lists like weeks, dates, shifts, empnum, etc.
others are textboxes where the user enter values such as
cases loaded and so forth. Right now the amount of cases
loaded is entered only once while both picker/loader
entered. So on the table will show the same amount of
cases next to both picker/loader names. I have a
calculated field in the pivot table that will do the
accuracy percentages. One person could work with multiple
people in a given day (one other person at a time). I
guess to rephrase my question, how can I have one pivot
table combine the number of cases for the same person
when he/she picking or stacking with one other person, or
multiple people?
About saving the data from both pivot tables in a dB
table then create a 3rd pivot table to retrieve data from
the new dB table.. how can I accomplish that (in steps if
don't mind).
Thanks.

 

[Bernie Deitrick]

Os,

To have just one pivot table, you need to modify your database by copying
the entire database, then appending it to the end of the exiting database,
effectively doubling its size:

Let's say you have this:

Picker Loader Value
Bernie Os 1
Os Bernie 2
John Os 3
Os John 4

After copying, you would have this:

Picker Loader Value
Bernie Os 1
Os Bernie 2
John Os 3
Os John 4
Bernie Os 1
Os Bernie 2
John Os 3
Os John 4

Then for the first half, replace the Loader's name with the job description
("Picker"), and for the second half, replace the Picker's name with the job
description ("Loader"):

Picker Loader Value
Bernie Picker 1
Os Picker 2
John Picker 3
Os Picker 4
Loader Os 1
Loader Bernie 2
Loader Os 3
Loader John 4

Change the column headings, and swap the Name and job columns of the second
half:
Name Job Value
Bernie Picker 1
Os Picker 2
John Picker 3
Os Picker 4
Os Loader 1
Bernie Loader 2
Os Loader 3
John Loader 4

Now you can use a single pivot table.

To use the two pivot tables, you need to copy both, paste as values, then
combine them into a single data set. You will lose a lot of information by
doing that, however, so the first technique would be prefered.

HTH,
Bernie
MS Excel MVP

 

[Os]

Yes, but this data is entered around the hour. That
means every day someone have to go open the table and
duplicate the data then do all the changes you 've
mentioned. I think it will require a lot of maintenance.
I'm trying to find a way to do it in Excel (Pivot table).
Like set up a formula or another calculated field that
can add up the cases (picked and loaded) for an
individual then we can calculate that individual's
overall accuracy. But I haven't figured that out yet.
Thanks.

 

[Bernie Deitrick]

Os,

If that is the case, then you should enter the data properly: two lines for
each set. Then the pivot table can be done easily and automatically. You
could use code behind your data entry form to do it, or use the worksheet
change event to modify the entered data from a single line into a two line
entry.

HTH,
Bernie
MS Excel MVP