Results 1 to 9 of 9
  1. #1
    ali1979 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    4

    Import data from excel, with repeating records

    Hello,
    I have an excel sheet about operated patients.

    Columns include: patientID, firstname, middlename, surname, address, surgerydate, ... (10 more columns related to surgerydate), surgerycode, surgeryname
    (PatientID is actually unique IDCard numbers of citizens, however there are foreign patient records who are missing a unique PatientID)
    1. Patient may be operated once, with one surgery code
    2. Patient may be operated once, with more than one surgery code


    3. Patient may be operated more than once, with one surgery code
    4. Patient may be operated more than once, with more than one surgery code

    Because of these possibilities, i created 3 tables:
    1- PatientDemographicsTable: DemographicsID (primary key), PatientID, firstname, middlename, surname, address
    2- SurgeryDateTable: SurgeryDateTableID (primary key), DemographicsID, PatientID, SurgeryDate, ... (10 more columns) --> Linked to table above with DemographicsID
    3- SurgeryCodeTable: SurgeryCodeTableID (primary key), SurgeryDateTableID, PatientID, SurgeryCode, SurgeryName --> Linked to SurgeryDateTable with SurgeryDateTableID

    I tried to import data into access, however i failed because of repeating records.
    Do you know any way to do it?

    Since i failed to do it, i created excel tables with the same access table fields, then i imported, now all data is in access in tables described above. However, as you can guess, i cannot copy the primary keys of parents to the childs.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    create a linked table(s) to your spreadsheet(s)

    then use an append query(s), importing parent first and joining the linked table to the parent table on a name, whatever, to 'lookup' the ID

    Not sure what you mean by repeating records - do you mean you have repeating records in excel, or you don't want to import if already imported?

    If the former, use SELECT DISTINCT in your append query to eliminate duplicates

    If the latter, join the linked table to your access table by a uniqueID (which may be several fields) using a LEFT JOIN and import where the access table uniqueID is null

  3. #3
    ali1979 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    4

    Sample Data in Image

    Quote Originally Posted by Ajax View Post
    create a linked table(s) to your spreadsheet(s)

    then use an append query(s), importing parent first and joining the linked table to the parent table on a name, whatever, to 'lookup' the ID

    Not sure what you mean by repeating records - do you mean you have repeating records in excel, or you don't want to import if already imported?

    If the former, use SELECT DISTINCT in your append query to eliminate duplicates

    If the latter, join the linked table to your access table by a uniqueID (which may be several fields) using a LEFT JOIN and import where the access table uniqueID is null
    Click image for larger version. 

Name:	2017-02-13.png 
Views:	17 
Size:	95.7 KB 
ID:	27485

    As you see, the yellow highlighted area is the same patient, which should be recorded as single record in patientdemographics table,
    Reds, pinks, and browns area should be included in surgerydate table as 3 different records (3 different dates of surgery)
    green, greenish, and dark blue area should be all recorded in surgerycode table as 6 different records (green should be child of red, dark blue should be child of brown, and red, pink and brown should be all children of yellow)

    Sample Database: Patient.zip
    Last edited by ali1979; 02-13-2017 at 03:37 PM. Reason: Adding accdb file

  4. #4
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    As advised, use SELECT DISTINCT

    Just looking at the highlighted rows and assuming you have linked to the excel table with a table names 'ExcelLink'

    for patients (assuming it has just the 6 columns with the column headings per your excel)

    INSERT INTO tblPatients
    SELECT DISTINCT PatientID, Firstname, middlename, surname, birthdate, address
    FROM ExcelLink

    for surgery

    INSERT INTO tblSurgery
    SELECT DISTINCT PatientID, Surgerydate, surgerystart,......
    FROM ExcelLink

    for surgery code table - not clear what this links to, but assuming it is tblSurgery, when a new record is inserted it should have a SurgeryID autonumber field. You also need to be able to identify this from the existing surgery data. For the purposes of this example, I'm assuming it is surgerydate. It may be the DISTINCT is not required, but left it in just in case

    INSERT INTO tblSurgeryCodes
    SELECT DISTINCT tblSurgery.SurgeryID, SurgeryCode, SurgeryName
    FROM ExcelLink INNER JOIN tblSurgery ON ExcelLink.SurgeryStart=tblSurgery.SurgeryStart

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    IMO, I think you should rethink your table structures/relationships.
    I've been looking at the table "Excelsheet" and have come up with this structure

    Click image for larger version. 

