Is there a way to use pivot tables in Excel to accomplish the same result as
an MS-Access process to "join" two tables on matching fields? For example,
I have one range of data that contains employee time card information that
includes a costing code for each time entry. I have a separate data range
that contains, for each costing code, one or more records containing
percentages that are used to allocate labor to specific business units. The
desired result is a table or data range that contains all of the records from
the time card range and all matching records from the allocation table.

I've been able to build the result that I want using VBA code and vlookups
but I'm looking for a better way. I've been researching Data consolidations
and pivot tables with multiple ranges to see if this might work. So far, it
seems that these processes in Excel are simply to combining data rather than
simulating a join. Is there a way to do this with pivot tables or is code
the only answer?

Thanks in advance for any input!!!

Glad to hear it was useful.

Tim


"Gr8lyConfused" wrote in message
...

Tim, I got this to work like I wanted on test data! This is powerful,
powerful stuff. I experimented with various types of SQL statements to
select and sum data ranges that I can use in other places in the
application
as well. This certainly seems far superior to the VBA coding that I was
doing that, at times, seemed like I was building the result one brick at a
time. Thanks again!!!!!

"Tim Williams" wrote:

"Microsoft ActiveX Data Objects 2.7 Library"

Earlier versions (eg. 2.5, 2.6) should also work.

Tim

I'm using the SQL approach recommended above in a variety of ways to match,
update and sum data in ranges. While I'm close to getting the results I
want, a problem has come up that I wasn't experincing prior to using ADO/SQL.
The problem is that, while testing the program, I often have to delete a
few thousand records in my worksheet prior to testing a modified version of
the program. Deleting these records now takes an extraordinarily long time.
Sometimes an hour or longer. So long that I've tried to use cntl-break to
break out of the code to no avail. After the records finally delete and I
try my program again, Excel seems to be randomly recognizing cntl-breaks to
stop execution of the VBA code when I haven't hit a cntl-break. Re-starting
Excel doesn't seem to help. Re-starting Window (XP) does seem to get rid of
the cntl-break problem but doesn't help with the time issue of deleting
records. One more piece of background information is that my VBA code is
getting quite lengthy. Since I'm re-working an existing program, I've copied
all of the original subs from my old program into the new one so that any
subs that I can reuse are readily available. My questions a

1) Are the problems that I'm experiencing more likely related to using ADO
or the size of the program?

2) If the cause is likely using ADO, is there something that should be done
to free or clean up memory after each use to keep from causing performance
issues with deleting records?

Any help would be greatly appreciated.

Thanks in advance for your help.

"Tim Williams" wrote:

Glad to hear it was useful.

Tim


"Gr8lyConfused" wrote in message
...

Tim, I got this to work like I wanted on test data! This is powerful,
powerful stuff. I experimented with various types of SQL statements to
select and sum data ranges that I can use in other places in the
application
as well. This certainly seems far superior to the VBA coding that I was
doing that, at times, seemed like I was building the result one brick at a
time. Thanks again!!!!!

"Tim Williams" wrote:

"Microsoft ActiveX Data Objects 2.7 Library"

Earlier versions (eg. 2.5, 2.6) should also work.

Tim



.

Hard to say exactly what's going on. How are you deleting your records ?
Do you have any sheet-level event handlers which might be firing during the
delete ?

If you're running the delete via code, try turning off Events and
Calculation before doing the delete, then turn them back on after.

with Application
.EnableEvents = False
.Calculation = xlCalculationManual
.Screenupdating = False
end with

'do deletes

with Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.Screenupdating = True
end with

If that doesn't work you'll need to provide some code.

If you'd like to mail me the workbook I will take a look.
timxxjxxwilliams (remove the x's) at gmail.com

Tim



"Gr8lyConfused" wrote in message
...

I'm using the SQL approach recommended above in a variety of ways to
match,
update and sum data in ranges. While I'm close to getting the results I
want, a problem has come up that I wasn't experincing prior to using
ADO/SQL.
The problem is that, while testing the program, I often have to delete a
few thousand records in my worksheet prior to testing a modified version
of
the program. Deleting these records now takes an extraordinarily long
time.
Sometimes an hour or longer. So long that I've tried to use cntl-break
to
break out of the code to no avail. After the records finally delete and I
try my program again, Excel seems to be randomly recognizing cntl-breaks
to
stop execution of the VBA code when I haven't hit a cntl-break.
Re-starting
Excel doesn't seem to help. Re-starting Window (XP) does seem to get rid
of
the cntl-break problem but doesn't help with the time issue of deleting
records. One more piece of background information is that my VBA code is
getting quite lengthy. Since I'm re-working an existing program, I've
copied
all of the original subs from my old program into the new one so that any
subs that I can reuse are readily available. My questions a

1) Are the problems that I'm experiencing more likely related to using ADO
or the size of the program?

