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.

 

[Uncle Gizmo]

"Date" is not an acceptable name for a Field.. It has the potential to cause you massive problems later in your database construction!

Access has a list of “reserved words”, that is words which are used by access itself. Date and Name, are a couple of the common ones, there are many more.

It is difficult to know which words are “reserved” unless you constantly checked the list, however bear in mind even if you manage to avoid the current reserved words, new reserved words may be added to the list at a future date, if this happens, again you could have serious problems with your database.

Bearing that in mind it is a good idea to “never” use a real word for something like a field name, and indeed any other places within your database.

One of the solutions is to always use a naming convention, for a field name you could prefix the field name with fld = fldDate, and for a form you could prefix the form name with frm = frmMyForm.

By following this simple rule you can help prevent your database crashing and you wondering what the hell happened, sat around scratching your head waiting days for someone to respond to your post!

 

[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.