EXCEL

Compiler

• For multiple files in the same format that need to be appended into one main document.
• Set column format within the tool, tell it where the files are located and the range that should be copied from each as it loops through them.  Displays each file name that is being processed in the background, on the status bar, and shows a done message when it’s finished.

Custom Excel Function

• Like vlookup or any of those this custom function, once installed, is always available.
• What it does is write a SQL insert statement, allowing you to select as many columns as you want, entering each column’s value into the values part of the SQL statement it writes.  From there you can simply copy the results into a SQL program and run the code to create a temp table.

RTV Tool

• Plays an audio file of directions upon opening, in case people don’t read the direction.
• Allows the user to paste in large numbers of unique ids for products that need to be uploaded into the system for returns.
• Hidden tab and functions scrub the data for inconsistencies and displays errors before letting them continue.
• Once all inconsistency errors = 0 and it makes sure Access is installed on their system (error msg if not) it then allows them to the next tab where they click Verify to make sure these values are actually in the system; flagging those that are not, for them to investigate.  Once they’ve been cleaned up they are then able to click UPLOAD.  (disabled otherwise)
• The upload button opens a hidden instance of a multi-user Access DB in the background and fires off macros which pull the ID’s into the DB, run a set of queries on the values to obtain additional data, and then it exports the results, in a specifically formatted CSV, to a hot folder where an IT process picks up the file and creates a Return To Vendor order that will be sent down to stores instructing them to package up these items and send them to xyz.
• Once the process completes they receive a done message.

Title Info Plus

• Much like the RTV tool this excel tool is linked to a multi-user Access db that holds a set of macros and queries that are triggered from Excel, in the background.
• User enters unique id’s, a date range for sales data, and selects checkboxes to let the DB know which queries to run (sales, returns, purchases).
• A hidden tab scrubs the data and provides error checking before opening Access.
• Once Access pulls all of the requested data, dynamically, based on the checkboxes in excel, it then displays a message instructing the user to click the custom refresh button in Excel to view the results.

ACCESS

Order Approval System

• Buying team opens multi-user db and enters unique id for an item they want to buy.  One at a time since they need to analyze it’s past performance before making a decision.
• Upon tabbing or clicking into the next field a VBA module is triggered.  It holds the code needed to open a connection to the DW and a dynamic SQL statement that will pull all of the items relevant attributes, as well as the last 2 wks sales units and $’s for the item and quickly display them in the interface (pass thru query).  It also projects the next 2 wks units and $ based on previous weeks.
• The buyer then has the option to accept the projection as the buy, or edit.
• Buyers username and date/time is captured with each entry.
• When the deadline is hit, management opens the tool and clicks on the Managers Menu button, which is password protected.
• From there they click the Pull Submissions button.
• This button locks down the db for any users that didn’t make the deadline.  It also takes all of the submissions and pulls additional data that management needs for approving the buy quantities.  A custom excel sheet is then auto-opened.  The manager selects his/her name from a drop down which then fires off an embedded query that displays the items for all of the buyers on their team, in a clean format.
• The managers analyze the submissions and select approve or deny in the last column, and can also edit the quantities until the calculated total at the top is within their budget.
• When they’re finished they click the “save & upload” button.
• This button fires a macro to open Access in the background and pull the edited values back into Access, then creates a specifically formatted CSV file that is exported to a hot folder which an IT process is always watching.  When the file shows up, the process picks up the file and creates a purchase order and processes it.
• Status and done messages are displayed throughout the process to let the users know what’s going on.

Airport (store) RPL (return pull lists)

• Main form has buttons that open other forms for the user to set the parameters for pulling slow moving products out of the stores, down to a specific store by store target.
• User enters list of stores.
• User enters store target ending quantity.
• User sets how many weeks of supply to leave in the store for those it leaves behind, as well as dates for when items were: last sold, last received (may be new and not on sale yet, don’t want to return those), and the how long ago the last sale should be to qualify.
• Since there is a specific time of day this can be run, it first checks what time it is and stops the user if it’s not time yet.  If it is time it asks if they’re sure, yes or no, before continuing.
• Pulls all quantity on-hand’s for each store, sales, dates, etc.
• Then it uses a set of SQL ranking queries to rank them in descending order by how weak of a title they are based on the criteria set by the user.
• It then builds a table of titles to return, up to the qty and weeks of supply criteria set in the parameter screens.
• Then it formats the tables into separate specifically formatted CSV files and exports them to a hot folder where an IT process picks them up and process them accordingly.

• Status messages are displayed throughout the process.