GlobalWare Version 3.2
These steps outline the procedure for setting up the first data export to Commtrak
1. Load Access. Select "Blank Database". A window titled "File New Database" opens up.
2. File Name: Type "gw2ct" - then click on "Create" 3. Go to "File" - "Get External Data" - "Link Tables"
3. A window titled "Link" opens up. Click down arrow next to "Files of Type" and select "ODBC Databases"
4. The "Select Data Source" box comes up
5. Go to the "Machine Data Source" tab
6. Select the data source name "GLBWARE 32" and double-click.
7. The "ODBC Configuration for Adaptive Server Anywhere" box appears.
Agencies with Globalware version 3.2 will have Sybase
7.
8. At this point, the ODBC Configuration Box will have tabs to click
on.
Click on the Login tab. This will bring up the User ID and Password
prompt.
9. Select data tables named DBA_Invoice, DBA_ARCTable, DBA_AccountID and DBA_PayView.The Unique Record Identifier for DBA_PayView is ID.
10. Click on the Queries Tab. Click on the New button. Select Design View and OK. Close the show table window.
11. Click on View on the top menu bar. Select SQL View. A Query Window will open with the word "Select:" highlighted. Delete the highlighted "Select:".
12. Copy the following to where the "Select:" was before.
SELECT DBA_Invoice.InvoiceDate, DBA_ARCTable.ArcNum AS ARC, DBA_Invoice.Branch, DBA_Invoice.BkAgt AS Agent, DBA_Invoice.Traveler AS Name, DBA_Invoice.DepartDate AS Arrival, DBA_Invoice.ReturnDate AS Depart, DBA_Invoice.BaseFare AS Rate, DBA_Invoice.TotalCost AS [Total Rate], DBA_Invoice.CommAmount AS Commission, DBA_Invoice.Property AS Phone, DBA_AccountID.ShortCode AS [Chain Code], DBA_Invoice.Destination, DBA_AccountID.Name AS Supplier, DBA_AccountID.Addr2 AS Address, DBA_AccountID.City, DBA_AccountID.State, DBA_AccountID.Zip, DBA_AccountID.Country, DBA_Invoice.TicketNum AS Conf_no INTO gwexport
FROM (DBA_ARCTable INNER JOIN (DBA_AccountID INNER JOIN DBA_Invoice ON DBA_AccountID.AccountId = DBA_Invoice.Property) ON DBA_ARCTable.StpNum = DBA_Invoice.Stp) INNER JOIN DBA_PayView ON DBA_Invoice.PayID = DBA_PayView.ID
WHERE (((DBA_Invoice.ReturnDate) Between [Enter Start Date] And [Enter End Date]) AND ((DBA_PayView.CommRcvdFlag)="n") AND ((DBA_Invoice.Status)<>"v") AND ((DBA_Invoice.RevType)="h"))
ORDER BY DBA_Invoice.InvoiceDate;
13. Click on File / Save. Save as Query Name: type "Export". Click on File / Close. You will return to the Queries Tab.
14. Highlight Export and click on Open to run the query. You will get a few warnings about creating the new export data table - accept the warnings by clicking on Yes. You will the get a prompt to provide the Start Date and then the End Date - these let you determine the date range of the data you wish to export. You will then get a warning about pasting these records into a table - Click Yes.
15. This will return you to the Queries Tab. Click on the Tables tab and you will see the newly created table "gwexport". Highlight this file and Click on File / Save As / Export...Click on OK for External File or Database. This will give you a new window displaying the default data subdirectory used by access. You may use this directory or select another destination for the file.
16. In the bottom of this window is an option box called File Name: type "gwexport".Click down arrow next to "Files of Type" and select "dBaseIII" and then click on the Export button.
17. Use a compression utility like PkZip or WinZip to compress the file and send to Commtrak.
These steps outline the procedure for data export to Commtrak after an initial setup.
The next time you wish to export data to Commtrak most of the work is already done.
1. Load Access. Go to "File" - "Open Database" - Select "gw2ct.mdb"
2. Supply password if requested
3. Click on the "Queries" table"
4. Select "Export" and click on "Open" to run the query. You will get a few warnings about creating the new export data table - accept the warnings by clicking on Yes. You will the get a prompt to provide the Start Date and then the End Date - these let you determine the date range of the data you wish to export. You will then get a warning about pasting these records into a table - Click Yes.
5. This will return you to the Queries Tab. Click on the Tables tab and you will see the newly created table "gwexport". Highlight this file and Click on File / Save As / Export...Click on OK for External File or Database. This will give you a new window displaying the default data subdirectory used by access. You may use this directory or select another destination for the file.
6. In the bottom of this window is an option box called File Name: type "gwexport". Below this is an option box called Save as type: . In this option box select dBaseIII and then click on the Export button. You may get an overwrite warning - but proceed.
7. Use a compression utility like PkZip or WinZip to compress the file and send to Commtrak.