Solved Import Data - update/append query (1 Viewer)

[Kayleigh]

I have a spreadsheet of denormalised data. The database I would like to import it to is normalised into two+ related tables. So what is best way to import this - using update or append query??

See sample data file and DB.

 

[Ranman256]

import records from a file

1. save the file (excel, or text) to a generic name everytime: C:\TEMP\File2import.xls (or File2import.txt)
2. attach (link) the file as an external table, like tFile2Import
3. make a query that appends data from tFile2Import table to the target table(s).
this query would use * to import all fields with the same name, but if the 2 tables dont have the same names, you must add the source fields and target fields to the query(s).
4. put this query(s) in a macro, run the macro.

Once this is done, the import steps are now:
1. save the new file and overwrite the old one
2. run the macro
done.

you can make as many queries as it takes to put the data in all the correct tables. put in macro.

 

[MajP]

You likely have to do both. From your example those staff members do not exist in your staff table.
1) index your tables with unique indices so that you cannot insert a duplicate
Example staff: Make a compound unique index on firstname, lastname, and DOB
If you try to input a duplicate it will not and nothing will happen. If you use currentdb.execute you will not event get an error
2) Insert staff not in staff table
3) I cannot imagine you would update the staff table from the results. But at this point all staff is in the table. You can run an update to update the address, phone, etc. The problem is you do not know the staffID, so do it on first and last name if that is enough. Or include DOB.
4) If you join the linked excel to your staff table by name your query will include the staffID. Now do an insert query with this into your results table since it has the staff ID for the foreign key. I assume this is always an insert.

 

[Kayleigh]

Actually the sample data was ambiguous because I have put in pseudonyms in place of real data so as far as I know all staff in spreadsheet is already in the database - although names are not always spelled correctly!
I had to run several updates to add staffID to the imported data for practicality. So now I'll use this table to update the other two tables but I guess I should be doing this in two levels as it will be appending to two tables?

 

[MajP]

Your import table does not have a staff ID. So in order to update the child tables you will have to link on first and last to the staff table to get the primary key. If the names are not spelled correct then you should also do a missing query to find if you have a staff in your import not matching a staff in your staff table. You want to verify that first. You may have to fix spellings. Those results will not be imported.

 

[Pat Hartman]

And finally, you can't use a linked Spreadsheet in an update query since the spreadsheet itself is not updatable by the query. You would have to import the spreadsheet first. Since that leads to bloat, a better solution is to create a template database with just an empty table in it. When you are ready to import a new file, copy the master template to replace the one you last used. Run an append query to append the data from the spreadsheet to the template database. Then the update query will join the linked table in the template database to update each table.. Sorry, got to run

 

[MajP]

That is a good point about the non updateable query. You could do your update in code instead of an update query, but that is a little more work. You can import directly into your db, but must remember to compact and repair or you db will get big.

 

[Kayleigh]

I have imported relevant columns of spreadsheet into database. Can you guide me how to do the append query to copy data onto two tables.

(Ps. the dates have been copied in American format - how do I change that?)

 

[MajP]

So your import data will really have the staffID, and all imports will exist in the staff table? That is far easier if true.

 

[Kayleigh]

Yes - as I mentioned.

 

[MajP]

As far as I can tell it is a simple insert query

INSERT INTO jtblcovidtests
            (fldcovidregid,
             fldbarcode,
             fldtestdate,
             fldtesttime,
             fldtestresultid,
             fldsymptoms,
             fldcovidtestid)
SELECT tblcovidregister.fldcovidregid,
       importcoviddata.barcode,
       importcoviddata.DATE,
       importcoviddata.TIME,
       importcoviddata.[test result],
       importcoviddata.[covid symptoms],
       importcoviddata.id
FROM   importcoviddata
       INNER JOIN tblcovidregister
               ON importcoviddata.fldstaffid = tblcovidregister.fldstaffid;

You are inputting into your junction table. However, this may be fine but only 7 records get imported. Because only registered staff get imported. Is that really how you want to design it? That may be correct, I am just wondering?

 

[Kayleigh]

Not really because there is currently no data in CovidRegister table - so this must be added BEFORE inserting into junction table.

 

[MajP]

I see. I thought you registed the staff, then they went to get the test, then the results comeback.

 

[MajP]

If I understand. You first append the registry and then append the results. However, there are fields in the reg that are not in the results (travel, vaccine, etc).

 

[Kayleigh]

Ideally we should have logged all this data in the DB but it was done on a denormalised excel spreadsheet...

Those queries worked fab on my end! Thanks ever so much!