Updating Multiple Records

I have a table in a database that contains all my photos. The fields are
like Name, SRC, GalleryName, DateAdded, SpecialStyle. The only one you may
be confused about is Special Style. I include that field in my databases so
I can overrid some CSS settings for particular items.

To display a listing of the galleries (to then go on and select a specific
picture) I had a query that just finds all the distinct gallery names from
the Photo table. I have also set that query to group by SpecialStyle so that
listing will show up as red for example. Is it possible, through the query,
or some other access technique to get color: red into the query called
GalleryListings, or to update all the photos with that particular gallery
with that in their specialstyle item so it then groups by that in the query?

Jrodan

EXAMPLE:

http://www.tufts2006.com/photo_album.asp

You'll see in the dropdown for Molsen night that it lists two items for the
same. One red and one yellow. This is because only one of the pictures from
the photo table has the red as its special color, thus the query groups by
that one, and then the blank ones. Of course, I could solve this by setting
the color for all of them as red and it would group into one, but thats too
hard to do!

Jordan

"Jordan Marton" <JMarton (AT) HAHAmarketaxess (DOT) com> wrote

I have a table in a database that contains all my photos. The fields are
like Name, SRC, GalleryName, DateAdded, SpecialStyle. The only one you may
be confused about is Special Style. I include that field in my databases
so
I can overrid some CSS settings for particular items.

To display a listing of the galleries (to then go on and select a specific
picture) I had a query that just finds all the distinct gallery names from
the Photo table. I have also set that query to group by SpecialStyle so
that
listing will show up as red for example. Is it possible, through the
query,
or some other access technique to get color: red into the query called
GalleryListings, or to update all the photos with that particular gallery
with that in their specialstyle item so it then groups by that in the
query?

Jrodan

You'll see in the dropdown for Molsen night that it lists two items for
the
same. One red and one yellow. This is because only one of the pictures
from
the photo table has the red as its special color, thus the query groups by
that one, and then the blank ones. Of course, I could solve this by
setting
the color for all of them as red and it would group into one, but thats
too
hard to do!

You want to update the table's SpecialStyle column to a certain style for
the specific gallery?
For example, Molsen is the name of a photo album, and it has 2 photos in
it...so thats 2 records..

Name | GalleryName | SpecialStyle
User1 | Molsen | Red
User1 | Molsen | Yellow

Thats as it is now, and you want to update the SpecialStyle for all
galleries to one certain color?

--
Ron

Well the way it is now...

Name Gallery SRC Stlye
Photo1 Hawaii photo1.jpg color: red
photo2 Hawaii gifs/photo2.gif color: red
....

And then my query would list all of the galleries it finds.

GalleryName NumPhotos Style
.... ... ...
Hawaii 2 color:red
.... ... ...

So if all the photos in a particular gallery have the same style, then it is
represented in the query. But if one photo has the color and rest do not, I
get this as my query for gallery names:

.... ... ...
Hawaii 1 color:red
Hawaii 1
.... ... ...

Because technically it is grouping by the style as well. So what I'm looking
for is a way to quickly update ALL the photos for a specific gallery so I
don't end up with my second scenario by accident.

Jordan


"Ron" <ron0079 (AT) spamaol (DOT) com> wrote

You'll see in the dropdown for Molsen night that it lists two items for
the
same. One red and one yellow. This is because only one of the pictures
from
the photo table has the red as its special color, thus the query groups
by
that one, and then the blank ones. Of course, I could solve this by
setting
the color for all of them as red and it would group into one, but thats
too
hard to do!

You want to update the table's SpecialStyle column to a certain style for
the specific gallery?
For example, Molsen is the name of a photo album, and it has 2 photos in
it...so thats 2 records..

Name | GalleryName | SpecialStyle
User1 | Molsen | Red
User1 | Molsen | Yellow

Thats as it is now, and you want to update the SpecialStyle for all
galleries to one certain color?

--
Ron

I see what your getting at...don't have an answer yet, but consider this
database layout:

TableName: Galleries
GalleryID (primary key)
OwnerID (user id, foreign key)
GalleryName
Style

TableName: GalleryImages
ImageID (primary key)
GalleryID (Galleries.GalleryID, foreign key)
ImagePath

With that, you can imagine this scenario:
User registers, goes to Create Gallery
User enters Gallery information (gallery name, selects style)
User goes to Upload Images
Images submitted to GalleryImages with the newly created GalleryID

User goes to Admin My Galleries
User selects a Gallery from a list of his/her galleries
Images are shown for that gallery allowing to update/delete

If it ain't too late to rethink the database layout, consider that one.

As it is now, that table has redundant data...meaning there are many records
with the same gallery name and same style in the table. The practice of
"normalization" is meant to prevent that. Sometimes its not possible, or
feasible to prevent redundant data, but I think in this case it is the
correct choice. The alternative layout suggested above uses "foreign-key
mapping" to associate the Gallery to the GalleryImage(s).

As for the existing problem, the only thing I can think of is to use the
query builder to run an update query on each record for a specific gallery,
updating the Style to the chosen color. The query would look like this in
Access SQL View:

UPDATE Galleries SET Style=["Enter Style"] WHERE GalleryName = ["Enter
name"]

When you click the run button, you'll have to enter the desired style
("red"), then the gallery name ("Hawaii"), and it will update the rows
appropriately for that gallery. You'll have to do that for each GalleryName.

If you want to try the alternative layout, then you'll need to figure a way
to import the existing data to the new tables...have to try out the various
Query types in Access to do that.

--
Ron

Thanks for your suggestion!!!

I think that I was thinking of something like that a few weeks after I made
the first version of my site, but had decided it would be too difficult to
change everything at that point. HOWEVER, now that I am re-designing and
re-building the site from scratch, I suppose I could put a little extra
effort in and re-design that part of the database. It wouldn't be too
difficult, I'll just export stuff out into excel, and then copy it all back
in to a better table layout.

THANKS!

Jordan

"Ron" <ron0079 (AT) spamaol (DOT) com> wrote

I see what your getting at...don't have an answer yet, but consider this
database layout:

TableName: Galleries
GalleryID (primary key)
OwnerID (user id, foreign key)
GalleryName
Style

TableName: GalleryImages
ImageID (primary key)
GalleryID (Galleries.GalleryID, foreign key)
ImagePath

With that, you can imagine this scenario:
User registers, goes to Create Gallery
User enters Gallery information (gallery name, selects style)
User goes to Upload Images
Images submitted to GalleryImages with the newly created GalleryID

User goes to Admin My Galleries
User selects a Gallery from a list of his/her galleries
Images are shown for that gallery allowing to update/delete

If it ain't too late to rethink the database layout, consider that one.

As it is now, that table has redundant data...meaning there are many
records
with the same gallery name and same style in the table. The practice of
"normalization" is meant to prevent that. Sometimes its not possible, or
feasible to prevent redundant data, but I think in this case it is the
correct choice. The alternative layout suggested above uses "foreign-key
mapping" to associate the Gallery to the GalleryImage(s).

As for the existing problem, the only thing I can think of is to use the
query builder to run an update query on each record for a specific
gallery,
updating the Style to the chosen color. The query would look like this in
Access SQL View:

UPDATE Galleries SET Style=["Enter Style"] WHERE GalleryName = ["Enter
name"]

When you click the run button, you'll have to enter the desired style
("red"), then the gallery name ("Hawaii"), and it will update the rows
appropriately for that gallery. You'll have to do that for each
GalleryName.

If you want to try the alternative layout, then you'll need to figure a
way
to import the existing data to the new tables...have to try out the
various
Query types in Access to do that.

--
Ron