More than 65,000 lines and ACCESS

[matthew kramer]

Hi,

I'm using the reverse pivot technique suggested by Debra Dalgleish to
create a list dataset from which I can make a pivot table. The only
problem is that because there are so many years going across, whenever I
try to do this the computer tells me it was only able to do an
incomplete list because the full list exceeds 65,000 lines.

Apart from having to break the dataset down into smaller datasets, is
there some way that I could hook up the original dataset to an Access
database and do the reverse pivot technique successfully that way in
order to get a complete dataset list for the pivot table?

There are about 100 years going across.

Any suggestions would be much appreciated. Many thanks.

Matthew Kramer

 

[Tom Ogilvy]

The limitation is the output, not the input. How would using Access as a
data source (if it works, I don't know for this technique) and produces more
than 65K lines, how would that be accomodated by Excel, because
that is where the output must be placed.

On the other hand, A macro could probably be done to generate the output and
put it in Access.

When you say reverse pivot technique, I am thinking of a method on John
Walkenbach's site (not aware of one on Debra's so you could post the URL).
In any event if whatever technique produces more rows of output than Excel
has, then the limitation is Excel as the output.

 

[Jamie Collins]

A macro could probably be done to generate the output and
put it in Access.

Take a look at this thread:

http://groups-beta.google.com/group..._frm/thread/2388f7501a2311e0/6cd656705433579c

If you could post some more details, including a sample extract of your
data, I would be able to adapt this approach for your circumstances.
Jamie.

--

 

[matthew kramer]

Jamie and Tom,

Thanks for the responses. Yes, the reverse pivot technique I'm
referring to is the one of Walkenberg, tip number 68 to create a
database table from a summary table.

Jamie, this is a sample of the structure of the dataset I'm working
with:

The values in the table are number of employees
companyname 1900 1901 1902 1903 etc. years go across
Pepsi 8988 2342 8785 2772 etc.
Nike etc.
McDonalds

There are about 3000 lines going down and 100 years going across or
about 100 columns.

When I do the reverse pivot technique, excel takes care of the first
65000 lines but then gives the message:
"Not all source records have been copied".

I've tried breaking down the dataset into smaller sizes, but keep coming
up with the same message.

Is there some way to do this reverse pivot technique successfully to
have the complete data set by using excel alone or in combination with
Access?

A macro which could put all the lines in a spreadsheet or even dispersed
among several spreadsheets within an excel workbook would be great. Any
other suggestions on how to do this, it would also be much appreciated.

Thanks.

Matthew Kramer

 

[Jamie Collins]

Jamie, this is a sample of the structure of the dataset I'm working
with:

The values in the table are number of employees
companyname 1900 1901 1902 1903 etc. years go across
Pepsi 8988 2342 8785 2772 etc.
Nike etc.
McDonalds

There are about 3000 lines going down and 100 years going across or
about 100 columns.

I've assumed, among other things, that the data as posted is on Sheet1
in range A15 and begins with the year 1900. For test purposes, the
code creates a database and two tables to receive the data:

Sub test()
Dim Cat As Object
Set Cat = CreateObject("ADOX.Catalog")
Cat.Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data source=" & ThisWorkbook.Path & "\New_Jet_DB.mdb"

With Cat.ActiveConnection
' .Open
.Execute _
"CREATE TABLE Companies (" & _
" company_name VARCHAR(255) NOT NULL," & _
" CONSTRAINT pk__companies PRIMARY KEY (company_name));"
.Execute _
"CREATE TABLE Employees (" & _
" company_name VARCHAR(255) NOT NULL," & _
" stat_year INTEGER NOT NULL," & _
" employee_count INTEGER DEFAULT 0 NOT NULL," & _
" CONSTRAINT pk__employees PRIMARY KEY (company_name,
stat_year)," & _
" CONSTRAINT ck__ee_count_pos CHECK (employee_count >= 0)," & _
" CONSTRAINT ck__ee_stat_year CHECK (stat_year BETWEEN 1800 AND
2100)," & _
" CONSTRAINT fk__employees__companies FOREIGN KEY" & _
" (company_name) REFERENCES Companies (company_name));"
.Execute _
"INSERT INTO Companies (company_name)" & _
" SELECT F1 AS company_name FROM" & _
" [Excel 8.0;HDR=NO;Database=C:\db.xls;].[Sheet1$A2:IV65535];"

Dim strColName As String
Dim lngCounter As Long
For lngCounter = 0 To 3 ' change to 99 or whatever
strColName = "F" & CStr(lngCounter + 2)
.Execute _
"INSERT INTO Employees" & _
" (company_name, stat_year, employee_count)" & _
" SELECT F1 AS company_name," & _
" " & CStr(1900 + lngCounter) & " AS stat_year," & _
" " & strColName & " AS employee_count FROM" & _
" [Excel 8.0;HDR=NO;Database=C:\db.xls;].[Sheet1$A2:IV65535]" & _
" WHERE NOT (" & strColName & " = 0" & _
" OR " & strColName & " IS NULL);"
Next

.Close
End With
End Sub

Jamie.

--

 

[matthew kramer]

Jamie,

It's very belated (was called out of town unexpectedly), but just wanted
to thank you for the VB script on that.

Best regards,
Matthew