Apend Query

[Maleficent]

Hi all

I have a database containing staff information - eg. name, dept, shift pattern etc

I am creating a new database to record absence

I want to lift some of the staff information from the staff database and put it into the absence database to save re-keying. ideally, when I update the staff database I want the absence database to be automatically updated

I thought I had solved this by creating a linked table

I don't want ALL of the table info in my new database. I had created a query to pull the relevant data and then append this to a new table. This way anytime the staff database is updated, I just run the append query on the absence database and it will update that too. HOWEVER this doesn't work - I'm assuming because it is attempting to append ALL records creating duplicates. What I really want is for it to update the existing data and only add any new records.

can this be done and if so how?

thanks in advance!

 

[Ranman256]

If you cannot key the target table to prevent duplicates, then you can try this:

Make an outer join.
In a query , have the source table, and the target table.
Join the 2 on common field.
Bring down the key field from both tables into the query,
Dbl-click the join line,
Set to :SHOW ALL RECORDS from target table, some recs from source table,
Under the key field criteria for the target table,enter NULL.

The query should now show you what is NOT in the target table. These can be added.
Convert it to an append query.

 

[jdraw]

If your proposed database deals with Staff and Absences, and your existing database deals with Staff and Shift patterns etc, why not create another table(s) or query in your current database?

Many will advise you to consolidate info into a database where practical and avoid fragments of common subjects in multiple databases.

I recommend you list your requirements for the bigger picture and build a conceptual model. It may be that a separate database is warranted, but make the decision from a point of knowledge.

Good luck.

 

[Maleficent]

I should clarify - the apend query works for adding new records but doesn't update existing records or remove deleted records

 

[arnelgp]

you need an update and insert query in ine.
this can be done as mentioned by other contrubutor:

Update tblHistory As t1 Right Join tblEmployer As t2 On t1.pk=t2.pk Set
t1.field1=t2.field1, t1.field2=t2.field2, ...

 

[jdraw]

???Append query is for Inserting new records.

To update an existing record you would use an UPDATE query and set an exiting field(s) value(s) to new value(s).

Many people do not Delete records, but have a flag to identify a record as logically deleted. Many times having the "logically deleted records" available can assist with some history facts. You could set such a flag by using an Update query.

To physically delete a record(s) you would use a Delete query. Beware a Delete query physically removes records not fields.

 

[plog]

I want to lift some of the staff information from the staff database and put it into the absence database to save re-keying. ideally, when I update the staff database I want the absence database to be automatically updated

Action queries (UPDATE, APPEND, DELETE) are generally hacks around poor designs or a lack of understanding about databases. I'm fairly certain one or both of those are true in this case.

You shouldn't have redundant data that needs to stay in synch. Instead you store it in one place and use that when needed. Even if you just need a subset for another part. That's the beauty of queries--you can set them up, get just what you need and use them just like they were tables.

A linked table should work, so perhaps you can tell us why it didn't. Better yet, why can't all this data live in just one backend?

 

[Maleficent]

Thanks everyone. I think on reflection i might just add the absence stuff into the headcount database and make it more of a general staff database.