I am pretty new to access but am trying to replace a very convoluted workflow with a simpler, more efficient one. Any input would be appreciated. Here is the situation
Our computer system gives me a text file, between 6000 and 33000 lines, with about 40 columns.
Each row is an item number, and each column a piece of data
Right now I have access doing an append query which involves a few other tables and pulling in definitions of codes, calculations etc... I also input the date of the data into each row, every time it is run.
I then go to excel and use a pivot table to summarize a few key sales statistics in the columns based on those dates....
But now It is becoming extremely slow since I add thousands of lines every time.
The ideal situation would be almost like a crosstab query, but I need more info. Basically I would like to add columns every time I have a new text file, with just a few columns updated with the date as the heading. Also, since some items are added each time, new rows would be necessary with 0's for previous dates.
I am really not sure what I should be getting into whether it is a type of report, a table or a different query...
I have included a sample of what would be ideal in terms of column and row layout
If I can clarify, anything, just let me know
Thanks In advance
-Jeff
Our computer system gives me a text file, between 6000 and 33000 lines, with about 40 columns.
Each row is an item number, and each column a piece of data
Right now I have access doing an append query which involves a few other tables and pulling in definitions of codes, calculations etc... I also input the date of the data into each row, every time it is run.
I then go to excel and use a pivot table to summarize a few key sales statistics in the columns based on those dates....
But now It is becoming extremely slow since I add thousands of lines every time.
The ideal situation would be almost like a crosstab query, but I need more info. Basically I would like to add columns every time I have a new text file, with just a few columns updated with the date as the heading. Also, since some items are added each time, new rows would be necessary with 0's for previous dates.
I am really not sure what I should be getting into whether it is a type of report, a table or a different query...
I have included a sample of what would be ideal in terms of column and row layout
If I can clarify, anything, just let me know
Thanks In advance
-Jeff