Within about the next two hours of posting this I am seeking someone
with the abilities to do an medium difficulty Access project. The
project has 10 steps with an Access file that is to be built on with
the 10 steps being sent by email by me, to be overlooked by answerers
who wish to see and post a request.

Most steps include varitations of creating queries using the "xxxx" table...

---

Clarification of Question by alwaysrelaxed-ga on 07 Nov 2004 17:50 PST

Thanks! Deadline is 2 hours from this clarification post.

To access the Access file please go to
http://myphlip.pearsoncmg.com/cw/mpchapter.cfm?vbcid=7938&vsubmit=Go

and download Project Files - Project 10 - Project10.zip   

If emailing the final completed file is not possible please upload the
file somewhere and provide the link so I may download it directly or
inform me of another way of accessing the file.

The assignment and steps are:

1)Create a query using the CLASS table that shows the class number and
enrollment where the enrollment is less than 25 students. Sort the
results by the class number field.

2)create a query that shows the class number (sorted) of classes that
have either less than 25 students of more than 100 students. Show only
the ClassNumb field, not the fields or field you use to contrain the
results.

3)Create a query that shows the class number (sorted) of classes that
have an enrollment of greater than 25 and less than 100 students. Show
only the ClassNumb field in the query results.

4)Create a query that displays all of the MIS classes. These classes
will begin the ClassNumb field with "MIS" and you should use an
inexact contraint match.

5)Create a quesry that finds all senior level classes in the CLASS
table based upon values in the ClassNumb field. A senior level class
will always have the fourth character of the ClassNumb field be the
value "4".

6)Make a parameter query that asks the user to provide a ClassNumb
value and limit the quesry results to the ClassNumb and Enrollment
field values for the class.

7)What books are not being sold by any student? Show the book title
(sorted) and book number.

8)For each class, count the number of books used in the class. Show
the class number (sorted) and the count of the books. For this query
you need two tables.

9)Count the number of copies of all books that each student has to
sell. The student name should be in first, last name format such that
one field (called "Name") is created as the first name, a blank space,
and the last name. Sort by the "Name" field you create.

10)Which book has the highest price? Show the book to price, title, and author.

Your mdb is at
http://www.hammerdata.com/Google/Textbook.mdb

There are ten queries with names beginning with GA.

I was working quickly to beat your deadline and some of the later
questions weren't entirely clear, but I think I interpreted them
correctly. Again, I was working very quickly, so a proof on your part
would be in order.

- Hammer

---

Request for Answer Clarification by alwaysrelaxed-ga on 07 Nov 2004 19:06 PST

Great!  I put an earlier deadline to make sure it happens so still
really had another 2 hrs. :-)

  I try to just open from the link but it says it cant but allows me
to save it. I save it and it says its located outside my intranet or
untrusted site. It says copy to my machine or an accesible network
location.  Im sure the answer is perfect, just how do i get it!!
thanks for any help.

---

Clarification of Answer by hammer-ga on 07 Nov 2004 19:09 PST

Try right-clicking on the link and selecting Save Link Target As...

If that doesn't work, drop your security settings enough to allow you
to get the file. Meanwhile, I'll post the 10 SQL statements so you can
make the queries yourself, if need be.

---

Clarification of Answer by hammer-ga on 07 Nov 2004 19:15 PST

Here's the SQL for each of the ten queries.

- Hammer

1.
SELECT CLASS.ClassNumb, CLASS.Enrollment
FROM CLASS
WHERE (((CLASS.Enrollment)<25))
ORDER BY CLASS.ClassNumb;


2.
SELECT CLASS.ClassNumb
FROM CLASS
WHERE (((CLASS.Enrollment)<25 Or (CLASS.Enrollment)>100))
ORDER BY CLASS.ClassNumb;


3.
SELECT CLASS.ClassNumb
FROM CLASS
WHERE (((CLASS.Enrollment)>25 And (CLASS.Enrollment)<100))
ORDER BY CLASS.ClassNumb;


4.
SELECT CLASS.ClassNumb
FROM CLASS
WHERE (((Left([ClassNumb],3))='MIS'));


5.
SELECT CLASS.ClassNumb
FROM CLASS
WHERE (((Mid([ClassNumb],4,1))="4"));


6.
SELECT CLASS.ClassNumb, CLASS.Enrollment
FROM CLASS
WHERE (((CLASS.ClassNumb)=[Enter Class Number]));


7.
SELECT BOOK.Title, BOOK.BookNumb
FROM BOOK
WHERE (((Exists (Select BookNumb from STUDENT where
student.booknumb=book.booknumb))=False))
ORDER BY BOOK.Title;


8.
SELECT CLASS.ClassNumb, Count(INTERSECTION.BookNumb) AS CountOfBookNumb
FROM CLASS LEFT JOIN INTERSECTION ON CLASS.ClassNumb = INTERSECTION.ClassNumb
GROUP BY CLASS.ClassNumb
ORDER BY CLASS.ClassNumb;


9.
SELECT [FirstName]+" "+[LastName] AS Name, Sum(STUDENT.CopiesToSell)
AS SumOfCopiesToSell
FROM STUDENT
GROUP BY [FirstName]+" "+[LastName]
ORDER BY [FirstName]+" "+[LastName];


10.
SELECT TOP 1 BOOK.Price, BOOK.Title, BOOK.Author
FROM BOOK
ORDER BY BOOK.Price DESC;

---

Clarification of Answer by hammer-ga on 07 Nov 2004 19:17 PST

AlwaysRelaxed,

I'm going offline for the night. Hopefully, the materials I've
provided will allow you complete your project.

- Hammer

---

Request for Answer Clarification by alwaysrelaxed-ga on 07 Nov 2004 19:18 PST

argh, set internet security to low, restricted to low... everything
lowered.  Not working same message. No other way? Some how to just
download it then i can resave it? I have a junkmail anyone can have
email address of prftman@aol.com it can be sent to. anything!! haha

---

Clarification of Answer by hammer-ga on 08 Nov 2004 04:31 PST

AlwaysRelaxed,

I'm sorry your network is not letting you have files from outside. We
are not permitted to email you directly, however, the SQL I posted
should have allowed you to put the queries in the mdb yourself.

- Hammer

awesome!

As researchers are not permitted to communicate with you by direct
email, you may want to reconsider that particular requirement.

- Hammer