How to extract from Dropped Records using InsertQuery

[InsaneMembrane]

Hello I havent been able to put this question into the right words so I will be using an example.

I have 2 tables: "MasterList","RawData".

MasterList has a field 'ProjectID' with unique identifiers (say about 100) for various projects.

RawData uses ProjectID as a foreign key so any record inserted into RawData must have a key that exists in ProjectID.MasterList or the record will drop.

Now I am using a VBA/Queries to import a formated .csv file into my RawData table. My .csv starts with 1000 records but I wind up importing 900. The missing queries are due to ProjectIDs that do not exist in the "MasterList".

My Question(Finally): Is there a way to somehow grab the records that are automatically being dropped by access? I would then print these values out for the user to know so the new ProjectIDs may be added to the "MasterList"

My current solution would be to just use a Distinct SELECT Query on the .csv data (imported to a temp table) and compare the ProjectIDs.csv with the ProjectIDS.Masterfile before starting the import; however, I was wondering if there was a more creative solution to this anyone can share?

Note: My knowledge of SQL says no but I am suprised every day with the stuff people come up with.

[TheSmileyCoder]

Hi InsaneMembrane and welcome to Bytes.

Yes there is, its actually rather simple.

Ill go through the process:
1. Make a query containing both tables.
2. Join the tables by ProjectID in the query.
3. Double-Click the link/join between the tables, select the option: Include ALL posts from tbl_RawData and only those from tbl_Master where the joined fields are equal.
4. Finally: Add a criteria "Is Null" on the ProjectID field from the master table.
5. Select those fields you wish to include/show from the tbl_RawData.

In simple terms: The query selects all records from tbl_RawData, that does not have a matching ID in the master table. I've included a screenshot showing an example of such a query.

Depending on your needs, you could modify the query to automatically insert the missing IDs (depending a bit on your table setup).

[NeoPa]

First, before you do anything else, you need to change the import so that it imports into a table with no restrictions. This is essentially introducing an extra step into the procedure, but it does give you extra flexibility on down the line.

Next, you can import any valid data from this new table into your RawData table.

As you now have all the original data available to you inside your database, you can now produce a query/report of what failed and why. Smiley's post gives clear instructions on that aspect.

Welcome to Bytes!

[InsaneMembrane]

Thank you for both responses

NeoPa: Actually I did import the csv into a temp table since I needed to do some parsing and such since the data doesnt come in with correct formating (nor will it change grrr) so work was already done there :).

TheSmileyOne: lol I always ignore the Left/right join in access because I learned OracleSQL and somehow confused Joins with UNIONS lol. Everything works except I had it print the ProjectIDs that did not exist rather than the Project name because if the Project doesnt exist in the master list then nothing would be printed if we tried to print the name :p.

I threw in a Distinct in the SQL and got exactly what I needed.

This really is an easier method than what i was thinking. Thanks :).