Results 1 to 12 of 12
  1. #1
    broscup is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    4

    Multiple Tables or One? with subquestions

    Good day all.
    It's been about 18 years since I have seriously worked in Access, so thanks in advance.

    I am the Training Chief for a federal fire department and I am creating a database to better track our firefighters certifications.
    I have a table with our personnel in it that lists out their employee IDs (ESAMS ID) and I use that as the primary key across several tables. Those are CPR certification, EMTcertification (of which there are two types State and National), and their emergency vehicle driving certifications.


    All have differing expiration dates and time periods.
    Is splitting these tables the best practice? I felt it was but…
    I wanted to create a form to enter new employees that wouldallow me to enter data across as many of the tables as possible at onetime. The problem I ran into was that Iwould have to type the ESAMS ID up to five times.
    What I would like is to type the ESAMS ID one time and haveit create a record in all five tables. Is this possible?
    I have attached a screenshot of the relationships of thetables in question.
    Thanks Again,
    Greg
    Attached Thumbnails Attached Thumbnails Relationships.PNG  

  2. #2
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    is splitting these tables the best practice?
    yes.

    What I would like is to type the ESAMS ID one time and have it create a record in all five tables. Is this possible?
    you add data to tables using an APPEND query. each APPEND query can only add data to one table at a time, but you can easily have several different queries run in sequence by including them in a macro. there are more elegant solutions involving writing VBA code but a macro will work and get you started.

    however, it is not necessary to have the ESAMS ID added to all five tables.

    have the ESAMS ID as the primary key in tbl Personnel list.

    in the other tables, have an auto number ID as the primary key and include ESAMS ID as a secondary key. then link the tables on their ESAMS ID and you only have to enter the data once but everything is connected and linked correctly.



    many thanks,


    Cottonshirt

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    In principle it looks OK with one significant change - but subject to you know your business, we don't!

    Main issue is you are joining PK to PK (PK=primary key) which means you can only ever have one record in the other tables per your personnel list. That might be OK but using your CPR table as an example - what happens when the person recertifies? you simply update the record? or do you need to maintain a history? If the former, then the CPR table might as well be part the personnel table. If the latter, then you need a FK (family or foreign key) field in the CPR table and link on this to the PK field in your personnel table.

    Other comments

    avoid spaces in table and field names - they will come back to bite you at some point

    avoid non alpha numeric characters in table and field names - (Airfield) in this case. Can't find a specific link but this is close enough https://docs.actian.com/ingres/11.0/...htm%23ww108918

    avoid using reserved words - Description in this case https://docs.microsoft.com/en-us/off...reserved-words

    perhaps you need a table for instructors to store names and expiration dates?

  4. #4
    broscup is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    4
    Thank You. I will look into Append Queries. I have been refreshing myself by going through the 2016 Essential Training through Lynda.com. Hadn't gotten to Append Queries yet.

  5. #5
    broscup is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    4
    I already planned on fixing the field and table names as suggested. When I came upon this problem a few weeks back I stopped working on it to start going through the Lynda/YouTube training. That's when I was reminded of better naming convention practices.
    I don't think I need to have a sperate CPR Instructor table because we only have a couple. This field is just a Yes/No box and I figure I could run a query on the yes's if necessary.

    As far as tracking. We really don't need to track past certifications, It's more about making sure that they are current. We can use the state and national EMT sites to get more in depth data if we need it. This is more for use in-house to quickly see what our people have. I think it might be easier to have all of this in one table I guess. I was just concerned with having a table with too many fields. Also, the EMT tables were pulled in from reports we are able to pull from the state and national website, so I figured if I ever needed to do a mass data entry, I could just drop it in to one table without messing others up. Just trying to see what others who use Access much more than I have in recent years would do.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    I was just concerned with having a table with too many fields.
    the limit is 255 fields. The table design should be around relationships, either one to many or many to many. You might want to split data with a one to one relationship because of confidentiality reasons (not relevant here, but you might want to keep salary data in a separate table for example) but I would still recommend treat as a foreign key. If your EsamsID is an autonumber in each table you will struggle to keep them in sync

  7. #7
    broscup is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2020
    Posts
    4
    Quote Originally Posted by Ajax View Post
    the limit is 255 fields. The table design should be around relationships, either one to many or many to many. You might want to split data with a one to one relationship because of confidentiality reasons (not relevant here, but you might want to keep salary data in a separate table for example) but I would still recommend treat as a foreign key. If your EsamsID is an autonumber in each table you will struggle to keep them in sync
    By too many fields I meant more convoluted than breaking the database haha.

    The ESAMS ID is a unique employee ID. Not auto generated. Just figured it made a good key because there's no privacy information to worry about with it but it's unique and everyone has one.

    Is there a problem using this as the primary key in multiple tables?

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    Is there a problem using this as the primary key in multiple tables?
    only if you type it in wrong and need to correct it. It is also giving the primary key meaning whereas a primary key's sole purpose is to uniquely identify the record. So it is up to you.

    Other factors are to do with performance. text takes up more space than a number so indexes will be slower and your db will be bigger. Simplistically a number takes 4 bytes, text takes 2 bytes per character plus 2 bytes. So a 6 char ID will take 14 bytes - 3.5 times the size. So you have your table - 14 bytes for the record, plus 14 for your index = 28 bytes v 8 bytes for a numeric key and index plus 14 for the char ID field =22 bytes. Not so bad but it does get worse. Say you have a child table with 10 associated records - (plus index) so that is another 280 bytes for a text PK or v 80 for a numeric key.

    The above relates to good practice - for databases with small amounts of data, not really a problem, larger databases more so.

  9. #9
    Cottonshirt is offline Competent Performer
    Windows 8 Access 2007
    Join Date
    Feb 2018
    Posts
    116
    Also, the EMT tables were pulled in from reports we are able to pull from the state and national website, so I figured if I ever needed to do a mass data entry, I could just drop it in to one table without messing others up. Just trying to see what others who use Access much more than I have in recent years would do.
    the method I use with mass data entry, around 2000 records at a time, is as follows:

    1. paste it into notebook (or some other basic text editor) to strip out any non-text characters like flags and such which get converted into text.
    2. paste it into Excel and check that it is properly aligned in columns and rows.
    3. check date and number formats.
    4. import into the database in an import table, which I think of as a holding pen where the data can sit while I play with it.
    5. check each field to make sure the entries agree with your current DB, make sure that names of things are spelled correctly and consistently, make sure that dates make sense in their context (a guy can't certify as CPR trained when he is 12 years old) and so forth. each extra check you make will save you a ton of time later because once an error gets in to your DB it will get lost in the mass of data. check every thing you can possibly think of. then sleep on it and come back and check all the things you didn't think of first time around.
    6. take a copy of your database. on the File menu, click Save As and save a copy of your DB in an archive folder. include the date in the file name in the format yyyymmdd. that way your backups will be automatically indexed.
    7. run a bunch of APPEND queries to add new data from the import table to your database, or run UPDATE queries to amend existing records.
    8. check your appends and updates have worked correctly.
    9. take a copy of your DB.
    10. delete the records in the import table. a DELETE query can do this.


    many thanks,


    Cottonshirt

  10. #10
    Summer is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Posts
    5
    duplicate entry
    Last edited by Summer; 07-18-2020 at 08:55 AM. Reason: duplicate

  11. #11
    Summer is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2020
    Posts
    5

    Question Working with mutlitple tables

    Hello. I think my questions are related to this thread--apologies if not. I am using Access to manage many details about my customers--too many to fit into one table. I've divided the data into 8 tables. I think I understand from the previous comments, but I want to confirm:


    • I've used the customer file number as the primary key in all 8 tables, changing the name for each table, but the number is identical (customerID, financialID, marketing ID...)
    • It sounds like I should use an auto ID as the primary key for tables 2-7, and the customerID should be connected between all 8 tables as a one-to-one relationship--is that right?
    • My goal is to add a customer record (in the Customer table) that also adds a corresponding record in each of the other 7 tables (without having to manually add the record to each table)
    • A secondary concern is that I'd like to move the customer records to another database when they are complete. I've set up 8 append queries to do this for each record. However, I'd like to:
      • use one append query to copy all 8 records from database A to database B at one time
      • delete the record from all 8 tables in database A at one time


    Hopefully my database design is salvagable--I've spent many hours on it.  Any advice to make it work more efficiently will be greatly appreciated!

    --Summer
    Last edited by Summer; 07-18-2020 at 08:53 AM. Reason: formatting

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    better to start your own thread - reference this one if necessary.

    really can't answer your questions with knowing more about your business and what each table stores

    At the moment your description sounds suspiciously like you are trying to apply excel principles to data management - which is about as far away from database management as you can get.

    Recommend you learn and understand normalisation then provide more detailed information about what you are trying to do

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

Similar Threads

  1. Replies: 6
    Last Post: 08-31-2019, 09:15 AM
  2. Replies: 4
    Last Post: 09-13-2017, 06:19 AM
  3. Replies: 2
    Last Post: 03-30-2015, 12:38 PM
  4. Replies: 1
    Last Post: 08-25-2012, 06:11 PM
  5. Replies: 10
    Last Post: 07-12-2011, 11:09 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