IIS ASP - HELP, How do I do this??

Author

HELP, How do I do this??

the other john

2005-07-17, 5:49 pm

This should be fairly basic but I can't think of how to do this and I'm
running out of time!

I am developing a picture gallery and I can't figure out how to select
"one" picture from each gallery. My DB is configured like this...

tbl_pictures
this contains all pictures in the picture gallery.

tbl_pictures_galleries
this contains all the picture gallery names that can be created by the
client. It's primary key acts as a foreign key in the tbl_pictures
table.

My problem: I don't know how to select the latest picture from each
gallery (identified by FK) via a loop. This should be so simple but
I'm just not seeing it right now.

Help greatly appreciated!!

Thanks!!!

Bob Barrows [MVP]

2005-07-17, 8:47 pm

the other john wrote:
> This should be fairly basic but I can't think of how to do this and
> I'm running out of time!
>
> I am developing a picture gallery and I can't figure out how to select
> "one" picture from each gallery. My DB is configured like this...
>
> tbl_pictures
> this contains all pictures in the picture gallery.
>
> tbl_pictures_galleries
> this contains all the picture gallery names that can be created by the
> client. It's primary key acts as a foreign key in the tbl_pictures
> table.
>
> My problem: I don't know how to select the latest picture from each
> gallery (identified by FK) via a loop. This should be so simple but
> I'm just not seeing it right now.
>

In a truly relational database, you would need to have a field in the
tbl_pictures table to store when the picture was stored so you could use it
to identify the "last" picture for each gallery. However, this is one of the
cases where Access is not truly relational. JetSQL has an aggregate function
called "LAST". Here is what the query would look like, using my guesses as
to your field names (it would have helped to show us your field names as
well as their datatypes):

SELECT g.GalleryName, Last(p.PictureID) AS LastOfPictureID
FROM tbl_pictures_galleries AS g INNER JOIN
tbl_pictures AS p ON g.GalleryID = p.GalleryID
GROUP BY g.GalleryName

If you do have a field (perhaps named DateAdded) to truly identify the last
record for each gallery, you will get more predictable results using this
query:

SELECT g.GalleryName, p.PictureID
FROM (tbl_pictures_galleries AS g INNER JOIN
tbl_pictures AS p ON g.GalleryID = p.GalleryID) INNER JOIN
(SELECT p.GalleryID, Max(p.DateAdded) AS LastDate
FROM tbl_pictures AS p
GROUP BY p.GalleryID) as q
ON p.GalleryID = q.GalleryID and p.DateAdded=q.LastDate

HTH,
Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Bullschmidt

2005-07-20, 8:47 pm

Well if each "gallery" is a separate table (instead of just different
data for one field in the main table) then you may need to use UNION
somehow in your SQL statement.

Best regards,
J. Paul Schmidt, Freelance ASP Web Developer
http://www.Bullschmidt.com
ASP Design Tips, ASP Web Database Demo, Free ASP Bar Chart Tool...

<<
This should be fairly basic but I can't think of how to do this and I'm
running out of time!

I am developing a picture gallery and I can't figure out how to select
"one" picture from each gallery. My DB is configured like this...

tbl_pictures
this contains all pictures in the picture gallery.

tbl_pictures_galleries
this contains all the picture gallery names that can be created by the
client. It's primary key acts as a foreign key in the tbl_pictures
table.

My problem: I don't know how to select the latest picture from each
gallery (identified by FK) via a loop. This should be so simple but
I'm just not seeing it right now.

Help greatly appreciated!!

Thanks!!![vbcol=seagreen]

*** Sent via Developersdex http://www.codecomments.com ***