Queries & calculations

[ShaAnne]

Per my profile recently returned to college, and confidence was high till
this online Advanced (go figure that one) Microcomputers, Access 2003, class
with no book and only tutorials and the dummies book I paid $25 for. I know
this is a newbie ?, but I have struggled my butt off and down to the wire;
must be turned in by midnight Sunday and i'm still struggling. I got to the
calculations, and just went brain dead, too much. If someone could send me a
link or post me some help. This is the following Lab three that I'm working
on:
10. Create a new Query that will display the First Name, Last Name, and Comm
Rate fields from the Sales Rep table to the grid.
a. Add the Name and Balance fields from the Advertiser table. Calculate the
pending commission (balance * comm rate) for the Sales Rep table.
b. Sort the records in ascending order by last name and format pending
commission as currency. (Hint: Use Microsoft Access Help to solve this
problem.)
11. Create a new query that will display the following statistics:
a. the total balance and total amount paid for all advertisers;
b. the total balance for advertisers of sales rep 29;
c. the total amount paid for each sales rep.

I also can't figure out how to restrict certain records : see below lab:
a. Create a query that will display the Advertiser Id, Name, Balance, and
Amount Paid fields from the Advertiser table and the First Name, Last Name,
and Comm Rate fields from the Sales Rep table.
b. Restrict the records retrieved in part a to only those advertisers that
are in MA. ( I have the query built, but how do I restrict certain records?)
gosh I am pullin my hair out!!!!!!!!!!!!!!!!!!!!!!!!! I've heard (from making
calls and stuff asking for help the last month that everyone I talk to says
this access is a program from hell, I'm sorry but I agree. Any pointers
would be helpful. ShaAnne





ShaAnne . . . . panicked over Access in OklahomA

 

[Evi]

Be calm ShaAnne.

It's just teacherese for Filtering the query by using the Criteria line. Why
do teachers always have to find the most jargony esoteric words for
everything!

Open the Query in Design View
Under the field that contains MA as one of the records, in the Criteria row,
type
"MA"

because it is a text field, you need quote marks around the MA

If it is a number field you don't need quote marks
If it is a date field you surround it with hash marks

#04/25/2008#

filters out the records which have the 25th of April 2008 in that field

According to your Regional Settings, you may need to use #25/04/2008# for
this last filter so check but if you are in the USA you will probably need
the month/day/year format

Other things you will need to know are the 'Operators' which can also be
used in the Criteria row
=

Greater than
10

would filter out all the records where the number field has numbers greater
than 10 in it.

= Greater than Or Equal to

<Less than
<= Less than Or Equals
Between x AND y
eg Between #04/25/2008 AND #04/28/2008#

filter out all the records between AND including those 2 dates.

everyone forgets that filter

Don't despair. It really sucks leaving you with Access for Dummies and a
Deadline.

Now if you could just persuade my daughter to settle down to her
dissertation!....

Evi

 

[ShaAnne]

Oh Evi, Guess I am just too tired of trying, cuz now I'm really lost. I'm
gonna cut and paste my Lab-three 9 thru 12 again and under that my troubles:
here goes:
a. Create a query that will display the Advertiser Id, Name, Balance, and
Amount Paid fields from the Advertiser table and the First Name, Last Name,
and Comm Rate fields from the Sales Rep table.
b. Restrict the records retrieved in part a to only those advertisers that
are in MA.
2. Create a new Query that will display the First Name, Last Name, and Comm
Rate fields from the Sales Rep table to the grid.
a. Add the Name and Balance fields from the Advertiser table. Calculate the
pending commission (balance * comm rate) for the Sales Rep table.
b. Sort the records in ascending order by last name and format pending
commission as currency. (Hint: Use Microsoft Access Help to solve this
problem.)
3. Create a new query that will display the following statistics:
a. the total balance and total amount paid for all advertisers;
b. the total balance for advertisers of sales rep 29;
c. the total amount paid for each sales rep.
4. Create a query that will display the Sales Rep Number, Last Name, First
Name, Advertiser Id, Name, Balance, and Amount Paid fields. Save the query as
Sales Reps and Advertisers.

Okay I created my 9th query, which happens to be #9 and it has Adv id, name,
and balance fields from Adv. Table Then included fields first name, last
name and comm rate fields from sales rep tables which built my 9th query.
Okay next instruction in my lab says: b. restrict records retrieved in part
a to only those advertisers that are in MA . . . .
but when i open the query in design view like you said there is no field
containing MA There are only the 6 fields mentioned above 3 from the adv
table and 3 from the sales rep table I started out good from #1 thru 8
then crashed and burned and cannot get my MOJO back. guess i'll just have to
finally give up. can't stay on this computer till midnight tomorrow nite.
ha! check it out below:

