|
E-mail Me
Syllabus
Schedule
Handouts
Assignments
All My Webs
Dept Home
|
Access Day 4- Many-to-Many Relationships
This section is based on Chapter 6 in the Grauer & Barber
book. When yo complete this section, you should have a working knowledge
of:
- Creating Many-to-Many relationships
- Creating queries that "look up" data from one table based
on the value in another
- Creating queries with formulas
- Creating subforms based on queries rather than tables
- Formatting forms, including column widths and captions
- Total Queries
- Adding a Combo Box to look up data
One topic not covered in the text is the use of functions within queries.
In a calculated field, it is often necessary to include an "If"
question. Access includes the function IIF( ) that works exactly like
the IF( ) function in Excel. The syntax for the function is: IIF(Condition,
True, False).
- Condition must be some expression that Access can evaluate as either
true or false:
- [Member] = Yes
- [Salary] >=100000
- Items enclosed in square brackets refer to a field in a table.
The expression is evaluated using the value in that field for the
active record.
- True is an expression that will be interpretted only if the Condition
is true
- False is an expression to be interpretted if Condition is false
For example, if you wanted to create a calculated field in a query called
Bonus to award a 5% bonus to all salespeople with more than $100,000 in
sales might, it might look like this:
Bonus: IIF([Sales] >100000,[Sales]*.05,0)
This would look in the field called "Sales" for each record,
and for those where Sales > 100000, the value in the Bonus field would
be calculated by multiplying the value in Sales by 0.05. For all others,
the value in Bonus would be 0.
|
