Anyone here good with Access 2007?

[Leftist Agitator]

I'm trying to finish up a project, and I'm pretty sure that either A - My copy of Access is somehow defective or B - My brain is somehow defective. Anyone able to lend a helping hand?

[darkstar3]

1. I train people in it.

Ask away. :)

[Leftist Agitator]

2. OK, what I'm trying to do is make a Query...

That will return a data subset of a table, specifically, entries above a numeric value from one column, and several entries from a column of text values that fit the aformentioned numerical criteria.

The numerical criterion enters and displays fine, but when I try to restrict the query to the text values from the other column, Access will only return the first appropriate text value from the specified criteria.

I've tried using the 'or' separator, the In() function, and I've tried entering each text value in a successive criteria field, and nothing seems to work.

If you need more explanation or screenshots, please feel free to ask.

[darkstar3]

3. In the query builder window,

the word 'or' doesn't actually work on the criteria line. You have to stack your 'or' arguments vertically in the same column, AND each 'or' criterion is a comparative line all to itself.

Let me 'splain with an example. This won't be easy, given the textual limitations of board, but bear with me. In the following example, the character '--' is used as a column divider.

Field -- PayRate -- Name
...(snipping to jump to criteria)
Criteria -- >10 -- 'John'
Or -- (blank) -- 'Sally'


I'm assuming that this is how you've tried using the OR separator line in the past. Unfortunately, this doesn't work. The reason is that the lines beginning with OR are additive. The query builder window, when laid out as above will return a result set corresponding to the following description:
All people named John who are paid more than '10', and all people named Sally no matter what the Sallys are paid.

So, in the query builder window, start with your Name column (or whatever text column you're using), and "stack" your names, starting in the Criteria row and going down, one name per line. Then go over to your PayRate field (or whatever your numerical field is) and in each row where you have a name specified in the name column, REPEAT your numerical criterion. You should end up with something like this:

>10 -- John
>10 -- Sally
>10 -- Rose
>10 -- Jack

Make sense?

[Leftist Agitator]

4. I tried that. It didn't work. But I'll go back and try again, and report back to you.

And thank you for taking the time to help me out with this! :)

[Leftist Agitator]

5. Well, I tried it again, no luck.

Here are screenshots of the build table and the results that the query is returning:





And here is a screenshot of the original table:






Why in the hell is Access only returning records for Fords?! This is driving me insane. I've been stuck in front of this damn computer all day and already had to cancel one outing on this gorgeous 70 degree sunny day, I'm going to be irate if I miss my whole Saturday night because of this shit.

[darkstar3]

6. Hmm...

Well, three things come to mind.

A. Try single quotes instead of doubles. That is, of course, if Access will let you do that.
B. Show me the In() function that you used before that didn't work.
C. In your next screenshot, use the view button in the upper left hand corner to switch to SQL View, then post that. That language is actually how Access gets its data, and the gui is writing it for you, so the gui may be making a mistake. I might be able to send a SQL query that will do it right.

[Leftist Agitator]

7. Well, the In() function was just that function on the criteria line for the make of vehicle...

With each value separated by a comma.

I tried single quotes, but of course, Access refused to cooperate.

Here's a screenshot of the SQL view:

[darkstar3]

8. Alright, let's try this:

In the SQL view, change your WHERE line to the following:

WHERE Models.Make In('Ford','Chrysler','Chevrolet')

At that point, run the query and see what happens. Hopefully, the In function specified should show all the Fords, Chryslers, and Chevrolets in the list.

If it works, switch back to SQL View and make your WHERE clause look like this:

WHERE Models.Make In('Ford','Chrysler','Chevrolet') AND Models.{Highway MPG} >= 28

--------
Another option is to get pedantically specific:
WHERE Models.Make = 'Ford' AND Models.{Highway MPG} >= 28 OR
Models.Make = 'Chrysler' AND Models.{Highway MPG} >= 28 OR
Models.Make = 'Chevrolet' AND Models.{Highway MPG} >= 28

ETA: Argh, the HTML filter on here cut out part of what I was trying to show. The '{' and '}' in the previous code should be STRAIGHT brackets, not curly.

[Leftist Agitator]

9. Well, I tried all 3 suggestions in SQL...

No luck there, either. It returned a syntax error on the boolean, and when I put in the Highway MPG tag, it still only returned Fords.

I give up... Life is too damn short to worry about this nonsense.


Oh, and darkstar3, THANK YOU for taking the time to help me with this! I appreciate it more than you know.

[darkstar3]

10. Check back up there before you give up,

that syntax error may have technically been my fault. :)

[Leftist Agitator]

11. Seriously, don't worry about it.

I'm sure as hell not going to.

And again, I thank you for going out of your way to help me.

[EvolveOrConvolve]

12. Try this query

SELECT Models.Make, Models.Model, Models.MSRP, Models.[City
MPG], Models.[Highway MPG], Models.Hybrid

FROM Models

WHERE ((Models.Make = 'Ford') OR (Models.Make = 'Chevrolet')
OR (Models.Make = 'Chrysler')) AND (Models.[Highway MPG] >=
28)


(I apologize for the messy formatting, DU's message board
software really isn't equipped to do code)

[Leftist Agitator]

13. Thank you, but this didn't work either.

I'm beginning to strongly suspect that my copy of Access has something very wrong with it.

I tried the same parameters as you and darkstar3 suggested on another machine, and it worked just as it should have, but on this machine, to no avail.

Oh well, that query will have to be done at some other juncture.