Combining Data Systemically

[jsnkid]

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

 

[MajP]

Your tables should look like this.

tblItems (item is Primary key)

Item Class Category Type
A A's Class A's Category A's Type
B B's Class B's Category B's Type
C C's Class C's Category C's Type
D D's Class D's Category D's Type
E E's Class E's Category E's Type

tblData (itemIDfk is a foreign key)
itemIDfk data Date
A somedata 1/1/2010
B somedata 1/1/2010
C somedata 1/1/2010
D somedata 1/1/2010
E somedata 1/1/2010
A somedata 3/1/2010
B somedata 3/1/2010
C somedata 3/1/2010
D somedata 3/1/2010
E somedata 3/1/2010
...

Now you just append records to tblData. This assumes the items are basically constant. As you said a crosstab can then show this in a tabular format.

 

[jsnkid]

MajP,

That is basically how I do it now... But with thousands of items stacking on top of each other, it is incredibly slow, especially a few months in.

I am looking for a way to keep constant information like category, item descriptions, etc... and just add the new sales data as additional columns.

Thanks though!

-Jeff

 

[boblarson]

...and just add the new sales data as additional columns.

RED FLAG, RED FLAG!!! New sales data should not be adding COLUMNS but instead should be adding ROWS of data (I hope you meant that and just spaced).

Bob Larson
FORMER Microsoft Access MVP (2008-2009, 2009-2010)
Free Tutorials/Samples/Tools:

http://www.btabdevelopment.com