In the Lab- Four



Maintaining the City Area Bus Company Database
Problem: The Advertising Sales Manager of the City Area Bus Company would
like to make some changes to the database structure. Another field must be
added to the database, and the size of the First Name field must be
increased. Because several different individuals update the data, the manager
also would like to add some valida¬tion rules to the database. Finally, some
additions and deletions are to be made to the database.
Instructions: Use the database created earlier in the class to perform
the follow¬ing tasks.
1. Open the Advertiser Table of the City Area Bus Company database
2. Create an index for the Name field. Be sure to allow duplicates.
3. Create an index on the combination of the State and Zip Code fields. Name
the index Statezip.
4. Display the Advertiser table in Datasheet view and order the records by
zip code within state.
5. Open the Advertiser table in Design view and change the field width of
the Name field to 22
6. Add the field, Ad Type, to the Advertiser table. Define the field as Text
with a width of 3. Insert the Ad Type field after the Zip Code field. This
field will contain data on the type of advertising account. Advertis¬ers are
classified as retail (RET), service (SER), and dining (DIN).
7. Save these changes and display the Advertiser table in Datasheet view.
8. Change the name of account HC11 to Hilde's Cards & Gifts.
9. Resize the Name column to fit the changed entry. Adjust the width of the
remaining columns to best fit the size of the data.
10. Print the table. If necessary, change the margins so the table prints on
one page in landscape orientation. Close the table. Save the layout changes
to the table.
11. Using a query, change all the entries in the Ad Type column to RET. This
will be the type of most accounts. Do not save the query.
12. Open the Advertiser table and order the records in descending order by
balance. Print the table and then close the table. If you are asked to save
changes to the design of the table, click the No button.
13. Create the following validation rules for the Advertiser table and save
the changes to the table. List the steps involved on your own paper,
a. Make the Name field a required field.
b. Specify the legal values RET, SER, and DIN for the Ad Type field. Include
validation text..
c. Ensure that any letters entered in the Advertiser Id and State fields are
displayed as uppercase,
d. Specify that balance must be less than or equal $450.00. Include
validation text.
14. You can use either Form view or Datasheet view to add records to a
table. To use Form view, you must replace the form you created in Project 1
with a form that includes the new field, Ad Type. With the Adver¬tiser table
selected, click the New Object: AutoForm button arrow on the Database Window
toolbar. Click AutoForm. Use this form and add the following record:

PP24 Pia’s Pizza 113 Main Richmond MA 05434 DIN
S50.00 50.00 31


15. Close the form. Click the Yes button when asked if you want to save the
form. Save the form as Advertiser. Click the Yes button when asked if you
want to replace the Advertiser form you created in Project 1.
16. Open the Advertiser form and locate the advertiser with advertiser NO 10
and then change the ad type for the record to DIN. Change the ad type for
advertisers AC25 and CS46 to SER.
17. Change to Datasheet view and print the table.
18. Use Filter by Form to find all records in the table where the account
has the ad type of RET and a zip code of 05434. Delete these records. (Hint:
Read the More About on page A 3.12 to solve this problem.)
19. Prepare to print (but do not print) the Advertiser table. Specify
referential integrity between the Sales Rep table (the one table) and the
Advertiser table (the many table). Validate the table relationships by
making sure the Relationships window is open
yey right I'm getting that done. ha! i still have Front page to go. But i
am not even gonna try that. I did paint and completed. 2 of the access labs,
tried access project and it's not right but i sent anyway. Diligent effort
ha!
Forever panicked in OK, and thanks for trying so hard to help me. my
head is just thick.

 

[Evi]

Hope you've slept a bit ShaAnne. Comfort yourself that whoever ordered you
to create a field named Name has himself some gaps in his learning (Name is
a Reserved Word in Access and can cause problems in a proper database when
used as a Field Name).
MA probably stands for Massachussess (that State I can't spell) so that
field needs to be in your query grid (you can untick it in Design View so
that it doesn't actually show when you go into the query's Datasheet View) -
remember the question (rather nastily) tells you to *Display* that field but
doesn't tell you that you can't add it to the query.
I'm not keen on trick questions myself since they don't test your knowlege
of Access, just the ability to read the examiner's mind.
Evi

 

[Evi]

Must stop writing messages in a rush.

I meant to say that the "question tells you to Restrict to those Advertisers
who are in MA, but tells you to Display certain other fields' ie it wants
you to add the field that contains MA to the query in Design View, and
filter that field by MA but Untick the field in Design view so that it
doesn't show in the main view.

Write again if you are still in a tangle. Hope you get the deadline
Evi