update & add data to a table using query

[Pierkes]

Hi,

I have a database table called : "tbl_advise urs" with the fields;
- ID_adv
- NR_adv (primary key)
- Segment
- ZIP
- TEL
- AM
- AD

Now, every now and again i get an updated list for this table with;
- update rows (so the existing records must be update)
- new record
- deleted records
This is delivered in excel. I then import this data in a table called "import_adv "

I now have a "update Query" that works fine for updating existing records. Fields in the qyery;
- Segment
- Zip
- TEL
- AM
- AD

However it does not append the new records to the existing database and it does not delete records that are not in the "import_adv " table

How can i make sure that new records in the import_adv table are appended tot the tbl_adviseurs ?
How can i make sure that records that are deleted in the import_adv table are also deleted from the tbl_adviseurs ?

Any help would be greatly appreciated!
Pierre

[zmbd]

You'll need at least two queries
One that will update and append the records of interest.
One that will delete the offending records

However, you haven't told us how you know which records to what to...

[Seth Schrock]

It sounds like a Find Unmatched query might be needed to find the records that are in import_adv and aren't in tbl_adviseurs to know which ones to append. A reverse would tell you which ones to delete.

[zmbd]

Seth, let us not guess here, let OP tell us.

[Pierkes]

hi zmdb,

How i know which records to add, delete or modify ?

- The primary key is the key to this. I set the primary key not to the ID but ot the field NR_adv.
This is the number we give to a "adviseur" and is unique.

When i get an updated list of "adviseur" i know;
- which are new entries - new NR_adv that i did not have before
- which entries are deleted - NR_adv that are not in the list anymore
- which entries to modify - all records that have the same NR_adv in both tables

does this answer your questions ?
regards,
Pierre

[zmbd]

I like to make sure I understand things correctly before proceeding... it's the Chemist in me :) :::

So if I understand this correctly:
You need to have "tbl_advise urs" be an exact duplicate of "import_adv " ?

[Pierkes]

Hi zmdb,

You are right, it should be an exact copy.

Regards,
Pierre

[zmbd]

Well, as I suspect we have linked fields on [NR_adv] we can't just delete the information and import the table; thus, as I stated in post #2 you will need two queries, one of which will be the unmatched as Seth stated.

The query wizard actually doesn't do too bad a job on building the unmatched query... let it do that for you.
Once built, run once to make sure it returns the unmatched records from "tbl_adviseurs" , if so, then switch to design mode, look at the ribbon, query type group, a red-X with an exclamation point (bang) - this is a run as delete. Click this, it will change the select to a delete query... now click on the run (just the red Bang)
I do this in two stages by hand just to be sure I have things correctly.
You can save this query as a delete query if you like and then the next time all you have to do is click on it in the navigation pane to run it... I personally don't like doing that - but that's me.

As for your update...
Open your update query in design mode...
Is there a join between the [NR_adv] fields for "Import_adv " and "tbl_adviseurs" ?
You need to edit this relationship so right click...
You need to select the option so that all of the records from the transfer table (import_adv) are returned and the matching records from "tbl_adviseurs" .
If you do not have this relationship then you need to create this relationship (left click and drag [import_adv]![NR_adv] over to [tbl_adviseurs]![NR_adv].... now right click the line and select the option to return all records from [import_adv] and matching records)

Your SQL should look something like this:
(now the field names will not be the same)
(tbl_people.peo ple_pk = tblstaff.staffi d are the related fields)

UPDATE 
    tbl_people LEFT JOIN
      tblstaff ON 
         tbl_people.people_pk 
            = tblstaff.staffid 
SET tblstaff.staff_FirstName 
     = [tbl_people]![people_FirstName]
     , tblstaff.staff_LastName 
         = [tbl_people]![People_LastName]
     , tblstaff.staff_email 
         = [tbl_people]![people_email];

(of course, in the sql view this will be all on one or two lines.)

and Bob's Your Uncle, you should be done.


Once these queries are running correctly, you can automate the process by creating either VBA or Macro code to call each query... I prefer VBA as I'd do a double check for stuff.

[nico5038]

When it should be an exact copy of the received file, you could use:

delete * from tbl_adviseurs
'and
select into tbl_adviseurs select * from import_adv

This will however only work when you haven't defined relations to the NR_adv field.

For adding records you can use the SELECT INTO anyway, as duplicates (those you UPDATE) will not be inserted, just suppress the error messages by running the query in VBA like:

currentdb.execute ("<your insert query>")

Nic;o)

[zmbd]

Noco5038:

(...) I suspect we have linked fields on [NR_adv] we can't just delete the information(...)

(...)Once these queries are running correctly, you can automate the process by creating either VBA or Macro code to call each query(...)

I already covered your entire post ?!

[nico5038]

@zmbd:
1) "Suspect" isn't "sure", thus my remark. Pierkes could have programmed relationships, thus making the replace table possible.
When the NR_adv is used as a relation in other tables, additional queries will be needed to remove the deleted values from those tables.
2) The VBA sample shows the syntax how to suppress the warningmessages , by not using a parameter for the errorhandling.

Guess you didn't cover my entire post :-)

Nic;o)