Results 1 to 3 of 3
  1. #1
    falberta is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2019

    Table design for Data imported from excel

    Hi everyone-

    I am new to the forum and used to do a bit of database design a long time ago. I am a bit rusty and I'm having a problem with designing tables from imported excel data. My data is a dump of patient information with procedure and diagnosis codes. Each patient has a unique patient ID and may have multiple entries in the spreadsheet for individual encounter dates. The problem is that each one of these encounters may have multiple procedures and those are entered on individual rows as well.

    Click image for larger version. 

Name:	excel pic.jpg 
Views:	21 
Size:	69.6 KB 
ID:	40106
    So the patient number is the unique identifier for each patient (as different patients could have the same name) and I want to group the data by encounter and by patient. The only thing that changes in those rows for the same encounter is the procedure code fields. I need to be able to search the database by procedure code but I would need to see the other procedures also performed on the same date of service. Ideally, the data would have a single record for each encounter with one or more procedure codes in each record.
    Any help in organizing this data would be greatly appreciated.


  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Ontario, Canada
    you ought to have a table of patients and store the patient id in tblPatientProc (patient procedures) as a minimum. Maybe a table for procedure options, depending on your needs (provides a list of procedures to be selected, eliminating keying errors). Not sure where Diagnoses fits in but probably should be its own table as well in the same way as procedure options. Then you'd need junction tables to relate patients to procedures and one for diagnoses. I suppose more info about how these relate to one another would be required to be more specific and accurate. The main take away, I think, is that your current table design resembles a spreadsheet (wide format where columns are additional data points) whereas a db is "tall" (tables hold additional data points as records; AKA "rows" but not really). I'd start with examining why any of this would be dependent upon Excel as a data source going forward. This is a job for a db and Excel should be eliminated as the data source.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  3. #3
    mike60smart is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    May 2018
    Living in Scotland UK

    Is the dump of data from Excel going to be a regular occurrence or is this just a once off dump of data?

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

Similar Threads

  1. Can my mega excel table be imported
    By rayted in forum Import/Export Data
    Replies: 3
    Last Post: 02-05-2018, 04:31 AM
  2. joining data imported from excel
    By nshatz in forum Queries
    Replies: 5
    Last Post: 06-15-2015, 03:33 PM
  3. Replies: 4
    Last Post: 03-19-2015, 06:14 PM
  4. How To Get Alert Not All DATA were imported From Excel
    By OneToLearn in forum Import/Export Data
    Replies: 1
    Last Post: 11-16-2013, 08:52 AM
  5. Linking Access Table with already imported External Data (Excel)
    By izzarshah in forum Import/Export Data
    Replies: 1
    Last Post: 07-29-2010, 09:40 AM

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 - Senior Forums