JCapper Message Board

JCapper 101
-- Impact Values Table Interface

Home	Register Log In

Impact Values Table Interface

TPW
8/25/2018
6:55:09 AM

Jeff,

I've collected quite a few entries in a variety of "Group Names" and it is quite difficult to massage some of the entries as they are ordered first by "Group Name" and then by "Factor". Is there a way to export this table to Excel so that I can sort by "Track" make my adjustments and then import back to JCapper?

Terry

jeff
8/25/2018
1:42:59 PM

--quote:

"Is there a way to export this table to Excel so that I can sort by "Track" make my adjustments and then import back to JCapper?"

--end quote

Absolutely.

Before getting started make a backup of your c:\2004\JCapper.mdb file (just in case.)

After that -- follow these steps:

1. Click System Settings and launch the JCX File Exports Module. Link to help doc here.

2. In the JCX File Exports Module:

Click MENU and select Connect to JCapper.mdb File. Then use the dialog box to connect to your c:\2004\JCapper.mdb file.

Beneath the blue "Action" header on the JCX File Exports Module: Check the Use Custom SQL Expression Box to open the SQL Expression Tool.

In the SQL Expression Tool, paste or key the following sql expression:

SELECT * FROM ImpactValues order by GroupName, Track, Factor

Then hit the APPLY button and answer Yes at the prompt.

Key a filename (or better yet the desired filepath and filename) of your output .csv file into the Output Filename Box.

Example:

c:\JCapper\Exe\impactvalues-table.csv

Finally, click the Create Export button to run the Export -- which in this case will write data from your ImpactValues table sorted by GroupName, Track, and then Factor to a .csv file on your hard drive.

That's it!

From there you should be able to double click the .csv file to open it in Excel...

And from there you should be able to paste it into an Access table.

-jp

.

TPW
8/26/2018
9:30:37 AM

Thank you Jeff. Is this process possible assuming that I’m running in Playlist and not SQL mode?

Terry

jeff
8/26/2018
11:41:14 AM

Yes. It's possible to do this while operating JCapper in Playlist File Mode.

BUT, after having a day to think over what I posted --

My post involves exporting data (or selected data) from the ImpactValues table to .csv file.

Followed by opening the .CSV file in Excel and making edits to the entries driving UserFactors and UPR in Excel...

Followed by opening up the ImpactValues table in Access - or another third party tool like Open Office...

Followed by pasting data from Excel directly into the ImpactValues table while it's open in Access - or a third party tool like Open Office...

While doable --

In order for that to work:

The columns in Excel absolutely need to be in the exact same order -- left to right -- as the columns in the ImpactValues table itself.

Otherwise, you're going to completely hose your UserFactors and UPR.

If you're up for that -- and provided you have a good backup of your c:\2004\JCapper.mdb file first --

Then it's doable.

Otherwise, it's not doable.

That said, the latest version of UPR Tools does have its own SQL Expression Tool.

It's programmed for the IVLookup table... an IVLookup Table Interface if you will.

If U can hold off a few days --

I think I can program the SQL Expression Tool in UPR Tools to launch the ImpactValues Table Interface using a custom (or user defined) sql expression --

Which would allow you to sort the records in the ImpactValues table as you see fit.

-jp

.

TPW
8/26/2018
12:20:41 PM

Appreciate the consideration Jeff. Definitely can wait.

Terry

JCapper Message Board

JCapper 101 -- Impact Values Table Interface

JCapper 101
-- Impact Values Table Interface