Name:	Patient1.jpg 
Views:	17 
Size:	40.5 KB 
ID:	27497

    I don't know where to include these fields because I don't understand what they are for:
    SurgeryGroup - A3-Grubu
    Side - (no data)
    Wound - (no data)
    ASAScore - (no data)
    Anesthesia - (no data)
    Time - 90 dk ' "Time" is a reserved word and shouldn't be used as an object name.
    Department - First


    My $.02........

  6. #6
    ali1979 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    4
    Quote Originally Posted by ssanfu View Post
    IMO, I think you should rethink your table structures/relationships.
    I've been looking at the table "Excelsheet" and have come up with this structure

    Click image for larger version. 

Name:	Patient1.jpg 
Views:	17 
Size:	40.5 KB 
ID:	27497

    I don't know where to include these fields because I don't understand what they are for:
    SurgeryGroup - A3-Grubu
    Side - (no data)
    Wound - (no data)
    ASAScore - (no data)
    Anesthesia - (no data)
    Time - 90 dk ' "Time" is a reserved word and shouldn't be used as an object name.
    Department - First


    My $.02........
    Thank you for the detailed table link structure. However, i do not need such complex structure. The table link structure i need is in image below.

    Because;
    SurgeryGroup - A3-Grubu Side - (there is some data) Wound - (there is some data) ASAScore - (there is some data) Anesthesia - (there is some data) Time - 90 dk ' (It can be SurgeryTime) Department - First are all related to the specific surgery record additional infos, which will also be included into SurgeryDate table. I just did not include those for the database to create a quick sample db.


    Click image for larger version. 

Name:	2017-02-14.png 
Views:	13 
Size:	11.1 KB 
ID:	27505

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    OK, but your design is flawed. Based on your example data, you will have problems entering your data.

    For instance, the table "SurgeryCode" looks like it holds "SurgeryCode" and "SurgeryName" as a look up table. But you have SurgeryDateID (PK) linked to "SurgeryCode.SurgeryDateID (FK)" which is backwards. "SurgeryCode.SurgeryCodeID (PK)" should link to "SurgeryDate.SurgeryCodeID (FK)".

    Looking at the table "Excelsheet", there is a patient that has 2 doctors. A patient (for one surgery) can have many doctors and one doctor can have many patients. Therefore, this is a many-to-many relationship - you need a junction table.
    Same with surgeries. One surgery (procedure) can have many surgery codes and one surgery code can be for many surgeries (procedures). Many-to-many;need a junction table.



    Good luck with your project...

  8. #8
    ali1979 is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2017
    Posts
    4

    Thumbs up

    Quote Originally Posted by ssanfu View Post
    OK, but your design is flawed. Based on your example data, you will have problems entering your data.

    For instance, the table "SurgeryCode" looks like it holds "SurgeryCode" and "SurgeryName" as a look up table. But you have SurgeryDateID (PK) linked to "SurgeryCode.SurgeryDateID (FK)" which is backwards. "SurgeryCode.SurgeryCodeID (PK)" should link to "SurgeryDate.SurgeryCodeID (FK)".

    Looking at the table "Excelsheet", there is a patient that has 2 doctors. A patient (for one surgery) can have many doctors and one doctor can have many patients. Therefore, this is a many-to-many relationship - you need a junction table.
    Same with surgeries. One surgery (procedure) can have many surgery codes and one surgery code can be for many surgeries (procedures). Many-to-many;need a junction table.



    Good luck with your project...
    Oopppsss, i missed that doctors possibility! Thank you. I may move doctors field into surgerycode table. It's still simpler, wouldn't it be?

  9. #9
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The same possibility exists with the surgery codes also. See the junction tables.

    Maybe I misunderstand what the table "SurgeryCode" is for.





    --------------------
    PS - it is not really a good idea to have multiple objects with the same name....... ie Table "SurgeryCode" and a field in that table of "SurgeryCode".

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

Similar Threads

  1. Replies: 2
    Last Post: 06-25-2015, 03:56 PM
  2. Replies: 6
    Last Post: 06-09-2015, 01:27 AM
  3. Import access query data into excel file, using excel vba
    By exceljockey in forum Import/Export Data
    Replies: 3
    Last Post: 11-15-2013, 02:36 PM
  4. import excel sheet causes autonumber beyond records
    By BCJourney in forum Import/Export Data
    Replies: 11
    Last Post: 09-25-2013, 06:02 AM
  5. Data in subform records repeating
    By skatiekat in forum Forms
    Replies: 2
    Last Post: 11-01-2012, 12:50 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