2) If the cause is likely using ADO, is there something that should be
done
to free or clean up memory after each use to keep from causing performance
issues with deleting records?

Any help would be greatly appreciated.

Thanks in advance for your help.

"Tim Williams" wrote:

Glad to hear it was useful.

Tim


"Gr8lyConfused" wrote in
message
...

Tim, I got this to work like I wanted on test data! This is powerful,
powerful stuff. I experimented with various types of SQL statements to
select and sum data ranges that I can use in other places in the
application
as well. This certainly seems far superior to the VBA coding that I
was
doing that, at times, seemed like I was building the result one brick
at a
time. Thanks again!!!!!

"Tim Williams" wrote:

"Microsoft ActiveX Data Objects 2.7 Library"

Earlier versions (eg. 2.5, 2.6) should also work.

Tim



.

Tim, I'm not aware of any sheet events that are programmed into my code.
But, I think you're right that the cntl-break issue is related to that
somehow. One of the places that the code would break was on"Loop" statement
after timer related DoWhile section looped around a DoEvents statement. So,
it has to be related to events.

I turned on manual calculation for the spreadsheet and the time required to
delete records seem to have returned to normal for the time being anyway.
If the problem shows up again I'll post some code. Thank you for your help
and expertise.

Rick


"Tim Williams" wrote:

Hard to say exactly what's going on. How are you deleting your records ?
Do you have any sheet-level event handlers which might be firing during the
delete ?

If you're running the delete via code, try turning off Events and
Calculation before doing the delete, then turn them back on after.

with Application
.EnableEvents = False
.Calculation = xlCalculationManual
.Screenupdating = False
end with

'do deletes

with Application
.EnableEvents = True
.Calculation = xlCalculationAutomatic
.Screenupdating = True
end with

If that doesn't work you'll need to provide some code.

If you'd like to mail me the workbook I will take a look.
timxxjxxwilliams (remove the x's) at gmail.com

Tim



"Gr8lyConfused" wrote in message
...

I'm using the SQL approach recommended above in a variety of ways to
match,
update and sum data in ranges. While I'm close to getting the results I
want, a problem has come up that I wasn't experincing prior to using
ADO/SQL.
The problem is that, while testing the program, I often have to delete a
few thousand records in my worksheet prior to testing a modified version
of
the program. Deleting these records now takes an extraordinarily long
time.
Sometimes an hour or longer. So long that I've tried to use cntl-break
to
break out of the code to no avail. After the records finally delete and I
try my program again, Excel seems to be randomly recognizing cntl-breaks
to
stop execution of the VBA code when I haven't hit a cntl-break.
Re-starting
Excel doesn't seem to help. Re-starting Window (XP) does seem to get rid
of
the cntl-break problem but doesn't help with the time issue of deleting
records. One more piece of background information is that my VBA code is
getting quite lengthy. Since I'm re-working an existing program, I've
copied
all of the original subs from my old program into the new one so that any
subs that I can reuse are readily available. My questions a

1) Are the problems that I'm experiencing more likely related to using ADO
or the size of the program?

2) If the cause is likely using ADO, is there something that should be
done
to free or clean up memory after each use to keep from causing performance
issues with deleting records?

Any help would be greatly appreciated.

Thanks in advance for your help.

"Tim Williams" wrote:

Glad to hear it was useful.

Tim


"Gr8lyConfused" wrote in
message
...

Tim, I got this to work like I wanted on test data! This is powerful,
powerful stuff. I experimented with various types of SQL statements to
select and sum data ranges that I can use in other places in the
application
as well. This certainly seems far superior to the VBA coding that I
was
doing that, at times, seemed like I was building the result one brick
at a
time. Thanks again!!!!!

"Tim Williams" wrote:

"Microsoft ActiveX Data Objects 2.7 Library"

Earlier versions (eg. 2.5, 2.6) should also work.

Tim



.



.