If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Combining data from multiple fields into Pivot table
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. |
#2
|
|||
|
|||
Combining data from multiple fields into Pivot table
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" wrote in message ... 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. |
#3
|
|||
|
|||
Combining data from multiple fields into Pivot table
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. -----Original Message----- 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" wrote in message ... 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. . |
#4
|
|||
|
|||
Combining data from multiple fields into Pivot table
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" wrote in message ... 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. -----Original Message----- 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" wrote in message ... 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. . |
#5
|
|||
|
|||
Combining data from multiple fields into Pivot table
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. -----Original Message----- 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" wrote in message ... 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. -----Original Message----- 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" wrote in message ... 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. . . |
#6
|
|||
|
|||
Combining data from multiple fields into Pivot table
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 "Os" wrote in message ... 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. -----Original Message----- 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" wrote in message ... 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. -----Original Message----- 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" wrote in message ... 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. . . |
Thread Tools | |
Display Modes | |
|
|