Results 1 to 14 of 14

Attempting to bring new Excel data into Access WHILE merging updated data with prior primary keys

  1. #1
    ShawnColborn is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    7

    Attempting to bring new Excel data into Access WHILE merging updated data with prior primary keys

    Hello and thanks for stopping by,



    I am new to Access and have been tasked with updating a 30+ year database. The database has 10 tables, 9 of which provide various information and are linked "relationship-wise" to the 10th by a primary key given to each data entry into this database. My current task is to bring in an excel file that contains this years collected data and update all these tables with the newly sampled data. To explore if this is even possible, I grabbed a subset of the data to be entered and manually assigned them primary keys (if the sampled individual was "new" this year) or checked in the Access database for a previously assigned primary key (if the sampled individual was "re-sampled" this year). Once this was done, I attempted to import my Excel file into the appropriate Access table and was met with this error:

    The contents of fields in 0 record(s) were deleted, and 23 record(s) were lost due to key violations.
    *If data was deleted, the data you pasted or imported doesn't match the field data types of FieldSize property in the destination table.
    *If records were lost, either the records you pasted contain primary key values that already exist in the destination table, or they violated referential integrity rules for a relationship defined between tables.
    Do you want to proceed anyway?

    What I get from this is that the 23 records that were "re-sampled" this year in the Excel file (that were previously assigned a primary key) are not updating ("merging") as I hoped they would...

    So, is there a way to bring BOTH new data and old data from an Excel file into Access?

    Thank you very much for reading and I hope you have a wonderful day.

    Sincerely,

    Shawn

  2. #2
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    357
    Importing newly updated records into ones already existing does not cause automatic update. Nope, you have to put all those records in a Temp table and then run an UPDATE query where it finds a matching PK. Then you can tag those records as done and move on to APPEND the new ones.

    Having them in Temp table also affords you the opportunity to *clean* them up before pushing to live data and risking deletion, key violations, etc... I normally do it this opening a Form directly after import to *check* the data and fix an anomalies. The User then gets the option to send them all or only do the ones that are fine and leave the balance there until they can be repaired, usually by checking the source of the data.
    Gina Whipp
    Microsoft MVP (Access 2010-2015)
    https://www.access-diva.com/tips.html

  3. #3
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,477
    Welcome to the forum...


    Quote Originally Posted by ShawnColborn View Post
    The database has 10 tables, 9 of which provide various information and are linked "relationship-wise" to the 10th by a primary key given to each data entry into this database
    So, do each of the 10 tables have an Autonumber type primary key field? If not, what is the field type of the primary key field?


    Quote Originally Posted by ShawnColborn View Post
    is there a way to bring BOTH new data and old data from an Excel file into Access?
    Not sure what you mean by this. Do you want to keep the old data and add NEW records or update existing records and add new records or ?????



    Maybe you would post an image of the relationship window? Or post the dB with a sample of the Excel data?
    Is the Excel data going into 1 or more of the 9 tables or into just the 10th table??
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  4. #4
    ShawnColborn is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    7
    Thank you very much Gina, I shall give that a try and see if that process does what I am needing!

  5. #5
    ShawnColborn is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    7
    Quote Originally Posted by ssanfu View Post
    Welcome to the forum...



    So, do each of the 10 tables have an Autonumber type primary key field? If not, what is the field type of the primary key field?



    Not sure what you mean by this. Do you want to keep the old data and add NEW records or update existing records and add new records or ?????



    Maybe you would post an image of the relationship window? Or post the dB with a sample of the Excel data?
    Is the Excel data going into 1 or more of the 9 tables or into just the 10th table??


    Thank you!

    The "main" table that all the other tables seem to be related to (reference attached image) has an AutoNumber data type, while the rest have a Number data type.

    I would like to keep pre-existing data and do two things: 1) update any re-sampled records with the Excel data import (i.e., if Plant #1 had old data from 2018 but was sampled again in 2019 it would be updated), and 2) if a new plant was sampled in 2019 a new record would be created in and all the necessary attribute fields are updated in all of the 10 tables (if the data exists).

    The Excel data would ideally be uploaded and then parced out to the appropriate tables. They recorded all the data into this single Excel file, but the main table only takes some of the data, while the others take accompanying data. For example, all plants would have a TAGNUM (in the "main" table), but maybe not a DIAM (in periphery tables). This probably makes no sense...

    I super appreciate the insight!

    Best, Shawn

    Click image for larger version. 

Name:	Data_relations.jpg 
Views:	28 
Size:	126.8 KB 
ID:	40056
    Last edited by ShawnColborn; 10-28-2019 at 07:30 AM. Reason: Picture wouldn't upload

  6. #6
    GinaWhipp's Avatar
    GinaWhipp is offline Competent Performer
    Windows 7 64bit Access 2013 32bit
    Join Date
    Jul 2011
    Location
    Ohio, USA
    Posts
    357
    Hmm, well problem number one, and it's a biggie, you cannot relate Autonumber to Autonumber, one mistake in the main table and you lose a number, the same for the child tables. AND you cannot *set* and Autonumber it has a mind of it's own. While each table should have a Primary Key and Autonumber is the perfect choice you relate to child table via a Foreign Key data type = Long. You need to fix that first and foremost.

    Now, having tables with numbers listed at the end (or beginning) of a field indicates your tables are not normalized. Tables should be long not wide, i.e...

    tblNotes
    nID (You Autonumber Primary Key)
    nPlantID (Link to your tblPlants)
    nNote

    I would suggest getting started with fixing the above two issues (especially the first one) before moving on to anything else.
    Gina Whipp
    Microsoft MVP (Access 2010-2015)
    https://www.access-diva.com/tips.html

  7. #7
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,477
    As Gina said, you need to make some design changes.
    Having the same name for a field in 9 tables will get confusing when making queries and reports.

    I use a suffix of "_PK" and "_FK" for the primary key and foreign key fields. Table "Plants" would have a PK field name of "PlantID_PK"
    Table "Status" would have "StatusID_PK". To relate this table back to the "Plants" table, the FK field would be named "PlantID_FK".


    In table "Diam", what do the numbers mean?
    Note: the Access Gnomes can get irate when object names begin with a number.


    Could you make a copy of the dB, delete all but 12 - 20 records and post it here?
    Maybe explain what the 9 tables are for?


    Instead if having a form for each of the years, you should have 1 form and filter the form by year to show the data for a specific year.........
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  8. #8
    ShawnColborn is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    7
    Mmmm, well this is a bit concerning... thank you both for your information and insight, I had a feeling something was up with this database from the reading I had been doing and my familiarity with other programs. As it is not my database, I am not sure how much flexibility I have when it comes to making modifications. They would of course want to be sure no data was lost, and may not want any changes made (I can envision them saying "if it ain't broke don't fix it" for their data needs haha).

    @ssanfu: in the DIAM table the numbers (I assume you are referring to 88diam, 89diam, etc...) refer to year of that associated data. So the column "88diam" has all the recorded plants and (IF they have a diam recorded) the recorded root diameter of the plant for that year (i.e., a lot of these rows are probably blank). The following year (89diam) would reflect growing plants by a remeasured plant with an increasing diameter, for example.

    Essentially this database has forms for each sampling year that are updated after data collection, with the form populating these outlying tables. The 9 tables and their roles are as follows: Flowering (if the plant was flowering, a ton of associated data from that event); Leaves (a regularly recorded variable for a living plant, total count of leaves); NoRosettes (in theory this table would have records of the count data of rosettes, but it appears it is not recorded/updated); Stage (is the recorded individual for a specific sampling year a seedling, juvenile, or adult); Status (plant status - alive, dead, missing); Notes (general text box); RosetteDiam (diameter measurement data of rosettes, again doesn't appear to be updated/used); Diam (diameter data of root crown of each plant); DiamAvg (another field not regularly used/updated).

    The plant table lists all the recorded tags, their location in the plot (DIST/AZI), and relevant life events and year. Also gives the recorded plant a unique primary key.

    The typical data entry process involves going into the database and clicking the form you want to update for the relevant year. For example, if I was inputting data collected in 2015 I would click the 2015 Basic info form and populate the fields that there is recorded data for... (also, a side note: some of these fields are not populated usually, because of various reasons, but I have to enter a space into them or the record will not be searchable in the form at a later date... any clue why?).

    Also, I've attached a modified copy of the data containing a very small subset of the database (that was a good learning exercise, figuring out how to run a delete query on all of those tables). Hope this helps.
    Attached Files Attached Files

  9. #9
    ShawnColborn is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    7
    Again, thank you both so much for your help! Yall are wonderful!

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,477
    After looking at the dB, I have a few questions.

    1) Does "NoRosettes" mean "the number of" or "No" as in there aren't any?


    2) Why are 6 fields from table "Plants" copied (duplicated) into table "Leaves"??



    3) Table "Flowering" and table "RosettesDiam" do not follow the naming convention of having the first 2 characters of the field name a year. What is going on with these 2 tables??





    * These tables have no data: "RosettesDiam", "DiameterAvg" & "Notes".....easy to convert.

    * Tables "Diam", "DiameterAvg", "Notes", "NoRosettes", "Stage" & "Status" should also be easy to convert. Writing some code to transfer data (or attempting to ).
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  11. #11
    ShawnColborn is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    7
    1) Should be number of rosettes

    2) I honestly have no clue (did not build this database), I would think this information could be redundant, but there may be a reason it is constructed the way it is...?

    3) The plants only flower once and then die, so yearly data is not possible for these guys... instead, when a plant is found to be flowering the research team records a variety of information: the Tag# of the flowering plant (which is tied back to an AccessID), total flowers (TotInfls), total bips (immature flowers; TotBippies), and then a diameter of the stem (if multiple stems, multiple diameter measurements). This information is entered into the year form for the particular data year being entered (i.e., if I am entering data collected from 2019 and there is an adult I would update all of these fields in that form... these fields are left alone in the form when the individual is not an adult - besides having to put a space apparently to make the record searchable). The Flowering table is definitely used, but I am not sure about the Rosette tables... there is probably data for these tables either in the rest of the data I took from this site (i.e., not all records in each table will necessarily have a value, but it doesn't mean the table is negligible), or in the other Access databases they have per site (i.e., there are multiple Access database files, each belonging to a specific site - I don't know why there is not a single database with site specific identifiers associated with each sampled individual, able to be Queried by site).

    You're amazing, thank you for tinkering with the conversions! I'd love to see what you come up with for the solution

  12. #12
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,477
    OK, here is my attempt at normalizing your dB.

    NOTES:
    - I wrote code to convert the data from the old design to the new design, so look at the relationship window FIRST. There was no data in several tables or there were fields with no data, so there is no code to move the data. Case in point is the table "Flowering". There are repeating fields for "Stmdiam" and "Infls/Bipps", but there was no data so I did not write code to update the new tables.

    - This is NOT finished (by a long shot)!! But it is a start.....
    - The new tables are prefixed with "tbl".
    - I use queries for the record source for tables. It is easier to add/remove fields and you can easily change sort orders.
    - I made the year a combo box, but haven't/didn't finish all of the sub forms. (new project pulled me away).
    - In "frmMain", the data from tables "Plants" is in the details section. All other table data is in a tabbed control with sub forms for their data.

    - I use the prefixes "frm" for forms, "sfrm" for sub forms, "qry" for queries and "rpt" for reports.


    - "Form1" - this is a form with 2 buttons on it. One button DELETES all data in the tables with the "tbl" prefix. (helps when debugging the VBA code.
    - The other button manipulates the data converting from Excel layout (short and wide) to Access design (tall and narrow) tables.



    Let me know what you think.....
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

  13. #13
    ShawnColborn is offline Novice
    Windows 10 Access 2016
    Join Date
    Oct 2019
    Posts
    7
    Good morning!

    I hope everyone had a great weekend

    I went to take a look at the changes you've made by looking at the attached document but it appears that it is a different database (in a different language)?

    Thank you so so so so so very much for the help!!!

    Cheers!

  14. #14
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    8,477
    Wow! Major PBKAC! Sorry.....


    Try this
    Attached Files Attached Files
    HTH
    -----
    Steve
    --------------------------------
    "Veni, Vidi, Velcro"
    (I came; I saw; I stuck around.)

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

Similar Threads

  1. Merging Data from Access to a fillable PDF
    By jeffhanner10@gmail.com in forum Access
    Replies: 1
    Last Post: 09-18-2019, 06:06 PM
  2. Mail Merge only merging the Primary keys
    By myusersname in forum Import/Export Data
    Replies: 14
    Last Post: 02-20-2019, 07:55 AM
  3. Replies: 3
    Last Post: 06-14-2016, 11:59 AM
  4. Replies: 3
    Last Post: 07-05-2012, 06:11 AM
  5. Need Frequent Excel Imports with Primary Keys
    By UMassEngineer in forum Import/Export Data
    Replies: 1
    Last Post: 03-27-2011, 10:07 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
  •  
Tech Forums: Microsoft Office Forums