I am very very needy for DBMS qry help! :)

hey! I was going along swell til last night when wham! I was hit in the face by 'Competency Test 2' which was due at 11:59 pm PDT YESTERDAY (oops :blush:). All of the sudden it's taking me 2 hours to construct a simple little qry I *know* how to do, but *Access* won't let me.
I am not looking for someone to DO my work; I need some pointed questions or out-and-out clues that will lead me to the correct answers.

What would Access not let me do this a.m? It would not let me make an 'OR' qry on design-I went into SQL about 3 times and changed the 'AND's into 'OR's and finally it worked. I got everyone whose Fname began with a "B*" OR Initial was "B" OR Lname began w/ a "B*" after struggling for awhile. Next one I struggled as well-mostly because I'm a crap Typist and I had to concatenate Zip + 4 in a Query.
Now I am left with four questions and I am lost-ish

"6. How many students live in each county where students live? Create a list the names of counties where students live and the number of students who live in each of those counties."
I came up with a really inelegant solution for this one. I did it in two-a simple qry, followed by an X-tab. and while it answers the question, I think it might just be teh suck.

"7. What is the lowest grade ever earned in a course with a CTN course number prefix in the history of the school? Final output should be one number…(To answer this question you may want to use two queries, but it can be answered in one query.)"
Trying to figure out the 1 query solution here

there are two more I haven't tried yet.

:hi:

6)
select county, count(*)
from the_table
group by county
having count(*) > 0

7)
select min(grade)
from the_table
where prefix = 'CTN'


am i missing something?

give me a few here-known for my extremely low WPM in the typing world, although I will Copy and paste this!

"SELECT tblCourses.ItemNumber, tblCourses.CourseNo, tblEnrollments.NumericGrade
FROM tblCourses INNER JOIN tblEnrollments ON tblCourses.ItemNumber = tblEnrollments.Item
WHERE (((tblCourses.CourseNo) Like "CTN*") AND ((tblEnrollments.NumericGrade)=Min("grade")));"

I must slowly and painfully type out, because MS doesn't allow Copy and paste of the office assistant (How helpful :eyes:)
"Cannot have aggregate function in WHERE clause((tblEnrollments.NumericGrade)=Min("grade")))"

But it doesn't object to Min("grade") (yet)

Thank you!

although I do know about SQL, being an Oracle developer. In Oracle, your WHERE clause "((tbEnrollments.NumericGrade) = MIN("grade"))" would not be allowed, unless MIN(grade) happened to be one of the columns in the select list.

What you would need to do is use a subquery:

AND tbEnrollments.NumericGrade = (
SELECT MIN(grade)
FROM whatever
WHERE wherever
)

Dunno if that will help you...

I got nothin' but time here apparently to try shit out. I may end up dumping this sad sack of crap on a floppy and head up to the computer lab. The DB geek should be in in a a little while...
Thanks! I just need to figure out the from and where-Part two of this course is SQL, and surprisingly, MS used SQL in Access, instead of making up a whole new f'd up way to do stuff-I'm beginning to think of Access as a big old incomplete DB 'Wizard'

and yes, you're better off typing in sql directly for all but the simplest of queries.

even if you're a slow typist ;)

and yes, you're better off typing in sql directly for all but the simplest of queries.

even if you're a slow typist ;)

it seems like DU'er "unblock" gave you the solution ...

I don't see why you're trying to use the min function in the where clause ... just use it in the select list ...

the where clause doesn't need to have a condition where numericGrade = min(grade) ... just use "Select min(grade) from table where courseNo like "CTN*" ... the query will return a single value containing the minimum grade for any course starting with "CTN"

That's the problem. *I think* I only know how to put it in the Criteria field and it goes to WHERE, which is unacceptable. So I am going to ask the guy at school-I only need to do it once and I'll remember it-for a few minutes, anyway.

you can simply do

SELECT foo, bar, MIN(baz)
FROM whatever
WHERE wherever

But then this is Microsoft we're talking about here, so are you actually prevented from typing in the SQL yourself? Do you have to formulate the query using some bletcherous GUI instead? (Because some marketroid thought that it would be easier to use, no doubt...)

It IS microsoft. And yeah, I am probably missing a bletcherous GUI thing.

i don't know how to do it either using the Access query builder but here's how you can switch to "SQL View" to enter whatever you need:

first, in the main DB window, click on "Queries" ... then, double-click on "Create query in Design View" ... add whatever tables you'll need for the query and then close the "Add table" dialog box ...

then, click on "View" and then on "SQL VIEW" ... this should give you something like "Select from myTable" ... then just insert the min(grade) clause after the "Select" keyword ... btw, if you then switch out of SQL view into the querybuilder grid, you'll be able to see how to add the min function to the "Select" list ...

on edit: in the "datasheet" querybuilder view, you can add the min function to the select list (NOT the criteria), by adding the following to the "Field" prompt: Expr1: min(grade)

i guess any functions used in the select list (in the "datasheet querybuilder view) have to be preceded by "Expr1: " ... perhaps subsequent functions would be preceded by "Expr2: , Expr3:" and so on ...

let us know if this works ...

but I am not a big fan of the Baby Ruth. I think nuts fuck up chocolate! You keep it sweetie!

:hi:

like those don't stick in your teeth :eyes:

:P

plus, they are fruitylicious! :7

:P

with a rudimentary sql parser.

foxpro is a fantastic compared to access.

the ONLY way access makes sense to offer as a product is if you're also selling sql server. if access were sold by an independent company, it would fail remarkably quickly.

i'd put this forth as further evidence of monopolistic power and/or private sector inefficiencies, except that you have to be too much of a techie to get it.

:P

:shrug:

?

cause I didn't understand a word. :)

Structured Query Language, and the word 'Structure' is key. :(

Thankyou for the help, the jokes and the offers of (bad) candy!

:hi:

Self Delete

to do exactly the right thing:
# 6:
SELECT Count(tblStudents.SSN) AS Expr1, tblWesternWashingtonZipcodes.County
FROM tblWesternWashingtonZipcodes INNER JOIN tblStudents ON tblWesternWashingtonZipcodes.Zipcode = tblStudents.Zip
GROUP BY tblWesternWashingtonZipcodes.County;

And #7
SELECT tblCourses.ItemNumber, tblCourses.CourseNo, tblEnrollments.NumericGrade
FROM tblCourses INNER JOIN tblEnrollments ON tblCourses.ItemNumber = tblEnrollments.Item
WHERE (((tblCourses.CourseNo) Like "CTN*"));

I get it now, sort of anyway, gonna get Lesson 6 started today:P and get my questions going before Friday

And 8 and 9 were so hideously simple, I finished them in under 5 minutes each, then got them checked, 'cuz it was TOO easy.