Go to Top

• Uniquely identify each record and required not null (blank)
• It prevent duplications.
• Indexed automatically which will speed up query process.
• Tie tables together through relationships.
• Sort records fast.
• Can be use more than one primary key in order to use the combination of fields (max 10 fields)  (Click the first field and Ctrl & click on the next field to select)

• If identical name, it join automatically.
• Field needs to have same data type.
• Use the long integer instead of integer.
• To remove join, click on the line to select and press the delete key.

Go to Top

• Open expression builder, type Total:=[quantity] * [price]   (click fields to select)
• Press the enter key
• Right click to open the field property box and choose the currency on format col.

Validation rule -- >= 1/1/99     (data has to be >= 1/1/99)
If you have trouble getting into the field due to the error message, use Shift & Tab key

• Right click on the menu bar which opens the property & size
• Excel import file, change target file to change the source file
• Tools/Database Utilities/Compact database which will compact database

Access is not case sensitive but check all import data
Zip code fields use the text field to contain leading zeros. If use the numbers, it will use all leading zeros.

Go to Top

• Open the table in Design view.
• In the upper portion of the window, click the field for which you want to define a different column name.
• In the lower portion of the window on the General tab, click the Caption property box, and then type the text for the column name. The text can contain any combination of letters, numbers, spaces, and symbols up to 255 characters in length. 

Go to Top

• Right click on the middle of the blank database view screen
• Show table and add tables, close
• Drag from one side field to many side field which opens relationship window
• Select Enforce Referential Integrity & cascade update related fields
• Select delete related fields
• Close and save file

Go to Top

• Open Query and select design view
• Select totals field
• Click on the 
• Click on Group by down arrow key & select Sum
• To see the totals for each company, eliminate other fields except to company name and totals fields.
• To see the grand total, delete all except totals field

• It is like the Pivot table that gives total of each group.
• It takes large amount of data and condenses it and makes groups to analyze data.
• If you select date field as a column, it opens another window for grouping
• Make sure if you choose month, it will add up all month regardless of year.  It better be all the same year.
• Limitations of Crosstab query – If there is no data, it does now show at all. If you add column headings on the Query property then all blank will show up

Go to Top

• Add on the criteria area,  like [What Company?]& “*”
• Press the enter key

• Go to Query from the menu bar, select Parameters
• Type the first parameter as it is showing on the criteria area then Press the Enter key

• If using Y/N field
• Change the order of parameters
• Data using between
• Between [start_date] and [end_date]
• Add character fields
• [first_name]& “ “&[last_name]

Go to Top

• Do not run if query name has ! or *
• After create a make table, click on datasheet view to preview, run & save
• Make table does not add primary keys.  You have to add Primary keys as original tables
• Select a query with condition using the criteria.
• Under date type >= [Closing Date]
• Open query drop down box, select the Make table query
• Run query and save , give the name
• Under the table tab, new table is created

• Put the letters Usys in front of the table name (change to a system object)
• Use Tools menu/Options, View tab, click System objects in the show section

Go to Top

• When open the database, click on Exclusive line and open
• Select Tools/Security/Set database password

Adding commands buttons to form
Click on the command button from the tool bar, select the line you need to setup

Go to Top

• On a form design view, click the command button
• Open the property box
• On Name and Caption field – “Find Name”
• On event tab, On click line click the builder button, …, select “Macro builder”.
•  Macro Action screen opens
• ShowAllRecords
• Goto Control , type Name on the Control name box
• Run Command, type Find on the Command box 

Go to Top

Go to Top

To assign a shortcut key to a field, edit the caption property of the label of the particular control you want to jump to, adding an '&' before the letter you want to act as the shortcut key. 

For example, if you wish to be able to jump to a 'Name' field you could edit the 'Name' label accordingly: 

N&ame 
In Form View the label will be displayed with the 'a' in name underlined: 
Name
Pressing ALT-a will switch the focus to the 'Name' field. This technique can be used on any object that has a caption property.

Go to Top

If you use the Expression Builder, the punctuation is filled in for you when you select a control or property, together with the names of the objects and controls.

How to copy a previous records values into a new record 
If you find that you often enter the same value in one field of your table you can use two methods to save re-typing the data. 

• Using CTRL+' (apostrophe) will repeat the value input in the previous record. 
• Amend the field's DefaultValue property in Design View to the most commonly used value. 

Go to Top

Adding Zeroes to the Start of a Numeric Field 
 If you need a numeric field with leading zeroes, change the field to a text field, the correct size. Access will convert the data from numbers to text fine.

Create an update query with the field in it. In the Update To cell, put a formula like this:
           right(("0000" & [original_field_name]),4)
This assumes you want a field 4 characters in size. 

Go to Top

For example, the account numbers were entered like this:

Wag1,   Wag2,   Wag3,   Wag4,   . . .    Wag10,   Wag11,   . . .
but when listed on a report they went like this:
Wag1,   Wag10,   Wag11,   Wag2,   . . .

To fix this, make the pattern like this, 'Wagnnnn', where the 'n' is a digit. This allows for 9,999 account numbers, plenty for the College. However, how can we get Access to automatically generate the next number?  Use ("Wag" & (right([acctnum],4)+1)) to get the next number, but if I use that technique on 'Wag0015' I will get 'Wag16'. To fix it, use this technique.

            "Wag" & right("0000" & (right([acctnum],4)+1),4)

Go to Top

Here is the way of formatting a date on a report.
Create the text box and in its format property put:

mmm d, yyyy         gives a date like this:  Jan 24, 2002
 or
mmmm d, yyyy      gives a date like this:  January 24, 2002

Create a text box and for its control source, put something like this:

           ="For the period of " & [Forms]![frmReport]![txtStartDate] & 
               " to " & [Forms]![frmReport]![txtEndDate]

 Make sure the text boxes on the form are formatted to "mmmm d, yyyy" for this example.