Access Problem

[chris567uk]

Hi Folks,
Quite a simple Idea, but I cant get it 100% -please help!!

A football club database I'm concerned with has 3 Tables:
1.Players: primary key= playerID (autonumber)
2Injury Clinic: 2x primary keys= player ID & Date of Injury
3Asessment Clinic: 2x primary keys= player ID & Asessment date

The [Injury Clinic] table holds all the data on the injury with the date it happened - "Date of Injury"
The Player may see the doctor or physio a dozen or more times with each injury, so the [Asessment Clinic] table holds the data for each subsequent visit made unique by "Asessment Date". Tables are joined by "Date of Injury"

The player ID field in the [Injury Clinic] table is a look-up field showing [firstname] [lastname] in a combo box to give a drop-down selection on the table (and entry form)

The Injury Clinic entry form has a subform for the subsequent visit data - filling in the [Assesment Clinic] table.

ok - now for the problem

The [Assesment Clinic] table will not show the players first or last names - just the ID number. How do I get it to autocomplete the players names from the Injury Clinic drop-down selection?

Help gratefully recieved
Thanks
Christine.

 

[jrbarnett]

If you have a combo or listbox with player ID on a form, set its rowsource to the following SQL (may need to adapt table name):

Select Players.PlayerID, Players.Firstname, Players.Lastname
From Players
Order By Players.PlayerID

Make sure the Number of columns property is set to 3 and the column widths are set appropriately.

Alternatively, use this as a lookup statement (look on the table design properties of the field).

As the player ID is the same across tables, it won't make any difference where it comes from.

John

 

[chris567uk]

Thanks Bob - It's much better now.
I get the firstname & Lastname showing up in the combo box now, but when I make a selection from the drop-down, only the lastname shows in the field. Better than a number tho!
Cheers
Christine.

 

[RamziSaab]

You need to go to the design of ur lookup table and change the column count from 1 to however many you want.