Change field type to lookup column

Can you (and if so, how?) change the data type of a populated field to lookup 
from a table without losing all the previously input data?
-- 
savannah
0
Utf
11/7/2007 7:31:01 PM
access 16762 articles. 3 followers. Follow

6 Replies
869 Views

Similar Articles

[PageSpeed] 41

Do not change the field in the table but in your form use a list box.
-- 
KARL DEWEY
Build a little - Test a little


"savannah" wrote:

> Can you (and if so, how?) change the data type of a populated field to lookup 
> from a table without losing all the previously input data?
> -- 
> savannah
0
Utf
11/7/2007 7:53:02 PM
Thanks Karl.

However, forms are not being used at this time. Data is entered directly on 
the form.  I do understand that this field should have been linked to the 
correct table from the start to provide the lookup column but... I inherited 
this, I didn't create it.
-- 
savannah


"KARL DEWEY" wrote:

> Do not change the field in the table but in your form use a list box.
> -- 
> KARL DEWEY
> Build a little - Test a little
> 
> 
> "savannah" wrote:
> 
> > Can you (and if so, how?) change the data type of a populated field to lookup 
> > from a table without losing all the previously input data?
> > -- 
> > savannah
0
Utf
11/7/2007 8:03:00 PM
On Wed, 7 Nov 2007 11:31:01 -0800, savannah
<savannah@discussions.microsoft.com> wrote:

>Can you (and if so, how?) change the data type of a populated field to lookup 
>from a table without losing all the previously input data?

Not at all easily, and as a rule you SHOULD NOT do so: see
http://www.mvps.org/access/lookupfields.htm for a critique of what many of us
consider a misfeature.

It's perfectly easy to use a Combo Box on a form, and to base a Report on a
query. Putting the lookup field in the table isn't necessary to do so.


             John W. Vinson [MVP]
0
John
11/7/2007 8:08:15 PM
On Wed, 7 Nov 2007 12:03:00 -0800, savannah wrote:

> Thanks Karl.
> 
> However, forms are not being used at this time. Data is entered directly on 
> the form.  I do understand that this field should have been linked to the 
> correct table from the start to provide the lookup column but... I inherited 
> this, I didn't create it.

 > forms are not being used at this time. Data is entered directly on the form. 

Oh really! If forms are not being used, how can you enter data on the
form? <gr>

It doesn't matter who created it. No one is pointing blame.
However you are the one modifying it. Do it correctly.

Tables are for data storage, not for data entry. Use a form! If you
like the table layout, simple set the Form View property to Datasheet.
A combo box placed on a form (including Datasheet View) is the correct
way to go.

Change the corresponding field datatype in the Table to the combo
box's bound column, the ID field. Make sure the table that contains
the combo box values has the correct field relationship to the field
in the main table. It's usually the ID field.
-- 
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail
0
fredg
11/7/2007 8:30:37 PM
Thanks for your help John.  I have read on a few web sites that lookup 
columns aren't the best way to go but, this is the first time I have seen a 
list of reasons why.
-- 
savannah


"John W. Vinson" wrote:

> On Wed, 7 Nov 2007 11:31:01 -0800, savannah
> <savannah@discussions.microsoft.com> wrote:
> 
> >Can you (and if so, how?) change the data type of a populated field to lookup 
> >from a table without losing all the previously input data?
> 
> Not at all easily, and as a rule you SHOULD NOT do so: see
> http://www.mvps.org/access/lookupfields.htm for a critique of what many of us
> consider a misfeature.
> 
> It's perfectly easy to use a Combo Box on a form, and to base a Report on a
> query. Putting the lookup field in the table isn't necessary to do so.
> 
> 
>              John W. Vinson [MVP]
> 
0
Utf
11/8/2007 4:43:02 PM
"fredg" <fgutkind@example.invalid> wrote in message 
news:1juqf9igl655f$.oby6qjmp2osf$.dlg@40tude.net...
> On Wed, 7 Nov 2007 12:03:00 -0800, savannah wrote:
>
>> Thanks Karl.
>>
>> However, forms are not being used at this time. Data is entered directly 
>> on
>> the form.  I do understand that this field should have been linked to the
>> correct table from the start to provide the lookup column but... I 
>> inherited
>> this, I didn't create it.
>
> > forms are not being used at this time. Data is entered directly on the 
> > form.
>
> Oh really! If forms are not being used, how can you enter data on the
> form? <gr>
>
> It doesn't matter who created it. No one is pointing blame.
> However you are the one modifying it. Do it correctly.
>
> Tables are for data storage, not for data entry. Use a form! If you
> like the table layout, simple set the Form View property to Datasheet.
> A combo box placed on a form (including Datasheet View) is the correct
> way to go.
>
> Change the corresponding field datatype in the Table to the combo
> box's bound column, the ID field. Make sure the table that contains
> the combo box values has the correct field relationship to the field
> in the main table. It's usually the ID field.
> -- 
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail 

