Hi Guys,
This is a single step in a data import and transformation Mod I am working on, I will need to reapt the step a fair few times. The import is for a number of tables that support cascade combo boxes. Tables mostly contain ids from the lower tier tables (so not to repeat data). The IDs are auto numbers and manual entries/deletions exist so I only want to append new, unique entries pulled from a new set of data to be added into the combo boxes underlying tables.
I have temporary tables that hold the new data, I have already removed all the duplicates and added in the correct IDs to the table in question. Now I need to add the new records from tblMakesTemp to Makes. Specifically;
tblMakesTemp.Nbody into Makes.BodyTypeID
And
tblMakesTemp.NMake into Makes. MakeName
But I don’t want to add it if there is already a record that matches those fields. From previous advices I am staying away from a DAO record set approach.
I see two options;
As seen in table tblMakesTempWithOLD I can still have the old entries in the temp table. I could make a field that is the combined values of the two required fields but just for OLD entries (which have a field that makes clear they are old) then make this field only allow unique values, then copy across the same combo for the new data. Now only unique entries that do not already exist will be allowed to be copied over and then I just delete the unwanted fields and records which self-identify and i'm left with only the stuff i want and can append to the main table. This would work but feel like the long way around.
Second option is to use INSERT INTO. Which I tired first, only they are two unrelated tables, and one is purely temporary. I feel like I would need a join, only there is no field that connects them. Also I don’t want only matching records but all records to be compared. My experience with joins is all based on normalised permanent tables with Primary and foreign keys and there it all works fine.
This is what I have but it doesn’t know what to compare without a join and I am not sure how to add without a relating field. I am also not 100% sure about the WHERE clause.
I think I need a sense check before I end up going down the wrong road again. What do you guys think. My test db is attached and let me know if the above didn’t make sense or you need more info.
This is a single step in a data import and transformation Mod I am working on, I will need to reapt the step a fair few times. The import is for a number of tables that support cascade combo boxes. Tables mostly contain ids from the lower tier tables (so not to repeat data). The IDs are auto numbers and manual entries/deletions exist so I only want to append new, unique entries pulled from a new set of data to be added into the combo boxes underlying tables.
I have temporary tables that hold the new data, I have already removed all the duplicates and added in the correct IDs to the table in question. Now I need to add the new records from tblMakesTemp to Makes. Specifically;
tblMakesTemp.Nbody into Makes.BodyTypeID
And
tblMakesTemp.NMake into Makes. MakeName
But I don’t want to add it if there is already a record that matches those fields. From previous advices I am staying away from a DAO record set approach.
I see two options;
As seen in table tblMakesTempWithOLD I can still have the old entries in the temp table. I could make a field that is the combined values of the two required fields but just for OLD entries (which have a field that makes clear they are old) then make this field only allow unique values, then copy across the same combo for the new data. Now only unique entries that do not already exist will be allowed to be copied over and then I just delete the unwanted fields and records which self-identify and i'm left with only the stuff i want and can append to the main table. This would work but feel like the long way around.
Second option is to use INSERT INTO. Which I tired first, only they are two unrelated tables, and one is purely temporary. I feel like I would need a join, only there is no field that connects them. Also I don’t want only matching records but all records to be compared. My experience with joins is all based on normalised permanent tables with Primary and foreign keys and there it all works fine.
This is what I have but it doesn’t know what to compare without a join and I am not sure how to add without a relating field. I am also not 100% sure about the WHERE clause.
Code:
Dim SQL As String
SQL = "INSERT INTO Makes (BodyTypeID, MakeName) " & _
"SELECT NBody, NMake " & _
"FROM tblMakesTemp" & _
"WHERE tblMakesTemp.NBody <> Makes.BodyTypeID AND tblMakesTemp.NMake <> Makes.MakeName;"
DoCmd.RunSQL SQL
End Sub
I think I need a sense check before I end up going down the wrong road again. What do you guys think. My test db is attached and let me know if the above didn’t make sense or you need more info.