Append Query Issue

[paul.clarke]

I have created a table for supplier production and i have set up an append query which updates this table from an imported excel sheet with all the data. This works fine the first week.


My trouble is when i get the new data from suppliers the next week and import into access again, and then run the append it adds all the records, not just the new ones.

Every week i run a list of outstanding orders for that supplier, so obviously there will be new records appearing on this list as weeks go by, and previous data given to me will change from last week - I have an update query for this -This works fine also.

So how can i get my append query to only append the new records, but i still need my update query to update all records (new and old) with the latest information.

thanks

 

[highandwild]

Have a look at this Paul.

http://office.microsoft.com/en-us/access/ha102051321033.aspx

Once you have set the unmatched query up you can then append this data to the new table.

Let me know if you need any more help?

 

[MStef]

Try to do it via PRIMARY KEY.
Are there in the Excel sheet any data which is unique
and unchangeable. Proclaim this data in the access table
as a Primary Key. it'll prevent the duplication of data.

 

[paul.clarke]

every record in the excel sheet is unique as we have 1 item per purchase order, everything links to or from this po number.

I did try use this as primary key, but in some instances the order qty is split so the po records will appear for each spilt so i can track each qty seperatly etc.

Am i correct in thinking i can use the "unmatched" query to do the append, and then a "find duplicate" query to do the updates??

Cheers for the help so far.

 

[highandwild]

Will you get purchase order data for the same po number appear as new data one week when you already have data for that PO number in the existing data?

 

[paul.clarke]

potentially yes, for example PO number 12345 supplier could initaially provide a delivery date of april, then next week due to delay etc they could change the date to may, so i will need to update the record for that po to show the latest date/details.

 

[highandwild]

Hi

More questions, more complexity.

Without giving me top secret information can you post an
Access 2003 .mdb with the existing data and the new data.

 

[paul.clarke]

i dont even know how to post a database here, its massive also.

Basically there is 1 query which picks up all outstanding orders from the system, i have linked my blank table (production details) to this and added the fields to the query.

The supplier sees the po details and then in the blank fields enter they production data - which is what i am trying to append/update into the blank table in access.

as i say works fine for the 1st week, just when i did it this week, it duplicated all records from last weeks aswell as adding the new records.

 

[highandwild]

Just a sample of the data would be good.

Create a new database and import the existing table and the new data.

When you reply just beneath the Reply To Thread box in the Additional Options box there is a manage attachments button. Click on that, browse to select the mdb file and upload it.

It is so much easier with a copy of the data.

 

[paul.clarke]

Im sure you will understand but due to data protection i think it better not to post any data online, i think though however as my "unmatched query" finds only the new records, i can create an append query from this to append the new records into the table. and therefore if i create a "find duplicate" query i can update the database table from this.

in simple terms its basically po, product, date, qty then supplier fields i.e materials purchased, production start date, qty produced etc. Its the same format every week, only differences are new pos - with which supplier will enter all details requested, and if any changes since last week, they update them as relevant.