*Please ignore this Post* Just using Livejournal for my computing course Notes*
to see the characters available for input masking, just put the cursor on the property box’s Input Mask and then press F1 key on your keyboard
Use captions to insert a more descriptive label to be used as a column heading in the table, e.g., for field CMobile, the Caption should be Mobile.
(1) symbol > will capitalise the entry, i.e., vic becomes VIC.
(2) a lookup wizard for state: Select I will type in the values I want. Click Next
Enter each state (VIC, NSW, SA, WA, QLD, NT, TAS, ACT - use the down arrow on the keyboard to enter the next state, click Next.
The label for the lookup column will be State, click Finish.
(3) in the input mask area, the build icon will appear, click on this to open a wizard to adjust the phone to suit Australian conditions
need to edit the first phone number to read !\(99) 9900\ 0000
(4) Similar to the above but you will need to remove the brackets and add some digits so the mobile number reads 0000\ 000\ 000
Click on the External data tab, Import section, Excel
In the design view of the Venue table, change the data type of CID to Lookup Wizard…
Select I want the lookup column to look up the values in a table or query, click Next twice.
Add the fields CID and CName
Adjust the column width, click Next.
The label for this look up column will be CID, Enable Data Integrity should be ticked.
This will ensure the customer exists is the customer table. Click Finish.
Defining a Relationship
Select the Database Tools tab and click the Relationships button
The tables should be visible with a fine line connecting them
Click and drag the key field (CID), from the one table (Customers), and drop it on the matching field (CID) in the many table (Venue). Click on YES, you want to edit the relationship.
Check Enforce Referential Integrity, click ok
Creating Table
Click on the Create ribbon, select Table section, click on the Table Design, save (....)
Validation Rule (reduce data input error)
Adjacent to the Validation rule for TTInCost add the following: >=5000 (or wtv) .
Save the table, click on datasheet view.
The error message is a default one which is difficult to understand. An entry in the validation text area will overcome this.
Go to the Design view. In the Validation text area, we will add the explanation: Amounts entered must be greater than $5,000. (or wtv else)
Query (A query is a means of questioning a database.) [more querries by stats or wtv below]
Create ribbon, Query Design view
Add the Customers and Venue tables (or wtv else) , by selecting each table separately and clicking on Add. close the dialog box
Add in the fields
Query is complete, close the query and save as (...).
Go to the Taps (...) table, in design view. Select Vid. In Data Type area, click the down arrow and click the ‘Lookup Wizard…’ In the dialog box, check the ‘I want the lookup column to look up the values … etc.’ button. Click Next.
Select the Queries option (next)
Select VID (...) as the value that you want to use in the database.
Remember, we want this value to link the Venue table and the Taps table. Click Next, and Finish up the process. Save the Taps table.
Cannot see details so edit width
design view. In the field properties section, you may have noticed the Lookup tab, and a General tab.
change the column width and list width to appropriate ones.
create a list box for Suppliers
In the Data Type area select Lookup Wizard, in the first screen of the wizard use the second option I will type the values that I want, click Next.
Use the down arrow to add the names of suppliers, click on Next. The Lookup column can have the label Suppliers, click Finish.
linking
TapType field (TTID) needs to be linked to the Tap Type
design view and select the TTID field.
In the Data Type area, type Lookup Wizard… Accept the default and click Next.
The Tap Type is going to come from the TapType table, select it
FORMS
selected the table from the Navigation Pane (the one you want to create)
Select the Create toolbar, Forms section, click on the Form button
To unlink the forms
Select all the fields concerning the contact person (CFirst, CLast, Cmobile, CDirect, Email), select the Arrange toolbar, Table section, Remove Layout.
right mouse clicking to bring up a series of shortcuts select Special Effect button
Click on the label icon in the Design ribbon, Controls section, draw a label above the contact details, enter the label - Contact Details.
Command Buttons
So it’s back to design view and the Design toolbar. This time it’s the Button (Form Control)
Click and draw the buttons at the bottom of the detail section. If there isn’t enough room, drag the bottom of the section to increase the size.
These buttons should print a record and close a form.
SIMPLE FORM
table highlighted, go to the Create ribbon, click the Form Wizard button
Click on >> to add all fields from the list of available fields. Click Next.
Accept the defaults by clicking Next twice, then Finish.
In Layout view, adjust the text box size.
Save the form
add the command buttons to save a record. Go to the Design view and the Design toolbar. This time it’s the Button (Form Control).
Click and draw the button
create a Menu screen.
Select the Create toolbar, Forms section, a Blank Form (not based on any table or query).
in Design view, right-click the mouse in the Detail section to bring up the shortcut list, select Form Header/Footer.
Copy the header from one of the other forms and put it on this form. This form should have the title Main Menu.
Draw a button to allow the user to open each of the forms. They should show all the records. This should start up the command button wizard
Save the finished form as MainMenu
Returning to the Menu
adding a ‘Close Form’
Open an existing form.............Create a button.............Select Open form, and choose the MainMenu....................Label the button, Close Form
MACRO (Autoexec Macro and Alternative Menus)
In the Create toolbar, click on the Macro icon (in the Other section).
Click in the Action column, choose Database Objects, OpenForm -
In the Action Arguments section (to left of the screen) the Form Name should be set to MainMenu.
The second action should be set to Maximize, select this from the Window Management optios. There are no Action Arguments for this action.
Save the macro as Autoexec and Close
Queries by state
Use the Create toolbar, Queries section, Query Design
Add in the tables
Close the dialog box.
add the necessary fields
sort on what we need in either ascending or descending order
Click on the View icon to display the result of running this query.
save query and close
Dynamic Query - Venue by State (the abobe query just created)
Create a copy of the previous query, by right clicking the query Customers and their Venues, select Copy from the context-sensitive menu.
Right-click on Query heading from the Navigation Pane., select Paste.
Save the query as Venue by State.
Open the query in Design view.
In the criteria row for VState, delete any text that is already setting criteria, and type the following, with the colon and the square brackets [Enter the State of the Venues you wish to view:]
Run this query by moving to datasheet view, using the icons in the bottom right of your screen, change to Datasheet view. Type NSW (or wtv) when prompted.
Save the query so we can always use it whenever we want the information.
Close this query.
Sum/Count Query: (exp ‘How many venues does each Customers operate?’)In the Create toolbar, Queries Section, select Query Design
Add in the Customers table and the Venue table.
To design the question, add the following fields: CName, CAddress, CSuburb and CState from the Customers table; and VID from the Venue table (we’re only interested in the number of venues, not their details).
We’ll ask Access to count occurrences of VID.
Click the Σ Totals icon in the toolbar.
Click the drop down button in group by for the VID field, and select ‘Count’ as highlighted in Figure 4.
Sort VID in descending order.
Run the query - by clicking on the red exclamation mark in the toolbar.save & close query
Calculation Query: Total Cost to (a calculation between 2 fields in 2 different tables)
In the Create toolbar, Design Section, select Query Design
Save the query
To enter the calculation, type Total Cost: in the next empty field
Click on the Builder icon in the Design ribbon, Query Setup section, click to open *the file*
Complete the expression by opening the Tables. Double-click +Tables on the left side of the Expression Builder.
Double click on the MaintenanceContract table, and then double-click on MCCost field in the centre area of the Expression Builder to add to the expression.
Click the + button in the middle of the Expression Builder or type a plus symbol, then add the other fields so the dialog box resembles Figure 8, then click OK
Right mouse click anywhere in this column, select Properties, Format for currency.
Run the query and Print Preview the results
save & close
The Report Wizard
Use the Create toolbar, Reports section, Report Wizard icon.
Choose the table (or query) the data is to come from
Select the fields we’re interested in
We don’t want to group in any way (more about grouping later), so click Next
We will not sort this list, click Next.
Choose a columnar layout, Next and name the report Customer List.
Click Finish and preview the result.
Report Based on a Query
Use the Create toolbar, Reports section, Report Wizard.
Choose the query the data is to come from,
Send across all fields in the query, accepts the defaults, click Next 3 times
Choose a Blocked layout style and name the report
In Design view, Report Design Tools ribbon, Header/Footer, select Date and Time - choose an appropriate format
save & close
A Report with Calculations
Use the Create toolbar, Reports section, Report Wizard icon.
Choose the table (or query) the data is to come from, in this case select the query Establishment Cost.
Send across all fields in the query.
Group by CName. Click Next
Sort by Venue.
Click on Summary options - sum Total Cost. Click OK and then Next.
Sort by Venue, click on Summary options - sum Total Cost. Click OK and then Next.
Choose a stepped layout, Landscape orientation.Title the report Total Cost to Hammer Wines of Installed Taps.
To get the subtotals into currency, right-click the Sum textbox control. Under format select currency
save & close