Multiple criteria query handled in vba?

[ledgerr.rob]

Hello,

Access 2007
Windows Vista

I have a DB where i keep track of family heirlooms and valuables. As part of that i've allowed for the incorporation of images, although not every item has a picture but some items have multiple images. Because i only have the need to see one image i have included a 'primary picture' checkbox that is used to pick that image for displaying in reports.

So far I've filtered items in the query
-Category: Done with using selection from report generator form
-In Family Still?: Done by using checkbox value from item entry form

I don't know how to:
-Determine IF the item has a picture THEN
-If the item has pictures, display only the ‘primary’ picture: Done by using the primary checkbox value from the item entry form

ELSE
-If the item has no pictures, still display the item with no picture

END IF


I think i could do this in VBA and point the query at the module to run the loop for each record but i don't know how to make the link. Maybe i should do the whole query in vba but not sure how or if i could do that.

Can i do this using sql viewer?

I have it set up now where I only display items that have pictures but when i run my reports i'm obviously missing some items.

Thanks for any advice
rob

 

[Cronk]

My practice is never to store images in the database, just links to the files, otherwise the database file gets too big and could hit max size limits, especially with a large number of detailed images.

If you can have multiple photos for an item, create a new table, tblPhotos with the foreign key being the ItemID. An extra field can be used to indicate the Primary photo.

It's then easy on your Item form to indicate how many photos there are and two buttons can be used to cycle through the photo collection for an Item.

 

[ledgerr.rob]

Thanks for the response cronk.

I know i didn't mention this before but i have done just what you have suggested. I have only stored the path of the file in the database. I have done this in a tblPhoto that is linked to the tblItem. I have an 'item entry form' where i can view each item with any images present.

I was hoping to be able to query all the items in the database by their assigned category (ie kitchen, furniture, paintings, tools) then sort out the ones that are no longer in the families possession. Once that is done then determine if the item has images assigned and if it does display only the most representable image as picked on the entry form. If the item doesn't have an image, then i'd still like to display the item, just with no image...

I am starting to think i might need two separate querries?

rob

 

[Cronk]

One query should suffice. Make it a Left join so you include all items that have no associated photo.

Then either no photo will be displayed if there is none (ItemHeld=False) or if there is a photo, display the photo with the RepPhoto = true.

One issue for you to sort out is what mechanism will you have to ensure one any only one of the photos, if any exist for an item, is indicated to be the representative one/
?