Use an Update Query to Compare Tables and Add Non-Existing Data to Source Table

[kwolfel]

Hello,



I am attempting to create labels from my database. The labels come from a report that I automatically fill from a table. The issue I am having is with updating my table.

I have two tables one is called Components and the other is Blue Tag Log. The Components table gets it's information from tables (filled through user interface forms within the the database) that I extract the required data from using queries. I want to be able to check the Components table and only pull data where the STP (short for system turnover package) field is filled. I then want to take these entries with STP filled, compare the tag field of the Components table to the tag field of existing entries in the Blue Tag Log, and only add the instances that do not already exist in the Blue Tag Log table. Is there an easy way to do this? I was attempting to do so with an Update query where the tag fields were joined between the two tables but it doesn't seem to do anything. (See SQL code below)

UPDATE [Blue Tag Log Test] RIGHT JOIN BlueTagComponent ON [Blue Tag Log Test].DEVICE = BlueTagComponent.CompTag SET [Blue Tag Log Test].DEVICE = [BlueTagComponent].[CompTag], [Blue Tag Log Test].[STP NO] = [BlueTagComponent].[STPNo], [Blue Tag Log Test].[STP NAME] = [BlueTagComponent].[STPDesc], [Blue Tag Log Test].[EID NO] = [BlueTagComponent].[DrawingNo], [Blue Tag Log Test].[LINE 1] = [BlueTagComponent].[Line1], [Blue Tag Log Test].[LINE 2] = [BlueTagComponent].[Line2], [Blue Tag Log Test].[LINE 3] = [BlueTagComponent].[Line3]
WHERE ((([Blue Tag Log Test].[STP NO])<>"" Or ([Blue Tag Log Test].[STP NO]) Is Not Null));

Additionally, I want to add a feature where the existing data within the Blue Tag Log could be refreshed when new information was added to the database.

Any help would be appreciated. I have experience with Access but I can't seem to wrap my head around this one.

[June7]

If record does not exist in Components then the SQL action would be an INSERT, not UPDATE.

Want to provide db for analysis? Follow instructions at bottom of my post.

[kwolfel]

Hi June7,

I have attached a sample of the database with only the information needed for my queries. The "qryUpdateBlueTagLog" is the one I am having the issue with currently. The other queries are used to populate the BlueTagComponent table and shouldn't be an issue. I included them as I would eventually have a macro that would go through the line of queries to eventually populate the Blue Tag Log table with new data.

As I stated in my first post, I want to pull in data from the BlueTagComponent table that does not exist in the Blue Tag Log table and only when the STP Number is field is filled. It seems like it should be able to work in a query but it's turning out to be more complicated than I anticipated.

Sample DB.zip

Thank you for your help!

[June7]

I want to be able to check the Components table and only pull data where the STP (short for system turnover package) field is filled. I then want to take these entries with STP filled, compare the tag field of the Components table to the tag field of existing entries in the Blue Tag Log, and only add the instances that do not already exist in the Blue Tag Log table.

Let me revise my earlier statement: If record does not exist in BlueTagLog table then the SQL action would be an INSERT, not UPDATE. SELECT records from Components for INSERT to BlueTagLog. General syntax is:

INSERT INTO tablename1(field1, field2) SELECT field1, field2 FROM query1 WHERE ...
You reference Components and BlueTagLog tables above but the query uses BlueTagComponent and BlueTagLogTest tables.
query1 would be a FindUnmatched query to isolate records that are in BlueTagComponents but not already in BlueTagLogTest

Why are UPDATE and INSERT sql actions needed? Why not using form/subform arrangements for data entry?

[kwolfel]

I have made the updates, tweaked my WHERE criteria (since nothing should be Null due to my use of the NZ function), and it works perfectly! Thank you for the help on this, I really appreciate it!

Also, I have been using the Blue Tag Log Test as a test table for this query. It's a copy of the actual Blue Tag Log.