Link to home
Start Free TrialLog in
Avatar of SteveL13
SteveL13Flag for United States of America

asked on

VBA code to use a table to create a new temp table with a column added

I have a table that looks like this:

User generated image

And I want to have VBA code that uses that table and creates a new temp table that looks like this:

User generated image
Note the insertion of the new column.  For each row, somehow loop through and add the data in that new column from F2 until you get to the line that reads "Total:"
Note: There are other columns in the Table Example Before that follow F2 that need to be kept in the new After table too but I am not showing them just to keep the embedded images easy to read.

Can this be done?  What would the code look like?
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of SteveL13

ASKER

Because I have several reports I need to design and need the field data added to the temp table.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
<<Because I have several reports I need to design and need the field data added to the temp table.>>

 Even so, you would not put the total line in....that's the job the report engine if your turning this into some reports.   As far as the "leader" name appearing on each line, if the DB is designed correctly, you should be able to do that right in the select query.   There's no need to create a temp table just to fill a new column with the project leader name on every row.

 Keep in mind that if your actually doing this as a report, then setting up a table this way is going to make all this appear as "detail" and you'll have a tough time controlling it. What you showed is exactly the type of thing the report engine is designed to produce, but it's not what you want as input to it.

 But to answer your question, I design a table as I want it, then suffix it with "_template" in the current database.    I then use the TransferDatabase command to copy the empty template to a temp DB.

 By having a linked table entry for the temp DB/table, I can then use append queries to fill it as needed, and also select queries for reporting off of it.

 When I'm done, I delete the temp DB.    The table link though remains and as long as I don't use it, there's no problem.

Jim.