Results 1 to 13 of 13
  1. #1
    rhubarb is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    76

    Changing Data Type of Imported Field Name


    I've had to change the underlying data type of a major table in my database. I export it to my new database but when I change the type from "text" to "lookup" the value from the table I created it drops all existing data. I have about 19k records here that would need to then be updated. Is there a way I can keep the data but change the underlying definition?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Are you familiar with this link? http://access.mvps.org/access/lookupfields.htm
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  3. #3
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    8,947
    Keep the text. Dont use lookup. (it causes headaches)

  4. #4
    rhubarb is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    76
    No, I was not. I don't suppose it lists alternatives? I suppose a one-to-one relationship would be one, right?

  5. #5
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    Having a Lookup (ComboBox) on a form is just fine. Having them in a table can be a nightmare. Are you trying to create a Lookup Table for your other tables to use?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  6. #6
    rhubarb is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    76
    Okay let me try to explain what I was doing. I have a tickets table that has a field for job address. This job address field can have duplicates as it is entered multiple times as there could be multiple tickets associated to a single address. The job address in this table is a text field so that's fine. The problem is this was made before I needed a customer table so this is the only place the job address resides. So now I'm making a customer table how can I get all my distinct job addresses from the tickets table into this new table. I rather have the tickets table job address field reference the customer table job address field and not the other way around, but the former was created first unfortunately.

  7. #7
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    When you're done you plan to have at least three tables, right? Customer, Tickets, Addresses
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  8. #8
    rhubarb is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    76
    I think two would suffice unless there is some functional reason to have job address in it's own table. I think customer and job address could be in the customer table. The tickets table will have job addresses preferably pulled from the customer table.

  9. #9
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    You're right if the Customer address is always the job address.
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  10. #10
    rhubarb is offline Advanced Beginner
    Windows XP Access 2010 32bit
    Join Date
    Sep 2011
    Posts
    76
    Yes, the two reference the same. It seems redundant to have a short text field for the job address in the newly created customer table when it also resides, although in some cases the same address appears multiple times, in the tickets table. At the time, it made sense to me to import the data to the job address field and have the tickets table select that data from the customer table using a lookup. Now I realize to just use the form to do that for the tickets table. But am I to leave it as two separate tables (customer and tickets) that contain the same data (job address)?

    I guess I could leave both tables job address field as short text and import the distinct job addresses to the customer table. Then use a lookup on the customer form for the job address. Yes, this works. I will just need to add a way to input new job addresses into the customer table. Easy enough.

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    The actual data should exist in only one table. The other table would contain a ForeignKey field to the data. Each of your tables should have a PrimaryKey field that should be an AutoNumber. Leave the address in both tables while you reorganize the tables. How many Customers are in the Customer table? How do you plan to synchronize them with their address?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

  12. #12
    sylviarodriguez is offline Novice
    Windows 7 32bit Access 2003
    Join Date
    Jun 2014
    Posts
    2
    I've had to change the underlying data type of a major table in my database. I export it to my new database but when I change the type from ...

    online pokies

  13. #13
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 7 64bit Access 2013
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,923
    I hate to be redundant but:
    Quote Originally Posted by RuralGuy View Post
    How many Customers are in the Customer table? How do you plan to synchronize them with their address?
    (RG for short) aka Allan Bunch Previous MS Access MVP - WinXP Pro, Win7 Pro Win10 Pro - acXP, ac07, ac10, ac13
    How to mark the thread as Solved!
    Teaching is not filling a bucket but lighting a fire.
    Borrowed quote..."Docendo discimus"

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 02-12-2014, 05:16 PM
  2. Replies: 1
    Last Post: 04-24-2013, 11:50 AM
  3. Replies: 7
    Last Post: 04-24-2012, 07:38 AM
  4. Replies: 3
    Last Post: 04-20-2012, 04:33 PM
  5. Changing field data into labels on the y-axis
    By slaterino in forum Access
    Replies: 1
    Last Post: 09-28-2010, 03:15 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums