Results 1 to 13 of 13
  1. #1
    NoSpatula is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    5

    What to do when there is no unique ID (Primary Key)?

    Been out of the Access game for a long time. I remember most of the 'stuff', but I'm stumped on Primary/Foreign keys.



    See the image below. I am getting data from two outside sources (you see one here - Classes) via MS Excel. There are no single eligible primary key fields. I created the Trainers table in hopes that I'd get IDs for them, but I won't be getting these. I need to create a one-to-many (trainers to classes) relationship. How would I go about doing this in this situation? If I auto-number the trainers table how do I create the one-to-many if the classes table only includes trainer first and last names. I need some combination of fields that would make up a primary key. First and last names could cause issues if I had trainers with the same name and no id field.

    Hope all this makes sense.

    Click image for larger version. 

Name:	AccessTables.png 
Views:	30 
Size:	5.8 KB 
ID:	32023

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    The combination of first and last name must always represent the same individual and spelling must be consistent in order to build a query with a compound join on the two common fields. Otherwise, you have just garbage and there is no solution.
    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.

  3. #3
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Add a TrainerID PK field to Trainers table - this could be autonumber or text e.g. trainer initials

    Add a ClassID PK field to the Classes table (unless the Class field is appropriate for this purpose)
    Add TrainerID as a FK field to the Classes table & populate it
    Then remove the duplicate Trainer First Name & Trainer Last Name fields from the Classes table

    Link the 2 tables using the new TrainerID field - should be 1 to many join

    Next create a Students table: StudentID (PK), LastName, FirstName
    Then replace the Student Name field with StudentID in Classes table and use this field to link both tables

    Suggest you remove spaces from all field names
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I would start with

    tblTrainers
    ------------------
    TrainerID_PK (autonumber)
    FirstName (Text) 'notice NO spaces in field/object names
    LastName(Text)

    tblClasses
    ------------------
    ClassID_PK (autonumber) (PK field)
    TrainerID_FK (Number - Long) 'foreign key to tblTrainers
    StudentID_FK (Number - Long) 'foreign key to tblStudents
    Class (Text??)
    ClassDate (Date/Time) ' "Date" is a reserved word and a built in function in Access
    **Other fields**

    Relationships:
    (1-to-many)
    tblTrainers.TrainerID_PK ---> tblClasses.TrainerID_FK
    tblStudents.StudentID_PK ---> tblClasses.StudentID_FK

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Great minds think alike ....
    Well spotted re Date field as well
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    NoSpatula is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    5
    Thanks for the suggestions. Will try and report back.

    Btw. The field names listed here are representations of the actual fields, not the actual names. But I get the point. =)))

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    There are no approximations/representations in a binary world. It is or it isn't.
    Makes it easier if the actual object are listed/named/referenced. (unless it is triple 'S' --> Super Secret Sh... uhhh ...'Stuff'!! - I don't want to be visited in the middle of the night, if you know what I mean! )

  8. #8
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    But OP says gets data for trainers without an ID. I had taken this to mean this occurs periodically, not a one-time occasion.
    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.

  9. #9
    NoSpatula is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    5
    June7,

    This is true, however, I may be able to influence a change in the originating data. Especially if management sees the Access reporting important enough to warrant the change. I will be getting the data monthly. This is our first run of the data. Call it a prototype of sorts.

  10. #10
    Join Date
    Apr 2017
    Posts
    1,673
    When original data are periodically read from Excel, then autonumeric ID's for trainers and students are probably out - unless you register trainers and students in Access DB, and those registers are read into Excel.

    In case you can't/don't want to move all data management into Access, you probably have to design a proper structure for Excel part too. Possible variations:

    1. Trainers and students registers are kept in Access [tblTrainers: TrainerID, TrainerForename, TrainerLastName, ...; tblStudents: StudentID, StudentForename, StudentLastName, StartYear, CurrentClass, ...). Both registers are read into Excel file(s) using ODBC queries, where additionally is calculated a FullName field in format like <LastName & " " ForeName & " (" & ID & ")". (ID is added for cases there are trainers/students with same fore- and last names). In Excel data entry table(s), users can select FullName (you use Data Validation List with dynamic named range based on FullName column of according query table). In data entry table must be a column(s), where TrainerID and StudentID are calculated. And when you'll keep Excel table(s) on predetermined network resource(s), you can link Excel table(s) into your Access DB. You can create a procedure in Access DB, which you can run at any time you want, and it updates your Access data, using query like
    Code:
    SELECT <FieldList> FROM LinkedExcelTable AS let LEFT JOIN YourAccessTable AS yat ON  yat.TrainerID = let.TrainerID AND yat.StudentID = let.StudentID AND yat.EntryDate = let.EntryDate WHERE yat.TrainerID Is Null
    2. Trainers and students registers are kept in Excel. In this case, it's best to have a single Excel file, or a master Excel file where trainer and student registers are maintained. When you have a master file, you must read both registers into user(s) Excel file(s) using again ODBC, or link formulas. Following will be same as p.1.

    It is best you link into access Excel files where real data entries are made - when you link some summary file to where data is read from data entry files, then before you read new data into Access DB, you have to open and requery this summary Excel file.

  11. #11
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    So why must Excel be involved? What is the 'outside source'?
    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.

  12. #12
    NoSpatula is offline Novice
    Windows 7 64bit Access 2013 64bit
    Join Date
    Jan 2018
    Posts
    5
    Circling back on this issue.

    Disaster averted. =)

    Convincing non-database people of the importance of a primary key is worth its weight in gold. We have decided (smartly) to get the data cleaned up before receiving it from outside sources. Here is the full story:

    Production Readiness Metrics (PRM)
    Training courses are conducted several times a week. All of the trainee metrics (approx. 12 data points) are collected via custom Excel spreadsheet, then dumped into a single excel file that contains a ton of formulas. I don't own this spreadsheet. I could not easily import this file since it is formatted over several sheets and rolled up into one summary, then 'prettied up'.

    This file had a few holes in it. Missing field data here and there. Humans entering data kinda stuff.

    Post Training Scorecard
    If you finish at the top of your class you hit the floor. Each month we collect performance data for all that do. These metrics are collected via vendor, then sent to us in an excel file. It contains a ton of stuff, but most importantly, it contains IDs that 'should' line up with the PRM data.

    Well...it didn't (initially). So we decided to clean up the PRM data by creating a version conducive to importing to Access, and to include all trainee IDs that match the scorecard. Seems like a no brainer, but we're talking about many people in the room trying to decide what they want when I'm trying to figure out how we're going to pull it all without a dedicated ID.

    Many thanks to all who took the time to read and comment.

  13. #13
    Join Date
    Apr 2017
    Posts
    1,673
    So separate trainers and students register from rest - as those are probably in future needed in other databases too, and they aren't updated frequently. You can start with them as seed for your Access database btw.

    Create an Access backend database with tables tblStudents and tblTrainers (both have an autonumeric ID), and a simple Access frontend application with 2 forms, where users can register/edit trainers and students lists. A couple of people (p.e. HR staff) will keep these registers (honestly I can't imagine p.e. trainers doing this - even in their Excel files).

    The list of students is read from Access DB into Excel workbook trainer are using, and based on it a Dynamic Named Range is defined. Based on Dynamic Named Range, a Data Validation Lists composed of students fore- and last names (and probably of ID too for case identical names) everywhere in workbook tables, where a student must be put into table. By default a Data Validation List doesn't allow to enter anything not defined by list - so using Data Validation List eliminates typos in those fields. And user can select student full name from dropdown list in addition of typing it into cell (a point you can use in your campaign). And additionally (to make it easier to read data into Access afterwards) you can calculate into table a column with student ID you read from Access DB, without trainers having to bother with it at all (when the ID is a problem for them anyway, you simply hide the column with it.

    As I understood from your last post, every trainer has his own Excel workbook. When this is the case, they don't need trainers list - you can simply write the trainer ID into trainer workbook - p.e. on hidden SetUp sheet - and add a calculated hidden column with it into table(s) there.

    You can consider also having other lists - p.e. Metrics List - in this Access database. You don't need even form for them on front-end - you simply enter the list into Access table, as they are determined at design time. Again, as end result, trainers will have a Data Validation Lists to select a value in table, and any possibility for typos is eliminated.

    My main advice is - allow users to enter without restrictions only data , which are impossible to predefine for him/her (i.e. in Excel - use Data Validation lists everywhere where it is possible). And don't overload the user with info - hide all sheets where the user can't change anything.

    And when all this is working, you can start with importing info from trainers tables (or from master excel table) into Access database, and designing a separate front-end for working with this data.

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

Similar Threads

  1. Replies: 19
    Last Post: 01-14-2016, 10:35 AM
  2. Replies: 3
    Last Post: 12-16-2015, 04:55 PM
  3. unique id on the primary key help
    By m1k3y1994 in forum Access
    Replies: 1
    Last Post: 03-08-2015, 07:40 AM
  4. Replies: 1
    Last Post: 08-19-2012, 09:55 PM
  5. Replies: 8
    Last Post: 03-16-2012, 01:07 PM

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