0
netys49
11/9/2007 10:40:47 AM
Reply:

Similar Artilces:

Adding columns automatically based on worksheets
MVPs In a workbook, I have initially 2 sheets. Sheet1 contains information about sheet2 which is reflected in Column B. How can I automatically add a column (column C, D, etc..) of information if I add another sheet3, Sheet4, etc.. Meaning, I want to be able to freely add sheet3, sheet4 and in sheet1 columns would be automatically be created based on these new sheets. ...

Adding columns to item table
Are there any incompatibility issues I would need to worry about in adding additional columns to tables in the database? I'm already using most of the available fields in the item table and I would like a place to store each items ASIN assigned from amazon. I don't need to be able to read or write this in either the POS or the Manager, I just need to be able to access it through SQL queries. I would like to know the same thing. Has anybody done that without problems? <tfitts@gmail.com> wrote in message news:ce05743c-7ca2-49cc-89da-815d91c77e4d@1g2000prd.googlegroups.com......

Changing Item Tracking To Enable Serial Number Tracking
Through GP you cannot change the tracking option if an item has any on hand or on order quantity amounts. Has anybody changed the tracking option outside of GP and can tell me the other steps that need to be taken to build the serial number inventory? Thanks -- Charles Allen, MVP Charles: First, you need to do a decrease adjustment to move the items out of stock. The, change the ITMTRKOP field in IV00101 to '2' for those items you want to track. Finally, perform an increase adjustment to bring the material back in and record the serial numbers. Then I'd run reconcile on ...

formula change problem #2
The columns that the formula is referencing can change locations on th spreadsheet, if a user inserts a new column. To simplify the questio I put the actual column numbers in the formula but actually I will hav an integer defined that will hold the column number. That integer wil get populated by grabbing the column number from a named cell in tha particular column. I hope I explained that good enough. I was using the offset's in the formulas because that is what I am mos familiar with. Can offset's not be used in formulas? Is there a better way? Thanks -- cparson ---------...

[P2007]
Hi, I have a PM user who has just changed the login account password in AD and after that he is no longer able to login to PWA, an access denied message will show up instead. Even if I assign him as an administrator, he is still getting the same problem. He has no issue using Project Professional (with his account) to connect to the project server, it is just the PWA that he is unable to access. Did anyone encountered the same issue before? Regards Godrid Godrid: Most likely his desktop is sending the incorrect credentials. Verify that the user didn't save the passw...

company name change
Hello Our company has recently changes names. We already have two email domains currently in use, one set as primary for everything and another which was used in the past. We have one defined SMTP address, which is primary for all users. We also have another which was used int he past, but is not listed as a SMTP address in EXCH system mgr, recipients policies. Of course, I have a new domain to add as our primary smtp address and will need to make the current smtp address secondary for users. In EXCH system mgr, I do see CCMAIL and MS properties which appear to be our old email domain...

