I am working with a large data base that organizes data as illustrated below: 4
ID: 3560376 • Letter: I
Question
I am working with a large data base that organizes data as illustrated below: 4 columns with many more data points than illustrated below. I would like to transpose the information from the 4 column format to a table/datasheet showing one record per line for each company. I think I can use transpose in Excel to accomplish this. However, typically, each company lists over 100 lines of data with normally a max of 20 columns of values per line per year and I am working on a 10-15 year analysis of trends.
Illustration:
COMPANY ID LINE COLUMN VALUE
A 0001 1001 10
A 0001 2000 50
A 0001 5000 40
A 0001 6001 55
B 0001 1000 10
B 0001 2001 40
The desired outcome is:
COLUMN
COMPANY ID LINE 1000-1999 2000-2999 3000-3999 4000-4999 5000-5999 6000-6999 ............
A 0001 10 50 40 55
B 0001 10 40
Thank you for taking the time to read and respond to this request.
Explanation / Answer
I think a crosstab query will do it.
Post the SQL of a SELECT query and I will build tables and crosstab.
EDIT:
Your post did not include a field for year.
Create a table named Column_Range ---
Column Low High
Use your actual table and field names and include the year ---
TRANSFORM First(YourTable.[Amount]) AS FirstOfAmount
SELECT YourTable.[ID], YourTable.[Line]
FROM YourTable, Column_Range
WHERE (((YourTable.Column) Between [low] And [high]))
GROUP BY YourTable.[ID], YourTable.[Line]
PIVOT Column_Range.Range;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.