Microsoft Access and SQL Server Programming

Below is a brief listing of the software projects that I have built over the last 20 years.  I am building and programming in Access 2007+, ASP.NET, C#, VB.NET,  VBScript, Classic ASP,  SQL 2008+ and other Microsoft related products. My strength lies in database design and database application programming and web based front-end systems to SQL database applications.   

Online and Backend System for Construction Training School

This is a SQL server database feeding the website and using an Access database for backoffice operations. The construction training classes are displayed on the website and the website allows on-line registration for classes. An Access application is used by the staff administration to maintain the class schedules, attendance, testing grades and graduation. Students can be assigned to training programs with course schedules required to obtain a graduation certificate. Instructors can enter attendance and test grades on-line thru the website. Client companies can register students in bulk for classes, pay by credit card, and monitor the students attendance and interim grades thru the website. Students can also monitor their progress and transcripts thru the website. The system has many reports and report email capabilities. This is a large system designed to be used by many staff, instructors and client companies.

Real Estate Sales Management

The real estate sales company keeps track of the seller information, property information, buyers and the commission breakdown. The database is used for listing inventory reports, marketing reports, etc. There is a lot of detail in this system. Clients can request a detailed breakdown of how their property has been marketed. Inquiry calls, showings, open houses, etc. are all recorded to show clients the efforts taken to sell their property quickly. This database ties into the company website.

The real estate system that I designed and produced for ABC Real Estate was the product of several years of development. It is a pretty large system.

They have several facets of their system.

  • The primary system is the business office system which is where the staff enters the new listings and sold properties and tracks agent sales commissions, referrals, splits and brokerage earnings. It is a very comprehensive back office system for tracking information and reporting. There are several automated functions that reduce staff time and speed up the process.
  • The Office Intranet is an internal website that has many features built for the Agents use. It is also tied into the primary system. The agents use this system to enter property showings for activity reporting, schedule open houses, request graphics, check their email, etc. Information the Agents enter is saved into the primary system for use in reports and updates the website in some cases.
  • The Website is tied into the primary database and thus, the website is automatically updated as new listings, open houses, price changes, etc. and photos are entered into the back office system.
  • The Multiple Listing Service search engine is also tied into the primary system, as well as, tied into the Dallas multiple listing service database. Here is where website visitors can search the MLS listings for homes in the desired area and price range. The data and the photos for the MLS are automatically downloaded into their system from MLS.


Online Website Print and Graphics Reorder System

This system was designed for a printing company to allow their customers to order and reorder printed materials that are held in inventory. Many times a client will order more printed brochures, graphics, business cards, etc. than they have room to store these items. The printing company will hold these materials in inventory for the client. You can have maximum control and save on typesetting and design charges this way. The printing company wanted a way to let customers order their inventoried items in a easy and convenient manner. This system allows the customer to login to the printers website, see the printing inventory, and order/reorder their materials. The information is automatically entered into the printers inventory database where economic reorder points, on-hand, print jobs etc, are managed


Purchase Order System For Home Builder

This system ( I have to say ) is extremely cool.  The process for creating purchase orders for lumber for home builders can be tedious.  This system was developed for a large home builder for issuing purchase orders for lumber.  The cost savings by using this system are enormous!  The system is extremely flexible for importing material takeoff from Excel spreadsheets directly into the purchase order database.  It is not necessary to have a takeoff for every home built because the imported takeoffs can be modified and copied in the system as home plans are changed.  Another flexible aspect of this purchase order system is the way vendor pricing is imported from Excel spreadsheets.  Vendors submit their pricing for materials in a pre-defined format that is then imported directly into the database.  Once the material takeoff and the vendor pricing is imported then purchase orders can be issued.  The user creates a Job Order by selecting the takeoff, and related home plan options for a home to be built. Then the system creates a summary list of materials for that home, which the user can modify if desired.  Once the material list is approved, then the system calculates the material pricing based on the vendor pricing that was imported.  The system can recognize vendors who only server local areas versus vendors that serve national areas.  The user then can select the vendor pricing based on low total cost or by low cost code cost.  Once the vendor is selected for a purchase the user can issue the purchase orders.  One of the cool features of this system is that purchase orders can be printed or they can be emailed directly to the vendor, project manager and accounting.  It is a full featured system run by menus and forms with lots of printouts and exports to excel if desired.


Email and Microsoft Access Integration

This is a feature that I developed for another application so that email is automatically retrieved from the email server and saved in Microsoft Access tables.  The feature automatically retrieves email from a POP 3 server every 5 minutes and saves the sender's email address, subject line, body, etc, into Access tables.  The cool thing about this system is that as the application receives email, it triggers the Access database to perform actions for the user.  If the subject line contains certain words, then the application does specific tasks.  The system downloads the email from the email server and parses the data into the appropriate fields.  We use specific subject and body tags so that Spam is disregarded under the assumption that it would be unlikely to have both subject and body tags identical to what we are using.  We thought this was a better solution than to tie Outlook and Access together. 


