Append query help

[rlmorgan]

I have reviewed numerous posts both here and on Google and can’t seem to find the right solution to what I thought would be a common problem.

I have a table named “tblHTags” that contains 5 columns, two of which are “HTag” and “ProductID”. The original “tblHTags” table was created from a CSV file that needs to be updated on a weekly basis. How can I import a more current external CSV file without overwriting existing historical data? I only need to capture the newly added information contained in the CSV file. Normally, I would use a Find Unmatched query and then an Append query to accomplish this. My problem, how to accomplish this if I have two columns that need to be filtered? Both the “Htag” and “ProductID” fields can have duplicates in the column. I need to filter the record if it matches both “Htag” and “ProductID”.

This would be okay:
ID___HTag_______ProductID__Publisher__Version___TimeStamp
1____ISW12345___555111___Thomson___7.0.1_____11/1/2009
2____ISW12345___331122___InTech_____2.0_______4/14/2011
3____ISL56998____555111___Thomson___8.2_______4/16/2011

I need to filter this:
ID___HTag_______ProductID__Publisher__Version___TimeStamp
1____ISW12345___555111___Thomson___7.0.1_____11/1/2009
2____ISW12345___331122____InTech____2.0_______4/14/2011
3____ISW12345___331122____InTech____2.0_______4/14/2011
4____ISL56998____555111____Thomson__8.2______4/16/2011
5____ISL56998____555111____Thomson__8.2______4/16/2011


I need to somehow be able to append the new CSV data into the existing table without creating duplicates using BOTH the “Name” AND “ProductID” columns.

Can this be done?

 

[Alansidman]

I need to somehow be able to append the new CSV data into the existing table without creating duplicates using BOTH the “Name” AND “ProductID” columns.

I don't see a NAME column in your example.

 

[plog]

I need to filter the record if it matches both “Htag” and “ProductID”.

Define 'filter'. Also it would help if you posted some sample data in your table, some sample data of what was in the .csv file and sample data of what the table should look like after you performed this update process.

With only a slight understanding of what you want, my general method would be to import this .csv file into a temporary table, use a query to identify records you want to update and then update them based on that query.

If you can provide the sample data I asked for I think I can help you more specifically.

 

[vbaInet]

If you make both Htag and ProductID composite keys it will deter duplicate records based on those fields. By composite I mean primary keys.

 

[rlmorgan]

If you make both Htag and ProductID composite keys it will deter duplicate records based on those fields. By composite I mean primary keys.

I tried your suggestion but Access won’t let me. It states I have duplicate records and will not let me proceed.

I don't see a NAME column in your example.

Sorry, I renamed the field from "Name" to "HTag"

 

[vbaInet]

Remove the duplicates before setting it.

 

[Alansidman]

Here's a workaround.

http://www.datapigtechnologies.com/flashfiles/duplicateproblem.html

Alan

 

[vbaInet]

I won't normally go down the route of creating a new table and inserting into this new table just to get rid of duplicates. It can all be done in a DELETE query.

1. Use the DMin() function to get the min of ID per record (based on those two fields)
2. Test the new field in step 1 against the ID field to see if is equal
3. Delete those that are not equal.

 

[rlmorgan]

First of all everyone, thanks for the help.

At this point, following the all of your suggestions, been able to accomplish importing the new data. I am not sure that I am describing my problem well enough for everyone to follow me.

The data in the “tblHTags” table is based off of a SMS query of 5 specific software installations that are installed on 6,000 PCs in our environment. My original report has about has about 10,000 records.

The PCs in the table are given Htag names, and can have one or more of the 5 software packages installed on them.

i.e. The PC by the name ISW12345 can have, let’s say, 3 of the software packages installed. Thus, this PC will have 3 records in the table containing the same “Htag” PC name and 3 unique “ProductID” fields.

Htag............ProductID
ISW12345.....555111
ISW12345.....331122
ISW12345.....115588

This will also produce duplicate entries in the “ProductID” field as well, as we have the software packages installed on more than one PC.

Htag............ProductID
ISW12345.....555111
ISW12345.....331122
ISW12345.....115588
ISL56998.......115588

​

I need to run the SMS query weekly to find new installations of the tracked software packages and import anything that is new to the query. I don’t want to delete any of the existing data from the “tblHTags” table for historical purposes.

I need to somehow compare the SMS query output to the existing data in the “tblHTags” table and only import the new software installations that have accrued since the last SMS query was ran and imported.

 

[vbaInet]

At this point, following the all of your suggestions, been able to accomplish importing the new data. I am not sure that I am describing my problem well enough for everyone to follow me.

Your posts were already clear enough.

I don’t want to delete any of the existing data from the “tblHTags” table for historical purposes.

Your historical data contains anomalies if you are unable to set those two keys as Primary Keys. That needs to be sorted out.

 

[rlmorgan]

Okay, I'm not fully understanding what you are suggesting if you want both the “Htag“ and “ProductID” fields set as Primary Keys. It is my understanding that a Primary Key cannot contain duplicate entries. Yet both of these fields must contain duplicate entries.

 

[vbaInet]

A combination of both values cannot be duplicate.

There are no duplicated records based on what you presented here:


Htag............ProductID
ISW12345.....555111
ISW12345.....331122
ISW12345.....115588
ISL56998.......115588

Htag is repeated a few times but the combination of Htag AND ProductID doesn't present duplicate records. BOTH fields should be made primary keyS.

 

[rlmorgan]

And now the blind shall see. That makes sense now, let me give that a try.

 

[rlmorgan]

With that said it sounds like I would just use a standard Append query and ignore the errors. Would that be a correct assumption?

 

[vbaInet]

Correct rlmorgan.

Use Currentdb.Execute to run the query. You won't get any prompts.

 

[rlmorgan]

Thank you for bearing with me thought out this. Greatly appreciated and Thanks sent.

 

[rlmorgan]

Thank you for bearing with me thought out this. Greatly appreciated and Thanks sent.

 

[vbaInet]

Happy to help!