3 super easy Access SQL questions

[jimbob90]

MyTable:

People who moved from Blah City, Blah State:

Name Location Date Kids
Jack LA 02/05/1999 0
Cindy Chicago 12/15/2005 2
Randy LA 06/19/2003 3
Jason Seattle 04/06/2002 0

1. Who moved to LA?

SELECT Name, Location
FROM MyTable
ORDER BY Location;

Expected result:

LA Jack, Randy

What I got:

LA Jack
LA Randy

2. Who has the most kids?

SELECT Max(Kids) AS MostKids
FROM MyTable;

Expected Result

MostKids
Randy

Actual Result

MostKids
3

Note: Not sure how to 'navigate' columns

3. WHo moved Before 2003

SELECT Name FROM MyTable WHERE Date<='1/1/2003';

Expected Result:

Jason, Jack

Actual Result:

NOTHING!!!!

Note: The date is in fact of data type Date/Time. Do i need to enclose the date in quotes?

[BitBlt]

The second two are pretty easy. The first one, I think your expectation is inaccurate. I'll explain at the end after we tackle the second two.

For selecting "most kids", keep in mind that you only retrieve the columns you list. So unless you include "name" in the select list you won't see it. Try this:

(NOT MS SQL SYNTAX...MSACCESS SQL...they are different dialects!)
select name, kids 
from mytable 
where kids = 
(
select max(kids) from mytable
)

This uses a subquery to restrict the rows returned to only those where the number of kids is the biggest in the table.

For using date literals in Access SQL, I believe you have to enclose the date in pound-signs rather than quotes.

(NOT MS SQL SYNTAX...MSACCESS SQL...they are different dialects!)
SELECT Name FROM MyTable WHERE Date<=#1/1/2003#;

It's goofy, I know, but you can confirm this in the Access help file.

Okay, now that those are out of the way, let's talk about the first one.
First, you don't show anything in your SQL to restrict location to LA. You would do it like this:

(NOT MS SQL SYNTAX...MSACCESS SQL...they are different dialects!)
select name, location 
from mytable
where location = 'LA'

Result:
name location
Jack LA
Randy LA

Notice that the columns show up in the same order as they appear in the select statement.
When you issue a Select statement, you are really listing the names of the data values you want to see on each line. Think of a spreadsheet with headers across the top, and each data value appearing beneath. So a simple select will give what you showed above as your result, no more and no less. That being said, you can perform some "SQL gymnastics" to get the result you want, but it's not easy. But you can sort of get what you're looking for like this:

(NOT MS SQL SYNTAX...MSACCESS SQL...they are different dialects!)
select name as LA from mytable where location = 'LA'
result:
LA
Dave
Randy

This is kind of cheating, because the heading in the result has been "renamed" to display LA and the names are listed below. If you REALLY want it to look like what you showed above you'd have to put a lot more work into it.

Can it be done? Sure. Is it worth the time and effort? Only you can decide that.