Relationships/Lookups Values and importing from excel

[jucooper1]

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?

[ranman256]

Access uses a query, joined to the lookup tbl.
dont manually run a lookup.

[Micron]

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.

[jucooper1]

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

[Micron]

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.

[jucooper1]

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.

[Gicu]

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,

[ssanfu]

@jucooper1,
For me there is not enough info to make suggestions.

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?

[jucooper1]

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.

[ArviLaanemets]

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).

[ssanfu]

@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.

....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)

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?

.... 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?

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