Hi,
I have a very large excel file that is updated monthly and left in a compacted pivottable. It has confidential information that obviously can't be shared but needs to be parsed for each individual employee and emailed in a table format so that it is easy to pull into excel or Access. I have tried two approaches. One convert the pivot table to classic view and remove subtotals and grand totals in Excel. It also fills down so that each transaction(this a record of each sale by sales person, supplier, etc.) has the salesperson it is for. I have been trying to find away to link the excel file to Access without manipulation in excel first but haven't been able to figure that out. This is a tool I am building for a non tech savy individual that can easily be used with little manipulation. The form in access I would like to have would allow the salespersons name be selected and the date range possibly in a split table with a option to select which items can be included plus a button to email to the person selected in the combo box. There are 26 different items from date to salesperson to commission before split to units and so forth.
Could someone impart their wisdom on how best to handle this? Given the vast amount of posts I am guessing something is already posted but I am not correctly searching for the name.
I also tried linking the spreadsheet and then runnng a table analyzer but i ran into reg errors. This is two years of data which equates to over 65k individual records in Compact mode. I think it will be less once the subtotals and grandtotals are removed.
I wish I could post a visual but I can't. Envision something like this( i hope the formating works out)
Joe Schmoe (next column>) date (next column>) invoice number(next column>) qty sold (next column>) price (next column>)total
-> Customer name
->-> Supplier name
->->-> part number 1
->->->part number2
->-> supplier number2
->->->PN 1
->->-> PN 2
-> Customer #2
->->Supplier name
->->-> part number 1
->->-> part number2
->-> supplier namer2
->->->PN 1
->->-> PN 2
Tommy Hilfiger
->Customer name
->-> Supplier name
->->-> part number 1
->->-> part number2
->->supplier number2
->->-> PN 1
->->-> PN 2
->Customer #2
->->Supplier name
->->->part number 1
->->-> part number2
->->supplier number2
->->-> PN 1
->->-> PN 2
Thanks in advance,
Razzbarry
I have a very large excel file that is updated monthly and left in a compacted pivottable. It has confidential information that obviously can't be shared but needs to be parsed for each individual employee and emailed in a table format so that it is easy to pull into excel or Access. I have tried two approaches. One convert the pivot table to classic view and remove subtotals and grand totals in Excel. It also fills down so that each transaction(this a record of each sale by sales person, supplier, etc.) has the salesperson it is for. I have been trying to find away to link the excel file to Access without manipulation in excel first but haven't been able to figure that out. This is a tool I am building for a non tech savy individual that can easily be used with little manipulation. The form in access I would like to have would allow the salespersons name be selected and the date range possibly in a split table with a option to select which items can be included plus a button to email to the person selected in the combo box. There are 26 different items from date to salesperson to commission before split to units and so forth.
Could someone impart their wisdom on how best to handle this? Given the vast amount of posts I am guessing something is already posted but I am not correctly searching for the name.
I also tried linking the spreadsheet and then runnng a table analyzer but i ran into reg errors. This is two years of data which equates to over 65k individual records in Compact mode. I think it will be less once the subtotals and grandtotals are removed.
I wish I could post a visual but I can't. Envision something like this( i hope the formating works out)
Joe Schmoe (next column>) date (next column>) invoice number(next column>) qty sold (next column>) price (next column>)total
-> Customer name
->-> Supplier name
->->-> part number 1
->->->part number2
->-> supplier number2
->->->PN 1
->->-> PN 2
-> Customer #2
->->Supplier name
->->-> part number 1
->->-> part number2
->-> supplier namer2
->->->PN 1
->->-> PN 2
Tommy Hilfiger
->Customer name
->-> Supplier name
->->-> part number 1
->->-> part number2
->->supplier number2
->->-> PN 1
->->-> PN 2
->Customer #2
->->Supplier name
->->->part number 1
->->-> part number2
->->supplier number2
->->-> PN 1
->->-> PN 2
Thanks in advance,
Razzbarry
