can't change data type error

[merlin777]

I'm building my first database.

I've created 4 tables and joined them with relationships.

I have a table of therapists and another table listing the type of disciplines they might have (nurse, physio, OT etc).

I think I have to add some kind of lookup list to the disciplineID field in the therapists table. so I can choose one of the disciplines available for the therapists.

I tried choosing 'lookup wizard' for the data type for the disciplineID field but I got a 'can't change data type....' error which said I'd have to delete the relationships first.

I tried to delete the relationship between the tables and I got the error 'the database engine could not lock the table etc'.

So, my questions are:

am i approaching this the correct way (to get a lookup list for data entry)
if not, what do I need to do!

 

[moke123]

You should not have a lookup field in your table. although it may make your table more readable it will cause numerous headaches in the future. google- the evils of lookup fields

 

[merlin777]

Thanks for that. What alternatives are there?

 

[arnelgp]

you can use a junction table, if you are unable to use lookup field.

as i've said lookup field are evil for those who don't know how to use them.

 

[merlin777]

you can use a junction table, if you are unable to use lookup field.

as i've said lookup field are evil for those who don't know how to use them.

Thanks - I'll research junction tables.

Is this a common question/problem for newbies? Is my general approach wrong?

I had intended to build tables, add relationships, add some example data in datasheet view then go on to build forms and queries. Is that a sensible sequence?

 

[arnelgp]

you're doing just fine.
even if you use the old pencil and paper method, still there will be something missing as you your db goes live.

 

[merlin777]

you're doing just fine.
even if you use the old pencil and paper method, still there will be something missing as you your db goes live.

thanks for that!

 

[merlin777]

I've been reading up on junction tables but i can't work out how they replace lookup tables?

For instance, I can't see how they would display a list to pick from.

 

[arnelgp]

the list will be done through form, using combobox.

form's recordsource=table joining your junction table.

your control for the field in junction table(combobox)
your controlsource - table field in your junction table (linked in query using join).
your row source = the field/fields from one of your table.

i think it will not be possible to use multiselect on the combo for this setup.

 

[merlin777]

the list will be done through form, using combobox.

form's recordsource=table joining your junction table.

your control for the field in junction table(combobox)
your controlsource - table field in your junction table (linked in query using join).
your row source = the field/fields from one of your table.

i think it will not be possible to use multiselect on the combo for this setup.

So in general terms, I have to introduce forms earlier on in my intended sequence to be able to safely select data from a list?

Do you think I'm trying to enter sample data too early as well?

 

[moke123]

I've been reading up on junction tables but i can't work out how they replace lookup tables?

theres a big difference between lookup TABLES and lookup FIELDS. You would use a lookup TABLE to populate your combo. A lookup Field actually creates a hidden table which stores a number even though the field will display text.
here's a quick example of how you could use a junction table, if in fact you need to show multiple disciplines for each Therapist.

 

[merlin777]

theres a big difference between lookup TABLES and lookup FIELDS. You would use a lookup TABLE to populate your combo. A lookup Field actually creates a hidden table which stores a number even though the field will display text.
here's a quick example of how you could use a junction table, if in fact you need to show multiple disciplines for each Therapist.

wow - I'm trying to get my head round that! When I saw the junction table with only numbers in it I freaked....