Bound DropDown in Continuous Form (1 Viewer)

[Bechert]

Hello, I am developing an app for a photography competition.

I have two tables:
Photographer (TblPhotographerKey (primary), name, phone, etc.)
Results (TblResultsKey (primary), TblPhotographerKey as foreign key, score for each category.)

The Photographers table and Results table have a 1-to-many relationship based on the photographerkey.

I have a subform for recording the results. A photographer's results are recorded on a single row of the subform. A bound dropdown has the photographer's name and key. The photographer's name is displayed in the control.

When I select a photographer from the dropdown list on the subform it displays correctly but a duplicate record is added to the Photographer table with the photographer's name.
In the Results table the photographerkey (foreign) has the key that was added in the Photographer table.

Any help would be greatly appreciated.
Thanks.
Bill

 

[bob fitz]

The usual method of dealing with a one to many relationship is to have a main form bound to the table on the one side and a sub form on that main form bound to the table on the many side.
Is this what you have?
You have not mentioned a main form.
You would not need to enter the Foreign Key in the sub form if it is set up properly as this would be done by access automatically.

 

[CJ_London]

I have a subform for recording the results. A photographer's results are recorded on a single row of the subform.

this sounds like your data is not normalised - I would expect them to be displayed vertically (one score per record) for entering/changing scores.

You can always use a crosstab query to display the results horizontally for reporting purposes - but this will not be editable.

 

[Bechert]

Thanks Bob and CJ for your input. Sorry for time lag, been away.
Let me explain a little better.
The end result of this project is to display the average score of each photographer over the many years that each has entered photos. This would be a cross tab report for the most-recent 8 years:
2008 2009 2010 etc. Avg
Mary 21 23 24 22.7
Bob 23 24 25 24.0

There are 10 categories (landscape, people, etc.). A photographer may enter 1 to 10 photos each year (1 per category).
The information is on paper reports from the past 8 years.
There are several sheets for each year. It would be easier to enter the information by photographer / year since that is how the results are organized on paper. Finding the same photographer in all this paper would be very time-consuming.

Paper reports are organized like this:
photographer year cat1 cat2 cat3 cat4 cat5 etc.
Mary 2010 19 21 22 0 20
Bob 2010 23 24 27 21 22

I want to create a data entry form to capture the results for all the photographers for a given year, then move on to the next year.

Any suggestions would be greatly appreciated.
Thanks,
Bill

 

[bob fitz]

Bechert

I agree with Chris regarding your data not being normalized.
The file attached may be of interest: