the said:
This is a query I ran in query builder. It doesn't do everything I
want it to do yet but I have some questions....
SELECT tbl_Pictures.FK_picture_galleryID,
tbl_Pictures_Galleries.PK_Picture_Gallery_ID,
tbl_Pictures.fld_picture_DateTime,
tbl_Pictures_Galleries.fld_Picture_Gallery_Name
FROM tbl_Pictures_Galleries INNER JOIN tbl_Pictures ON
tbl_Pictures_Galleries.PK_Picture_Gallery_ID =
tbl_Pictures.FK_picture_galleryID
WHERE (((tbl_Pictures_Galleries.PK_Picture_Gallery_ID)=5))
ORDER BY tbl_Pictures.fld_picture_DateTime DESC;
what's with all the parentheses??
Yeah, I know. The Access Builder takes no chances. Everyting it considers to
be an expression is surrounded by parentheses to prevent the query engine
from misinterpreting anything. The Query Builder does not always know
exactly what you mean so it takes no chances. The first thing I used to do
when using the Query Builder after switching to SQL View was: remove all the
damn parentheses ... except for the ones surrounding the joins in the FROM
clause - those seem to be required by Jet. Anyways, the above generated SQL
would be changed by me to (you don't seem to like using the shorter aliases,
so I'll leave the generated alieases used in the field qualifications
alone):
SELECT tbl_Pictures.FK_picture_galleryID,
tbl_Pictures_Galleries.PK_Picture_Gallery_ID,
tbl_Pictures.fld_picture_DateTime,
tbl_Pictures_Galleries.fld_Picture_Gallery_Name
FROM tbl_Pictures_Galleries INNER JOIN tbl_Pictures ON
tbl_Pictures_Galleries.PK_Picture_Gallery_ID =
tbl_Pictures.FK_picture_galleryID
WHERE tbl_Pictures_Galleries.PK_Picture_Gallery_ID=5
ORDER BY tbl_Pictures.fld_picture_DateTime DESC;
Also, I tried to filter by using MAX() on the DateTime field but got
an error that said...
"You tried to execute a query that does not include the specified
expression'FK_picture_galleryID' as part of an aggregate function". I
don't know what this means. I know that aggregate means to summarize
but I don't know why this won't work.
Aggregate = result of summary (aggregate) function such as SUM,
MAX,MIN,FIRST, etc.
You cannot aggregate without using GROUP BY, unless all the fields in the
SELECT clause are aggregates. This also applies to the WHERE clause. In
fact, an aggregation cannot be used in the WHERE clause. If you need to
filter on an aggregate, it needs to be done in the HAVING clause. See the
link I cite below.
And if you do use GROUP BY, the only non-aggregates allowed in the select
are the columns listed in the GROUP BY clause.
Here's an old post I made that hopefully explains this:
*****************************************
Say you have a table with two columns containing the following 4 rows of
data:
Col1 Col2
1 28
1 33
2 5
2 8
Now you decide to create a grouping query:
select Col1 From table Group By Col1
You would get these results:
1
2
Now you decide to add Col2 to the select list:
select Col1,Col2 From table Group By Col1
Here are the results:
1 ?
2 ?
What do you replace the ?'s with? Do you see the problem?
Bottom line: You have to tell the query engine how to aggregate Col2 (min,
max,sum, avg,count, etc.) so that you wind up with a single row for each
value in Col1.
Even if Col2 contains the value 5 in every row, you still have to tell the
engine how to aggregate it. The query parser does not know what's in your
table: all it can see is that there is a column in the select list that
needs aggregation.
***************************************************
You should also read this:
http://groups-beta.google.com/group/comp.databases.ms-sqlserver/msg/ec194ab2109662c8
Also, I'll have to dynamically loop through the gallery ID's in the
WHERE clause.
I don't understand.
Do I just open a seperate recordset for these and embed
the statement I've been working on above within it?
I don't know. I don't understand what you're trying to do.
Thanks again! My deadline is getting close and I'm starting to sweat
now.
Sorry, but if you're looking for me to write it for you, I've got my own job
to do ... ;-)
Bob Barrows
