Question: I have a large data of about 700 columns & 4000 rows relating to the sales.Great of columns(fields) are due to no. of sale-able items (About 670)
I have designed the table with each individual sale-able items as separate Fields instead of One field under Item Name as I want to view sales transaction in one record. This is to overcome disadvantage of entering data for party name, bill no, date...etc several times for sales transactions having more than one items.
But when I converted the data into Table & make use of Database functions like dsum, dget to extract the results, it slows down.Calculation is very slow,even if I open or save the excel file it takes a lot of time.
I tried pivot table but it didn't worked As I need to drag and drop almost 650 fields.
Answer:
The correct approach is what you have been avoiding, that is, to put the items names under One column. This arrangement facilitate the use of Pivot Table to generate results. What you are concerned with, to view transactions in one records can still be achieved in the Pivot Table view, instead of the raw data view. Right not, you have no choice but to re-organise everything in the other view. A macro can be very helpful for this situation.
