Results 1 to 5 of 5
  1. #1
    ChaseC is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    18

    In a datasheet form used for mass data dumps there's a column for FK...

    ... that references an autonumber PK in another table. I'd like to be able to just paste information in from Excel, however in Excel the information is stored as a client name. So, as an example:



    My excel spreadsheet looks like this:

    Hedge Fund Name Portfolio Name Portfolio Type Value
    Chase Fund Chase Fixed Income Buyout $1,000,000

    and my access data dump form has the following headings:

    fundManagerID_FK portfolioName portfolioType portfolioValue

    I have a 'fundManager' table that holds only fundManagerID_PK and the fundName.

    So: my dilemma is it does not allow me to easily paste my excel data in as it expects the auto-numbered PK. From looking at the properties of a combo box I see that the answer lies somewhere in changing the 'Row Source / Row Source Type' however it appears that text boxes do not have that option.

    I apologize in advance if this is a silly question. I am very very new to Access. Thanks!

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you can always import to a 'temp' table, that has an empty FK.
    use the primary lookup table with the PK, to backfill the temp table keys.
    THEN move the temp table data to the real table.

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Ooops, just realized going FROM Excel TO Access, not the reverse. Hope you didn't already read original comments.

    Is the Excel structure simple enough you can set a link to the Excel sheet? Are you confident the fund names will always be the same? Not "Janus Company" sometimes then "Janus Co" others? Why is Excel involved?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    ChaseC is offline Novice
    Windows 10 Access 2007
    Join Date
    Aug 2017
    Posts
    18
    Quote Originally Posted by June7 View Post
    Why is Excel involved?
    Excel will be involved only in the data migration. We're moving from an excel-based database system (with 10 or so different spreadsheets) to having all of our data in a centralized location (Access) that can be used to then generate the quarterly reports. I'm trying to make the process of pulling our data from Excel and putting it into Access as simple and painless as possible.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    This is a one-time process to get the data from Excel to Access? Why would properties of textboxes be a concern - are you trying to paste into a form? If this is a one-time event, why is a form even involved?

    HedgeFundName is same values as fundName, with no variations in spelling?

    Follow ranman's suggestion for using a temp table or do the same directly in the real table.

    Add a field in the Access table for the HedgeFundName. Import the data. Run an UPDATE query that joins tables on the HedgeFundName and fundName fields to populate the fundManagerID_FK field. Then you can delete the HedgeFundName field.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Mass Import from Excel to aggregate data
    By swift1 in forum Access
    Replies: 2
    Last Post: 06-16-2015, 04:47 PM
  2. adjust column width in datasheet form
    By Bospeentje in forum Forms
    Replies: 3
    Last Post: 09-15-2014, 10:13 AM
  3. Replies: 4
    Last Post: 12-18-2011, 05:55 PM
  4. Replies: 3
    Last Post: 07-29-2011, 09:30 AM
  5. Replies: 0
    Last Post: 12-25-2008, 10:05 AM

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