Help with subform and Happy Holidays 1

[briansdell]

Help, I have created a database to track my digital photos and have run into a road block. I want to create a subform on my contest form that show all images I have submitted to the contest currently displayed. The problem is that in the photo table I have 4 fields contest1 - contest4 when is submit a photo to a contest I add in in one of these fields. One image can be out to several contest this is why I created the 4 fields. If I create the subform and have it match contest1 only everything works great I can scroll through the contest form and the subform matchs everything I entered into contest1. However when I ask it to look at all 4 fields the subform show up blank I can scroll through the contest form and nothing.
Why wont the subform look at all 4 fields and show me what is valid and skip what is blank?

Thanks to everyone in advance for the help and happy holidays to everyone.

Brian

 

[GingerR]

Sounds like your tables are not normalized, so I'd say this is the first place to start. You'd need a table listing the contests, one holding each photo, and a third table that relates the contests to the photos (many-to-many relationship)--something like this:

Table: Contests
Field: ContestID (Autonumber)
Field: ContestName
Field: ContestDate

Table: Photos
Field: PhotoID (Autonumber)
Field: PhotoDesc

Table: ContestPhotos
Field: ContestID (Number, from table Contests)
Field: PhotoID (Number, from table Photos)

Then your main form will be based on table Contests, showing one contest at a time.
Your subform will be based on a query based on table ContestPhotos joined with table Photos on PhotoID, with Child/Master links of ContestID. This way your subform will show photos from the related ContestID.

The way you have it set up is just not good relational db design. Every time you add another photo (contest5, contest6, contest7....) you have to go thru every object in the entire db (such as your existing subform) and put the additional field on the object to accomodate your new photo.

 

[briansdell]

Thanks, I already have two tables one that contants the contest information and one that contains the all the photo information. If I understand you correctly I need to add a third table that is related to the first to for the subform to work proplery?

Thanks
Brian

 

[GingerR]

Yes..a third table with two fields, ContestID and PhotoID.
these will be filled with ID's from your other two tables, i.e.

ContestID PhotoID
1 1
1 2
2 1
2 3

so ContestID 1 had two photos, 1 and 2.
Contest 2 had two photos, 1 and 3.

THis way you can relate as many contests to as many photos as you wish, and your subform will click thru them with the naviagation buttons.

 

[briansdell]

Sorry but I am a bit lost, My contestID and PhotoID are autonumber feilds in my existing table can't I just create a relations ship between them and do the same thing?

Brian

 

[GingerR]

this IS creating a relationship between them. A properly-built relational-database type of relationship. Seems like you don't like my idea?

Maybe I am misunderstanding how you have things set up. Please post your table structures. I think you have something like this:

PhotoID COntest1 Contest2 COntest3 COntest4
1 1 2
2 1 3
3 2 4 5

right? so photo1 was in contests 1 and 2, etc?

i'm guessing your main form is based on the field Constest1, So the subform will only show related records for the items in the field Contest1. It's just not designed properly.

When you said above "why can I just create a relationship between them and do the same thing?"....have you tried that? what are your ideas on how to do that? Seems to me you are posting on this forum because it's not working.

 

[briansdell]

Thanks, I would be happy to send you the tables so you can see what the look like. If that will work let me know, Otherwish thanks for the effort and happy holidays.

Brian

 

[GingerR]

Hi Brian--You can just list your table structure here, as I did above. Table name, fields and sample data.

Have you tried sorting it out like I suggested?

They suggest here that we do not give out personal info (such as email addresses). Plus if we finish this up here it could help someone else.