Results 1 to 5 of 5
  1. #1
    Chris@harveynorman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2016
    Posts
    7

    Import excel database then change field type

    I am not sure I am doing this the best way but here is what I am trying to do:

    I have created a table for which I record details about deliveries made. Such details include the date; store; invoice number; product details and a few other fields. Over the years I have created a few new table which run for a few years. Now I have 5 different tables and I want to combine them all together. One problem is that for the fields STORE and DETAILS, the data comes from a list or combo box that is linked to another table which has all the store names for the STORES field and another table for the DETAILS field. The tables for each are different.



    So I have copied and pasted all 5 tables of the delivery details into one excel spreadsheet and done the same for the STORES and DETAILS tables and changed all the entries that I wanted to so that they are all called the same thing matching the entries that exist in the new STORES and DETAILS tables. So I now have one main table in excel called All Deliveries and another called All Stores and another called All Details.

    I have now imported these into access and linked the fields for STORES and DETAILS to the relevant tables via a list box.

    So I am happy with how this turned out up to here. Now in order to use the selection tab to choose (or filter) all A store or all B store entries or something from the Details field, it requires me to change the field type from text to number in order to do this. If I change the type to number, I loose all the existing data for those fields.

    How can I fix this?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    IF the details table is a 1 to 1 record about the store, then combine the 2 tables into 1. Build a query, bring in the 2 tables, join on the common field (store). Run a Make table query to combine them.

    if the details is NOT a description of the store, then you do NOT want them combined.

  3. #3
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    It sounds like you are storing the text value of the store name in your details table, rather than the primary key value of your stores table

    i.e.
    Code:
    tblStores
    S_ID  S_Name
    1     Store A
    2     Store B
    3     Store C
    
    tblDetails
    D_ID  S_Name ----> other details related fields
    1     Store A
    2     Store A
    3     Store B
    when what you actually want is something like:

    Code:
    tblStores
    S_ID  S_Name
    1     Store A
    2     Store B
    3     Store C
    
    tblDetails
    D_ID  S_ID ----> other details related fields
    1     1
    2     1
    3     2
    You want to sore the primary key field in your details table, NOT the text value of the store. Assuming you are using an autonumber as your primary key you can fix your data by creating a numeric field on your tblDetails table and updating it with a looked up primary key value from your stores table, then your lookup should function properly on the numeric (primary key) field without issue.

  4. #4
    Chris@harveynorman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2016
    Posts
    7
    Quote Originally Posted by rpeare View Post
    It sounds like you are storing the text value of the store name in your details table, rather than the primary key value of your stores table

    i.e.
    Code:
    tblStores
    S_ID  S_Name
    1     Store A
    2     Store B
    3     Store C
    
    tblDetails
    D_ID  S_Name ----> other details related fields
    1     Store A
    2     Store A
    3     Store B
    when what you actually want is something like:

    Code:
    tblStores
    S_ID  S_Name
    1     Store A
    2     Store B
    3     Store C
    
    tblDetails
    D_ID  S_ID ----> other details related fields
    1     1
    2     1
    3     2
    You want to sore the primary key field in your details table, NOT the text value of the store. Assuming you are using an autonumber as your primary key you can fix your data by creating a numeric field on your tblDetails table and updating it with a looked up primary key value from your stores table, then your lookup should function properly on the numeric (primary key) field without issue.

    Hi,

    I understand what you mean by using the primary key as the the value being stored which is a numeric value not text. But I am not sure how to make that happen. I am not sure how to use codes as you have put it. The way I have developed my current database is going to the design view of the table and changing data to a lookup value from another table. I then started entering records from scratch. I am having trouble now converting the existing data that I have consolidated into excel spreadsheets. I have attached the three excel spreadsheets so you can see where I am coming from. I had to make the main database (All deliveries Samples) as only a sample as the file was too large. I have about 42,000 records.

    I want the Date, Store and Details data to be "required". Also the data for the Store column to be selected from Store table (via lookup drop down menu - combo or list box). The same goes for the Details column coming from the Details table. The Send To column also uses the Store table but data is not required.
    Attached Files Attached Files

  5. #5
    Chris@harveynorman is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Oct 2016
    Posts
    7
    I am finding that I can only import the excel table if the fields are all text type data. If I change a field (eg STORE) to number it has a type conversion failure. But then to use the field in the access database and convert it to a lookup field (to ensure the data matches that in the STORE table and can be selected from the drop down list or combo box) it needs to be a number field. When I convert the field to number type I then loose all the data for that field I just imported.

    What should I do to remedy this? Is there another way to design my main table to use the info from the other tables other than lookup?

    I have attached what the three tables look like in access
    Attached Files Attached Files

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

Similar Threads

  1. Replies: 5
    Last Post: 10-27-2015, 06:00 AM
  2. Replies: 2
    Last Post: 04-29-2014, 05:03 PM
  3. Excel Import Errors-Type Conversion Failure
    By ejm2163 in forum Import/Export Data
    Replies: 5
    Last Post: 04-28-2013, 09:17 PM
  4. Replies: 5
    Last Post: 01-09-2012, 05:55 PM
  5. Import to Excel from Access column of LOOKUP data type
    By Derek in forum Import/Export Data
    Replies: 1
    Last Post: 12-06-2010, 06:27 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