Results 1 to 11 of 11
  1. #1
    jucooper1 is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2016
    Posts
    18

    Relationships/Lookups Values and importing from excel

    I have a table with values that I want to relate to other tables, so I used a lookup value for the data type. However, all of this table gets imported from Excel and the fields with the lookup value get imported blank. Here is a sample (I am using it for high school football):

    Table 1


    ID
    Season
    Week
    Opponent

    Table 2
    TeamID
    Team

    I want to relate Team to Opponent, but if I use a lookup it doesn't work, and if I do it in the relationship table, it either tells me I have to use the TeamID, or I have to enter the TeamID in Excel, instead of the team name, which I really don't want to have to look it up every time I enter data. Any suggestions?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,338
    Access uses a query, joined to the lookup tbl.
    dont manually run a lookup.

  3. #3
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    11,365
    IMO your tables are likely not correct (aside from the fact that most developers won't use lookup fields in tables). Without much insight into the purpose of the db, perhaps should be more like

    tblSeason
    SeasonIDpk
    Season
    Week

    tblTeams
    TeamIDpk
    TeamName

    tblSeasonTeam
    ST_IDpk
    SeasonIDfk
    TeamIDfk
    MaybeOtherField

    If that looks quite odd, then perhaps you should review db normalization.
    Last edited by Micron; 08-02-2022 at 09:36 AM. Reason: corrections
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  4. #4
    jucooper1 is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2016
    Posts
    18
    Quote Originally Posted by Micron View Post
    IMO your tables are likely not correct (aside from the fact that most developers won't use lookup fields in tables). Without much insight into the purpose of the db, perhaps should be more like

    tblSeason
    SeasonIDpk
    Season
    Week

    tblTeams
    TeamIDpk
    TeamName

    tblSeasonTeam
    ST_IDpk
    SeasonIDfk
    TeamIDfk
    MaybeOtherField

    If that looks quite odd, then perhaps you should review db normalization.
    Thanks for the advice and the jab at the end. I'm still not sure that will letme import the data from excel into my table without having to look up an ID number to enter into excel

  5. #5
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    11,365
    There was no jab intended. As for the rest, I don't see how I can help if the root of the problem is your tables and fields.
    Perhaps someone else will be able to help you with what you have.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    jucooper1 is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2016
    Posts
    18
    The root of the problem is that I can't figure out how to import from excel with fields that have relationships. The import wizard imports those cells as blank. I've tried setting it up multiple ways and nothing seems to help.

  7. #7
    Gicu's Avatar
    Gicu is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    3,473
    Don't use lookup in the table. Use a temporary table (empty it everytime) to import the data from Excel (importing the team name) then build a query to append the final data in which you join the new temp table to your Table 2 (teams) table by team name and append the TeamId.

    Cheers,
    Vlad Cucinschi
    MS Access Developer
    http://forestbyte.com/

  8. #8
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,663
    @jucooper1,
    For me there is not enough info to make suggestions.

    Quote Originally Posted by jucooper1 View Post
    I have a table with values that I want to relate to other tables, so I used a lookup value for the data type. However, all of this table gets imported from Excel
    You have shown two tables - are there more tables?
    How does Excel fit in?
    With such simple tables, is Excel not handling the data? (Just wondering why you want to switch to Access).

    Which table do you want to import the Excel data into?
    Is this a one time import?
    You could use code to import the team name and get the teamID number.

    Are you using forms in Access? Instead of a Lookup FIELD, use a combo box on a form to select the Opponent Team by name and store the PK number.
    What does the Excel worksheet data look like?

    Maybe you would consider posting the Access dB and the Excel workbook?

  9. #9
    jucooper1 is offline Novice
    Windows 10 Office 365
    Join Date
    Dec 2016
    Posts
    18
    Ok, I was trying to simplify everything just wanting to know anyone had a work around for importing from excel with relationships. I did try importing to a new table and doing an append query and the values with relationships still were blank.

    Here is what I am trying to do. I coach high school football. We have a software program that we use to upload game film. We can "tag" information in this program, which can then be exported to Excel. This is what I did last year, but it ended up being too much data for excel to handle, as it ran really slow. Each play we tag in the software is a row in excel (or access). So these are the columns (conceptually, not my exact name) in my table:

    ID
    Season
    Week
    Hudl (the play number in the software)
    Team on Offense
    Team on Defense
    Quarter
    Score Difference
    Drive number
    Hash
    Yard Line
    Down
    Distance
    Play Type (run, pass. etc)
    Result
    Misc
    Position of ball carrier
    number of ball carrier
    Yards gained
    Formation
    Motion
    Motion Direction
    Play
    Variation
    Direction of play
    Strength of formation
    Play Strength
    Motion Strength
    Field
    Formation to field
    Front

    Some of these are calculated fields, plus I have several other fields that are calculated as well. I have a separate table for teams (that I would like to relate to offense and defense), formations, motions, plays and fronts. All of which I would like to relate to this table, but when I do that and import from excel they come in blank. The do this when I use a lookup field. When I just relate it to the other table in the relationship view, it only lets me relate the ID of whatever field I am working with, and so it only accepts a number value in that field, so that would mean that every formation I tag, I would have to look up its ID, same for every front, every play, etc, which is way to much work for 100 plus plays a game. I don't know how to normalize, since they all of the fields belong to the entity of that play.

  10. #10
    Join Date
    Apr 2017
    Posts
    1,428
    Based on your last table fields names, it looks like a row in this table contains one episode of one specific match/game. Is it so? When yes, then you need a table where all matches/games are defined, and some fields (like season, week, etc.) from your current table must be moved there.

    Clear out all lookup fields. Instead store ID's of lookup tables as foreign keys (like having MatchID field instead Season and Week fields - you can get those values always from matches table).

    Clear out all calculated fields - you can calculate those values always when there is a need for this. An exception for this is, when you need such calculated field to link subforms or to filter forms.

    Don't use tables to view or enter data! The best practice is to hide all tables from user(s) after the app is designed. All data entry/modifications must be done in forms or running VBA procedures. All data viewing/reporting must be done using forms or reports. Generally forms and reports will use ODBC queries to get proper data from tables. All calculations must be done in datasource queries of forms/reports. When designing app's data structure, the possibility of these calculations must be taken into account.

    Now about getting data from Excel.

    I think the best way is to have specific Excel tables stored (with certain name), where they are always available for your app. You link this Excel table/those Excel tables into your app through ODBC query, and those linked tables in your app are used solely to read data into other tables. Refreshing data in Excel table(s) is a separate solution - either data are entered directly, data are copy-pasted from other tables manually, data are read from other Excel tables using ODBC queries, etc.

    In your app, you ran a procedure or a set of procedures manually, or on schedule, which reads info from linked Excel file(s), and updates app's tables with it. When you use Access or SQL Server Express back-end, to automate data refreshing you have to use Windows schedule to run app's procedure(s). When you use (licensed) SQL Server back-end, you can define SQL Server scheduled works to run procedure(s).

  11. #11
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,663
    @jucooper1,

    Thanks, that helped.

    Full disclosure.... I know next to nothing about football. The high school and college I went to did not have football (did have basketball and hockey - I played neither).
    So a lot of the field names mean nothing to me (Formations, Motion, Play Strength, Formation to field, Fronts ????)
    I have watched "Rudy" and "Gridiron Gang" .


    So it looks like there might be at least 5 tables involved based on the fields you provided.
    Quote Originally Posted by jucooper1 View Post
    ....I don't know how to normalize, since they all of the fields belong to the entity of that play.
    Keep asking questions here and posting your dB for review/analysis.

    Maybe:
    a table for Games/Films
    a table for Teams
    a table for Play_Types
    a table for Formations (if there are standard formations)
    a table for Positions (Position of ball carrier)




    Quote Originally Posted by jucooper1 View Post
    I coach high school football. We have a software program that we use to upload game film.
    Is the dB only for film data or do you do the same analysis for actual games played?


    As noted by Micron and Arvil, you should not use Look-up FIELDS (different than Look-up TABLES). See The Ten Commandments of Access , The Evils of Lookup Fields in Tables and About calculated table fields


    Some suggestions:
    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not begin an object name with a number.
    Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.


    Be aware that "Result" and "Field" are reserved words in Access and shouldn't be used for object names. Plus they are not very descriptive..."Result" of what? "Field" means... whaaaaat?



    Quote Originally Posted by jucooper1 View Post
    .... so that would mean that every formation I tag, I would have to look up its ID, same for every front, every play, etc, which is way to much work for 100 plus plays a game.
    You could write a UDF (user defines function) in VBA to get the PK (look up its ID) automatically.


    Would you make a copy of an Excel workbook, deleting all rows except maybe 10 and post it?



    Quote Originally Posted by jucooper1 View Post
    I don't know how to normalize, since they all of the fields belong to the entity of that play.
    What is Normalization? (Parts I to V)
    What Is Normalization, Part I: Why Normalization?
    What Is Normalization, Part II: Break it up.
    What Is Normalization: Part III: Putting It Back Together
    What is Normalization: Part IV: More Relationships
    What Is Normalization: Part V: Many-to-Many Relationships

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

Similar Threads

  1. Lookups Vs. Relationships
    By Tuckejam in forum Access
    Replies: 3
    Last Post: 04-30-2020, 07:18 AM
  2. Replies: 4
    Last Post: 07-21-2017, 01:07 PM
  3. Need Guidance On 2 Table Designs, Relationships & Lookups
    By tdelios in forum Database Design
    Replies: 2
    Last Post: 05-31-2015, 04:27 PM
  4. Replies: 1
    Last Post: 06-02-2014, 07:15 AM
  5. Importing Excel file. (2 non numeric values won't import)
    By Amerigo in forum Import/Export Data
    Replies: 3
    Last Post: 05-20-2011, 11:31 AM

Tags for this Thread

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