how do i change the color of a cell auotmatically?
I am desiging a data shee and i want to changes the color of a cell based on the valuse of anthor cell. ex. if "cell m34" is equal to 0 then "cell p21" is white, if cell m34 less than 1548, then "cell P21" is yellow Can this be done? If so, please help me out. Hi! Select cell P21 Goto Format>Conditional Formatting In the dropdown select Formula is Enter this formula in the box: =AND(M34<>"",M34=0) Click the Format button and select the style to apply then click OK. Click the Add button In Condition 2 Formula is: =AND(M34>=1,M34<=154...

average of percentages between 1% and 100% in a column
Hello, I am trying to get the average of percentages within a column, but would like to exclude 0% and 100% from the formula. This is the formula I thought would work: {=AVERAGE(IF(AND(L1:L988>0,L1:988<100)))} (I read in an earlier post to hit ctrl+shift+enter to apply this type of formula) Thanks! Here is a way using sumproduct that does NOT need array entering =SUMPRODUCT((E1:E100>0)*(E1:E100<1)*E1:E100)/SUMPRODUCT((E1:E100>0)*(E1:E100 <1)) or array enter this =AVERAGE(IF((E1:E100>0)*(E1:E100<1),E1:E100)) -- Don Guillett SalesAid Software donaldb@281.com &qu...

How do i set up a budget with self totalling column
I have made a basic budget with columns Date, Money in, Money out, Balance what I want to know is if there is a way for me to put a formula in the Balance column which will recognise if there is a number in the in r out column and if it is in the IN to add it to te previous balance and if it is in the OUT to minus it from the previus balance Thanks I hope someone can help Kristie I also have a buget with the same basic column as you do. Here is what you need to do. At the very top of the balance column, put a BAL FORWARD. This amount can be zero, but can also be equal to the last bala...

'To' field missing
When you open an email the "To" Field Is Not Displayed in Outlook Inside Outlook, try clicking on View Message Header "Ron" <youwishbabe@hotmail.com> wrote in message news:024001c368de$4bbd4730$a401280a@phx.gbl... > When you open an email the "To" Field Is Not Displayed in > Outlook ...

changing calendar appearance
I can not get the calendar day of the week to start on Sunday. Version of Outlook? Are you using Compressed weekends? You need to disable compressed weekends if you want Sunday at the beginning of the week. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips by email: mailto:dailytips-subscribe-request@lists.outlooktips.net EMO - a weekly newsletter about Outlook and Exchange: mailto:EMO-NEWSLETTER-SUBSCRIBE-REQUEST@PEACH.EASE.LSOFT.COM New Poll: What type of email acc...

Why does Outlook display text in yellow? How can i change it?
When pasting text into an e-mail, it appears in pale yellow. Sometimes, parts of meesages from others are also yellow. How can I prevent this? Are you using stationery by any chance? -- Kathleen Orland "Wise Dog" <Wise Dog@discussions.microsoft.com> wrote in message news:2D2FBAF7-2B2B-4C66-AE33-B2CA5A51B821@microsoft.com... > When pasting text into an e-mail, it appears in pale yellow. Sometimes, > parts of meesages from others are also yellow. How can I prevent this? ...

Auto Complete (auto fill) in Lookup Fields
Main table looks up to 2nd table, using this code: SELECT [1a-Emb Format-tbl_Formats].[ID], [1a-Emb Format-tbl_Formats].[Format] FROM [1a-Emb Format-tbl_Formats] ORDER BY [Format]; In form view, when typing a value from the list, the value "auto completes" fine until I close the database, re-open, add a new record and try to populate the field again. Even if i type the copy exact value from the lookup table list, an error results saying it doesn't match. I'm forced to click and scroll on the drop arrow to populate. When I remove the relationship from the ...

5000 character limit in data fields
Is there any way to extend the 5000 character limit for data fields? I have one field that will be at least 10000 characters regularly. It appears that I cannot make a custom field larger then 5000 characters though even with the ntext format. Just wanted you to know that we have just created a 5000 character ntext field in the Leads entity and get an error going offline with that data. Microsoft is working on a fix for this, but in the meantime we're stuck. Can you use the Notes Entity? "fbell@itstrategists.com" wrote: > Is there any way to extend the 5000 character...

Changing the data label position below the chart axis.
I'm charting some percentages. Many of them are negatives, but not all. The Y axis in my chart is more in the middle, instead of on the bottom, as with normal charts that have all positive numbers. The data labels are overlapping some of bars in the chart that are negatives. I can't get the data labels to move any further south. I've already changed the 'data label distance from axis' to the maximum of 1000. The data labels are still overlapping. Is there anything else I can try? The chart looks terrible with the labels partially on the negative bars, please help! H...

Make subform visible when a field has a particular value
Hi, I have a field in my form that is a text field, but will only be entered as Y or N. If the field is Y, I want a subform to be displayed, if it's N the subform can stay hidden. I can't work out exactly how / what to have the subform bound to to make this work. thanks JJ Use the link master field and link child field to link the subform to the main form. Use the after update event of the textbox to show or hide the subform. Use the current event to show or hide the subform when opening an existing record for editing. Jeanette Cunningham MS Access MVP -- Melb...

Cannot see fields in query design view on laptop; Access 2003
When in query design view on desktop (large screen), I can see the fields and criteria pane in the bottom half of the window, but when in query design view on laptop (small screen), I sometimes cannot see the fields and criteria pane no matter how far I scroll down. The only solution I have found so far is to move the fields and criteria pane to the top half of the window before closing the Access file on my desktop and before opening it on my laptop. Has anybody else run into this problem and found a way to view the fields and criteria pane if not immediately visible in the ...

Separate file with one field
Seems like fairly simple need but cant figure it out. How do i export the contents of One field of a table into a .csv or a text file, one record per line? Thanks Ramesh Create a query that returns only one field. Use TransferText (in code or a macro) to export the query. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Ramesh" <ramesh2020@gmaildotcom> wrote in message news:e6cgFNDKIHA.4880@TK2MSFTNGP03.phx.gbl... > Seems like fairly simple ...

can't change the data type or field size
I am creating/editing updating fields in table design. I am trying to change the field size of the first field. But getting message.. 'You can't change the data type or field size of this field, it is part of on or more relationships. I deleted the relationship and still get the message. Can I screw anything up if I delete any other relationships. Or does access re-connect all relationships on demand. Thanks for any replies. On Sat, 9 Jan 2010 17:12:01 -0800, Steve Stad <SteveStad@discussions.microsoft.com> wrote: >I am creating/editing updating field...

Change amount from number to figure form
I am making appointment letter for employees using mail merge option. In appointment letter I have to put salary in number form i.e. 121000 and also in figures i.e. One lakh twenty one thousand. I have a coloumn in excel where I have all the salary package in number form. Is it possible, for excel to automatically convert this number form salary into figure ? Any assistance would be greatly obliged. Jai Numbers to words Bob Phillips' site for help on this. http://www.xldynamic.com/source/xld.xlFAQ0004.html or Bernd Plumhoff's site http://www.sulprobil.com/html/spellnumber.htm...

How to change english numeric to arabic numerics?
I changed the language already, I can type arabic in MS word 2007, but arabic numerics is not coming, If MS word 2003 it was very easy to settle. ...

Pivot Table Page Field value from cell in another worksheet
I have looked through the other posts on the subject and the combination of my low skills in VB and the other solutions not exactly covering my situation means I still need to ask for help. Scenario: Worksheet a = "Scorecard" FieldName = "CustomerNumber" Worksheet b = "Products Resume" PivotTable = "PivotTable2" PivotTable Page Field = "Account Number" When CustomerNumber value on worksheet "Scorecard" is changed I need the Pivot Table Page field "Account Number" to use this value to filter the pivot t...

Use a Carriage Return when typing data into a text cell
What is the code for entering a carriage return in a text cell.? (not using a char map lookup) I tried "& char(10) &" and various combinations using alt, with no luck. What I want is to type Far[the_char_return_code]Farley[ENTER] resultng in Far Farley in the same cell. I know it exists, because a long time ago I found a spreadsheet that had it in the cell, and I copied the cell. I have been using that, but going to that worksheet and copying that character is a bit of a pain in the butt. There HAS to be a better way of doing it than that. TIA Far Farley The Profe...

Problem in creating a lookup field
I am creating a new entity "Decision Makers", in which Decision Maker is from Contact. for that, i have created 1-to-many relationship for Decision Maker field, but not able to get that field in the list of attributes while placing attributes on the "Decision Makers" form. Is it like that if we create 1-to-many relationship, then it does not show in the attribute list? if yes, then what is the alternatives? or it is possible to go for many-to-1 relationship... any one can please help me out.. ...

Report fields not printing
I have modified a Project Accounting Invoice Fromat Document Summary. All I did was move the invoice number and date into Group Break 2. When I run the report and print to screen the fields show up perfectly, but when I print the report they do not show up. Any suggestions? Thanks ...