Mike Beaver's Quick Access Reference

This document is one I did while writing tables and queries for an insurance middle company that has contracts with both insurance companies(payors) and doctors. So the terms are couched within this context.

Unloading Text Files from RBASE:The first copy statement below is done in order to copy the RBASE down to the administrators PC so that when accessing it this will not slow down the main database the rest of the company is using. Do all of these steps shown below before running reports: 

1) copy K:\rb31\dbold* c:\rb31 
2) In Rbase (at the R> cursor, type:)  con c:\rb31\dbold (this connects you to the database) 
3) show null (the response will be -0-)
4) set null " " (there is a space between the quotation marks; this statement keeps access from flipping out over the null character as it originally was. ) 
5) out c:\mike\tblphy.txt (directs the output from the database to this particular text file)

OTHER EXAMPLES

out c:\mike\phycl010.txt
out c:\mike\hspcl010.txt

6) unload data for tblphy as ascii (sets the data type of the output) (LIMITING CLAUSES WHERE AND BETWEEN) where PHPRODTE between 03/01/1996 and 08/20/1997 PHPRODTE=start date
7) out screen (ends the screen output to the database;VERY IMPORTANT)
8) exit finishes setting up the database for use

Payor Downloads:

1) Perform above steps. (export data from RBASE)
2) Open Access / File / Open Database / find the database and open it / (ie: 1K:\ACCESS\PAYOROUT.MDB
3) File / import / text (delimited) - always for RBASE / go to IMPORT ASCII TEXT FILE c:\mike & choose the file matching the exported data from RBASE / import / append / OK.

Import Templates: For payor info.(with a database open) 

1) File / import / double click Microsoft Access / go find K:\access\template.mdb / structure only / choose a template.

Opening a database.dbf in Access:

1) File / open database / c:\microsoft\access\db1.mdb (open the database)
2) File / import (the safe way) attach (direct manipulation) / double click dbase IV / c:\download\company name\filename.dbf / close (pickup data)

Making a Table: (in the Database:PayorOUT query box)

1) click Design / Query / Make Table / put in the table name (ie: a10 A&I table) / (!) use the run query button

Queries:

1) File / K:\Access\payorout.mdb (open the database)
2) click the Query tab (on the left) (In the Database: PAYOROUT)
3) New / New Query / double click the proper table / drag & drop field names

Modify the Make Table Query to an Append:

In Database: PayorOUT Queries box (with the proper table highlighted) click Design / Query / Append

Combining 2 address fields into 1: (in a query)

a) right click on field line (of column) you want to build an expression for (expression builder comes up)
b) clear out windows / double click tables / select a specific table and double click on it (ie: addr.1 (&) " "(&) addr.2.

4) click red exclamation (!) square at the top of the page. (Run Query:)
5) End Date (term) -

a) place cursor in criteria field >2/1/97. [of tbpenddt (file) (end date; column)]

or field is null (selects docs who have been termed in last 6 months or haven’t been termed.)

6) un X box to hide this field (it doesn’t need to be seen)
7) Parametized Query [(in Criteria field) next to >[end date]]
8) after opening the newly created Append query with the Design button:

Make sure that:

   A) inside your Append query that the table (you’re pulling your fields down from) is the table you are getting your data out of.
   B) in the Query menu (on the top of your screen); click on the checked word Append and check to make sure that Append To Table 

    Name is the name of the (newly created) table you are outputting the data to.

Creating a Delete Query:

Open database in K:\ Access \ payorout.mdb / (with the query box open) Query / new / new query / add / close add table / drag * down to field / Query / delete / close & save.

 Export the Table :

Open database in K:\ Access \ payorout.mdb / in the table box click File, export, type of file you want to create.

Redefining the Table: (we have just created)

1) In the Database:payorOUT Table box click Design.
2) Use field requirements from notes / docs / or customer to modify table.

Totalling figures for a column:

1) Right click on the lower part of the query (down where the criteria field is). 
2) select total (out of the drop down menu that appears). 
3) click on the total field that appears with "group by" in it; then choose Sum from the options available.  

Creating Relationships Between Tables (Linking them):

1) type: Alt/E for Edit; R for Relationships. (with no query or table open)
2) right click on the gray area in the center of the box and add a table. (repeat for the other table) 
3) left click on the field which is common to both of the two tables you have imported and without

letting up on the button drag that field over onto the same field in the other table and drop it there.

4) save your changes to the relationships when exiting this sub-program.
5)