Guidance on Form/Query please

[Lensmeister]

Hi,

I hope someone can point me in the right direction.

I have a Access 2003 DB in it I have a table (tbl_Coredata) in the table are the fields

Season, Date, Home, Away, HG, AG, (plus some others.)

I want to have a form created that has a combo box on it. When the person chooses a value from the combobox it shows in the for the results of that item in a data sheet view in that same form. Now if you choose Fred there are occurrence of Fred in both the home and away fields.

How do I do it?

Thanks in advance.

 

[gemma-the-husky]

either a union query, or resdesign (better)

union query = select HOME data UNION select AWAY data

redesign =

match table = with some of your data
match participants with 2 rows for each match
- team home/away indicator

then all you have to search is a single table, and you dont need a union query.

matter of taste really. However, its getting used to relational thinking though. A match isnt really a match between a home and away team. A match is an event on a given day. The match has a 1-many (but specfiically 1-2) relationship between the participants, in terms of the data

think of it as a horse race

the event is the Grand National, or the Derby
the runners are all the particpants in the race.

you dont have a separate column in the race table for runner1, runner2 etc etc
you have a separate table with all the runners.

 

[Lensmeister]

Dave (aka Gemma),

Thanks very much. I will have a play with this late on tonight and see what I can do with it.

Uncle,

Thanks you for you comments on the naming conventions.