modifying table to include add'l field based on extant field

[uscitizen]

here's the long and short of the story:

i have a table called 'Protocol' which has a field called 'SponsorID'. it is a number field and uses a combobox with a rowsource of table/query. the table that it looks at is called 'Sponsor' having two fields one numeric (also called 'SponsorID', this is column #1) and the other (column #2) is called 'Name', a text field.

i didn't create this and i would've just had a single column in 'Sponsor' since the number field is just an autonumber field w/o any intrinsic meaning.

what i'd like to do, is re-engineer the 'Protocol' table so that its 'SponsorID' field was a text field that looked at a single-column rowsource table having just 'Name's in there.

i've tried a query based approach to create a clone of 'Protocol' having a field with the property wanted, however, there are quite a few 'gotchas', i.e. the checkbox fields have disappeared to be replaced with 0 and -1s, the dropdown arrows have disappeared from other fields where they were being used to help the user and whathaveyou. so, this approach would require quite a lot of work if i wanted to restore the original capability that 'Protocol' had prior to making the change just discussed.

if there's a better way, would someone like to share it with me?

 

[dhookom]

I recommend:
-always store the ID type value unless the title descriptions are so cut & dryed they will never change. I might use "Male" and "Female" but not much else.
-don't ever use lookup fields in table definitions. Create and use combo boxes on forms.
-never allow users to view your tables (use forms and reports)
-It doesn't make any difference if a checkbox appears in a table. Again, create forms and reports.
-Don't create field names like "Name", "Date", "Time", etc since these are either function names or properties.

Duane
MS Access MVP

 

[uscitizen]

good stuff (and i would've if i could've but this was designed by an absent predecessor so it's no use crying over spillt milk).

if this forum has a fault, i think it's that once you submit your tale of woe it can't be retracted in the event you serendipitously/miraculously solve the problem that brought you to post it. such has been the case since posting this inquiry.

the problem stemmed from the fact that the absent predecessor's look up table was a two column affair containing a meaningless autonumber and meaningful name, the autonumber being bound to the table and the primary key as well. the folks using the table had added additional rows to the table over a period of months and being unsophisticated weren't aware about the problems that that could cause.

so i changed the primary key from number to name, thus forcing the names to arrange themselves in alphabetic order while still allowing the bound column to belong to the number field -- table doesn't need to be recreated and all's well that ends well.

thanks!

 

[dhookom]

You can specify a sort order in the Row Source:
SELECT SponsorID, [Name]
FROM Sponsor
ORDER BY [Name];
This allows you to keep the same primary key.

Here is a link to some best practices and you want you can click the link to the evils of lookup fields:

http://www.mvps.org/access/tencommandments.htm

Duane
MS Access MVP