Adobe InDesign / Database Integration / Adobe Tagged Text files

This function takes data from a SQL database and creates a file that can be imported into the Adobe InDesign publication software.  The function retrieves data based on a users query and wraps the data into Tagged Text, which InDesign can understand.  The function creates a text file that can be imported into InDesign with the appropriate style sheets.  The user can create style sheets that define the way the imported data will look in the finished document.  The style sheet tags are included in the database text file. This procedure reduced ad production from days to a few hours.  


Staff Recruiting website

A basic website for a recruiting firm, with a form that captures candidate information and resumes into a database.  The database has 2 functions:  1) To capture the candidate information for an "on-line" interview prior to finally storing the interviewed candidate information in an ACT database.  2) Sending un-solicited candidate information to ACT for later search and retrieval.  Other interesting features of this system include capturing the candidate references as separate leads into the system for later search and retrieval. 


Indemnity, Casualty and Medical Insurance Audit Form System

A database system that is used to audit the performance of third-party workman's comp administrators.  A separate system for auditing Casualty claims administration.  Basically, these systems are used to audit the performance of third-party administrators.  The client seeks to determine if the administration is efficient, timely and effective for the insured.  There are contractual  performance guarantees that need to be assessed 


Legal Process Server Management and Tracking

This database program allows the user to track a large number of legal papers that are being served to individuals or entities.  The software allows process servers to track multiple addresses and multiple attempts to deliver.  Legal documents are on-line and automatically merge with the appropriate data fields in the database, so the users can simply press a button and create motions and court documents for a specific case.  The database is designed for a high volume of papers being served by many process server employees.  A commission structure is programmed into the system for paying the server employees based on the work they performed on that case. 


Construction Daily Reports

A general contractor has remote offices and requires daily reports to be completed by the job superintendents.  These daily reports provide a written record of the weather, equipment used, manpower and subcontractors on the projects. The superintendents seem to be more likely to complete these computer forms, than the written forms.  The program is a system of web based forms that are automatically emailed to the general contractors main office.  The emails are then stored as computer files, instead of paper files.  A copy of the daily report can easily be forwarded to the owner representative if necessary. 


FTP Automation

Automated FTP system to synchronize jpeg picture files and to download from a list of thousands of files residing on a master system.  The program scans a local file listing, determines which jpeg picture files are missing from the local system and uses the FTP protocol to download the missing files from the primary system.  The program also verifies that the photo files exist on the master system, prior to executing the download.  Essentially, this is a synchronization program for Multiple Listing Service MLS real estate photos.   This was written as part of the real estate management application.  The current projects download home, commercial and vacant land images from the NTREIS real estate multiple listing service system and the NWMLS real estate multiple listing service system. 


Exchange 2007 Web Services Integration

Messaging application for a real estate brokerage firm.  Web based CDO for Exchange messaging system.  Various business forms are completed on an intranet site and upon completion of the form data, an email is created, and sent to the appropriate company staff.  This is a huge time saver.  The data created for the email is also stuffed into a table in the database. Programming in CDO is pretty complicated until you learn the architecture of the database.  


Applications running on Remote Desktop Server

I have Microsoft Access databases running on Remote Desktop Server that are accessible from remote branch offices and home offices via DSL or Cable internet connections.  Microsoft Access has difficulty running properly on a remote connection.  Using Remote Desktop Server allows Access applications to run properly from remote branch offices and home offices.


Quickbooks Import Export

Several companies that have moved to Quickbooks accounting software have used conversion software that I have written.  Usually this involves reading data from reports that can be saved as a file, and converting that data into a file that Quickbooks can use to import Customer data, Vendor data, etc.  Also, using Quickbooks new interface, companies can access their accounting data for use in add-on applications.


Service Dispatch System

This program was developed using Microsoft Access for service companies. The application includes a service dispatch board, paging software and an invoicing system. Databases for customers, technicians, service calls and a diary are included. It is designed to be a multi-user system.  As service calls come in they are logged into the dispatch system and all users have access to the call information. The dispatcher can view his backlog of service, the 911 calls, and the regular service calls on his dispatch board. He can assign those calls as techs become available. After a service call is completed the tech can phone in the time and material, which is then priced out and invoiced in the office by the billing clerk. The tech creates an invoice at the truck for the customer to either pay then, or if a commercial account, sign off approval and keep a copy. The billing clerk then clicks off that service call complete, it is removed from the active service call dispatch board, but remains in the system as an account receivable until the tech brings the customer payment into the office, or the customer pays on account. 


Multi-Level Marketing Commissions Application

This application was designed to track commissions due to agents of a MLM sales company. The software included client, agent, product and transaction databases. The design of the system included application menus, data-entry screens & database validation, process sequencing and report printing. The goal of the software was to produce a weekly accounting transaction statement of agent activity which could be used to pay weekly commissions.  Originally it took several days for the staff to manually create these reports, but with this application, the turn around time was reduced to about 4 hours.