Drop Down Problem

Hi All!

I've got a list of companies which is part of a single table database.
Data for the people who work for which company are stuck within the
database.

Eventually I want to separate out the companies, assign them unique
codes, thin them out so there are no duplicates, then stick them in
table marked Companies. I then hope to link them to the respective
employees in some kind of set up which will allow me to quickly and
easily change the company people work for.

I am setting up a form to see the data. The problem is that if you
make up a query fromtwo different tables there appears to be no way to
edit the resulting data - even when it is in a form. Can anyone tell
me how to get things so that I can actually edit the link between a
person and their company?

Also, when I'm doing the form, I would like a drop down box which both
displays an alphabetic list of the companies (presumably from the
table) while allowing me to add a new one as I need. Anyone know how
this can be done?

Cheers
DF-B

"David F-B" wrote in message
...
Hi All!

I've got a list of companies which is part of a single table database.
Data for the people who work for which company are stuck within the
database.

Eventually I want to separate out the companies, assign them unique
codes, thin them out so there are no duplicates, then stick them in
table marked Companies. I then hope to link them to the respective
employees in some kind of set up which will allow me to quickly and
easily change the company people work for.

I am setting up a form to see the data. The problem is that if you
make up a query fromtwo different tables there appears to be no way to
edit the resulting data - even when it is in a form. Can anyone tell
me how to get things so that I can actually edit the link between a
person and their company?

Also, when I'm doing the form, I would like a drop down box which both
displays an alphabetic list of the companies (presumably from the
table) while allowing me to add a new one as I need. Anyone know how
this can be done?

Cheers
DF-B

David

Another approach might be to build a query of the companies and set the
Unique Values property to Yes. This would give you a short(er) list of
companies, which you could then use an append query (or change your query to
an append query) to add to your new tblCompany.

You haven't provided any description of what your current or intended table
structures are like, so it's a little difficult to offer more specific
suggestions.

--
Good luck

Jeff Boyce
Access MVP

See in-line.

--
HTH
Van T. Dinh
MVP (Access)


"David F-B" wrote in message
...
Hi All!

I've got a list of companies which is part of a single table database.
Data for the people who work for which company are stuck within the
database.

Eventually I want to separate out the companies, assign them unique
codes, thin them out so there are no duplicates, then stick them in
table marked Companies. I then hope to link them to the respective
employees in some kind of set up which will allow me to quickly and
easily change the company people work for.

I am setting up a form to see the data. The problem is that if you
make up a query fromtwo different tables there appears to be no way to
edit the resulting data - even when it is in a form. Can anyone tell
me how to get things so that I can actually edit the link between a
person and their company?


If you want the Query to be updateable (so that you can edit the data), you
need to join the 2 Tables by the Primary Key - Foreign Key pair. See next
part for perhaps, more appropriate set-up.



Also, when I'm doing the form, I would like a drop down box which both
displays an alphabetic list of the companies (presumably from the
table) while allowing me to add a new one as I need. Anyone know how
this can be done?

This can be achieved using a "search" ComboBox with Not-In-List using a Form
/ Subform combination where the Main Form is based on tblCompany and the
Subform is based on tblPeople.

OTOH, you are much better to decide on the Table Structure before worrying
about the GUI. You may need to consider whether you should use One-to-Many
or Many-to-Many first before you can decide on the GUI.


Cheers
DF-B