Stop append query from adding the same data multiple times if run more than once.

[Bobwords]

Hello!

I'm running a split database with about 20 different users with local files. I have an update query that I need to run every time someone submits specific items to the database. Is there a way to build an append query that will only add records that aren't yet in a table? If I have 5 or 6 users submit items, I don't want to see the first record copied 6 times as each append query is run. Is there a way to do that?

[JoeM]

Create an Unmatched Query that compares the data you are looking to add to the data already existing in the table.
Then, create the Append Query from this Unmatched query to only add those records which do not already exist.

[Bobwords]

Ok, so I created a query that should just return the items I need to process (this is a database being used for hardware RMAs)


This query returns whatever items haven't be processed yet (using an unmatched query as you said)


Now from that, I run an append query based off that query, and it runs.

(This is the appending query)

It sees the one row, and does the work.


But, after I run it, I still see the one item as needing to be processed. So the next time someone runs the appending query, it adds the same field again.

I'm sure this is just me setting something up wrong, but I need a little direction to figure out just what that is.

[June7]

I don't use APPEND query objects, I use VBA to run INSERT sql action.

The new record goes into [Return Labels]? It should add the value of CompanyName from RMA_Tracker?

Post the SQL statements.

What does the last image represent? Why are there multiple similar name column heads? I don't see anything duplicated.

[Bobwords]

I don't use APPEND query objects, I use VBA to run INSERT sql action.

The new record goes into [Return Labels]? It should add the value of CompanyName from RMA_Tracker?

Post the SQL statements.

What does the last image represent? Why are there multiple similar name column heads? I don't see anything duplicated.

I'll try to make what I want to do a little clearer. I have one table in my database that holds all information for a RMAs a team submits for hardware replacements. We generate shipping information though a UPS system. It was just added as a requirement that the system also be able to add return labels, which have different field names for the same data (CompanyName becomes Company, Billmethod becomes Billing, etc).

My thought was creating a secondary table that copies all the data from my main table (RMA_Tracker) to a database with the correct field names (Return Labels), then running a query from the Return Labels field that I can dump to a CSV. I wanted to make the appending update happen automatically whenever a user submits a new item through a form.

Is doing a separate table the best way to go about doing this? Is there a way to force the field names to change in a query (just for exporting to a CSV)? All I really need is for a number of fields to export to a CSV file with the field names different than they are in the RMA_Tracker Table. All the data I need is contained in that field.

[qa12dx]

if your table accepts dups when it shouldn't then it is set-up wrong. dont use pk for id. u can index id no dups.

for pk, use the combination of fields that will make the record distinct. after that run what ever query u want there will never be dups.

[Bobwords]

In some cases I might need duplicate fields from the main table. Beyond the primary key, there aren't really any fields that I can promise will be unique no matter what. Is there a method with how I currently have the database build that I can do what I need?

[June7]

qa12dx is suggesting you use a compound index. Multiple fields can be set as a composite unique index. They don't have to be the primary key. What causes a 'duplicate' record? What field or combination of fields causes a duplication?

You can 'change' field names in a query by using alias, like:

SELECT ID AS MyID FROM tablename;

[qa12dx]

you can set more than 1 field as pk.

for eg, im a production db u have a table with date, dept, item_cd, qty, req_cd, etc. to record units made. so we can say on the [same day] one [dept] cant have two [time_cd]. so in that table i'd highlight date, dept, item_cd and select pk. so when ever someone tries to enter date n, dept y and item_cd z twice it will give a dup error.

having said that, a simple solution for u might be a field temp yes/no. after you run your append query, run an update query to set the temp field to yes. (y/n value in access is 0 = no, -1 = y)

so in your append query you will add where temp=0, so after the update query is run, fields once added will be excluded next time the append query runs.