Results 1 to 5 of 5
  1. #1
    Grafixx01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    13

    Query for Data move into multiple tables?

    So I'm going to ask a question after doing a little research on here and on the net. If I have my table, tblImportCSV, which has a large amount of data in rows in it imported from a CSV file and now need to pull or push that data into the respective tables in the DB I'm making, I'll need possibly an append/update query then following all the 'imports', a final 'delete tblImportCSV contents' statement.

    So what I have is the tblImportCSV, which has all the combined fields in the below image, aside from the tblState, tblDEASchedule, tblDrugCategory as they are constant so they need no updating/appending. The thing that I'm having difficulty with is that all the fields in the original CSV (tblImportCSV) are in rows with like a new patient for each row.

    What I'm trying to figure out is how to query the tblImportCSV row-by-row, to then populate the actual database tables with the correct information and not lose that integrity from the original CSV? Like ALL of the first row data needs to match the same Patient across all the different tables.

    After I'm done with this, I'm looking at then doing more imports, on a monthly basis that will do the same but also have to end up with an 'update' if the same patient (based off of first name, last name, and DOB are equal), the import would append the data for that patient across the tables, if it was different, it would add all the data as a new record.

    IF, and that's a HUGE IF, I have made sense here, can someone please help me?



    Thank you in advance,

    G

    Click image for larger version. 

Name:	Relationships.jpg 
Views:	19 
Size:	85.8 KB 
ID:	45020

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    A bit confusing, but based on my interpretation you'll need an append query for each "set" of csv fields that need to go into the final tables. So 1 query to get records into tblPharmacy and another for tblDispenser. However, you must include the fk from the csv table each time you append to a table that's related to some other append you did; e.g. PharmacyID goes in to tblPharmacy (from csv table) but also PharmacyID in to tblDispenser. If you must create a partial record in a table and other fields will come from other portions of the csv file and cannot be written at the same time, you'd do the same as above but with an update query to fill in the missing fields.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    Grafixx01 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Posts
    13
    Micron,

    Thanks for the response. It sounds like you got what I was saying. Follow-on question that I have is would it be easier to pull the FK from the ImportCSV imported file into each of the tables? Or would it be better to rename the field in the tblImportCSV to mimic the tables within the database? Mind you that the imported CSVs do not change ever in format.

    **Oh, also, since I haven't messed with Access in over a decade, how can you see (other than Duh, look for the same fieldnames or ones with '_FK' listed after it) the foreign keys?

  4. #4
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Easiest way to find the foreign keys is to look at your relational diagram and look to the fields the primary keys are connected to.

  5. #5
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Hmm, I thought I replied. Came back because of an email and I see the 'restore content' thing in the post area, which is a great feature.

    yes to first and don't know for the second. Usually, csv imported data is un-normalized and not suitable "as is" for tables so one creates a target table that accepts it as is. The final tables are really all you need to worry about being properly designed, but you should be able to tell by the target table field names which ones contain your pk/fk values I would think.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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: 6
    Last Post: 08-17-2014, 06:14 PM
  4. Query pulling data from multiple tables
    By jetman5843 in forum Queries
    Replies: 3
    Last Post: 04-08-2014, 12:27 PM
  5. Replies: 1
    Last Post: 04-30-2012, 08:42 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