Records remain in query results. Why?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have an append query with two tables to move approx 9 fields. When I run
the query it seems to work but the same records seeem to stay in the query
results eveytime I run the query. This happends in a few other querys I have.
Am I missing something in my Criteria that keeps these records in my query
results? Please help!
Thank you
 
An append query will not 'move' records from one table to another by itself.
It will add records to a table, but not remove them from the original table.

Please provide the SQL statement for the append query and any other queries
used in this process.
 
Great! So I can run an update query instead or do a delete query after the
append query has ran?
 
troy said:
Great! So I can run an update query instead or do a delete query after the
append query has ran?
Click to expand...

Run the append query first, and then a delete query with the same criteria.

You may consider wrapping the entire thing in a transaction (check out
the help system first) in case you have to rollback (the append fails).
You'd hate to delete the stuff if it wasn't appended properly.
 
Here is the SQL statement.. It is going to a history table with the exact
fields and data types. Does the join make any difference the way it is set up
now? It keeps showing me the same 4 records with the Null validation error.
It is an append query. Thank you for your help and if you need additional
info let me know. Thanks again

INSERT INTO tbl_History_Form ( FormId, FormNumber, Description,
DigitalSignature, FileName, EffDate, Attachment, FormAvailable, DateChanged )
SELECT DEV_dbo_TblForm.FormId, DEV_dbo_TblForm.FormNumber,
DEV_dbo_TblForm.Description, DEV_dbo_TblForm.DigitalSignature,
DEV_dbo_TblForm.FileName, DEV_dbo_TblForm.EffDate,
DEV_dbo_TblForm.Attachment, DEV_dbo_TblForm.FormAvailable, Now() AS
DateChanged
FROM dbo_TblForm LEFT JOIN DEV_dbo_TblForm ON dbo_TblForm.FormId =
DEV_dbo_TblForm.FormId
WHERE (((DEV_dbo_TblForm.Description) Not Like [dbo_TblForm].[description]))
OR (((DEV_dbo_TblForm.FileName) Not Like [dbo_TblForm].[filename])) OR
(((DEV_dbo_TblForm.EffDate)<>[dbo_TblForm].[effdate])) OR
(((DEV_dbo_TblForm.DigitalSignature)<>[dbo_TblForm].[digitalsignature])) OR
(((DEV_dbo_TblForm.Attachment)<>[dbo_TblForm].[attachment])) OR
(((DEV_dbo_TblForm.FormAvailable)<>[dbo_TblForm].[formavailable]))
WITH OWNERACCESS OPTION;
 
I must have described my results wrong. I have two tables. One linked to SQL
the other just linked to another db. The target tabl has exact fields as the
source tbls. questions...
1. do the results always show in the datasheet view view once it has either
been appended or updated?
2. if so is there a macro etc to get rid of the results after it has updated
so you only show last results?

Right now my main concern is making sure it runs then does not show the
same records next time I run the query. Hope this make sense?
Thanks again
 
troy said:
Here is the SQL statement.. It is going to a history table with the exact
fields and data types. Does the join make any difference the way it is set up
now? It keeps showing me the same 4 records with the Null validation error.
Click to expand...

This is the first time you are mentioning anything about an error.
Before you simply said that it would copy the records over and not
remove them.
It is an append query. Thank you for your help and if you need additional
info let me know. Thanks again
Click to expand...

It looks to be correct in copying the records from your dbo_TblForm
(table) to tbl_History_Form (table). A little misleading on the object
names because you have 'form' on them. Those two objects I mentioned are
in fact the tables right? One that is the linked SQL and one that is the
linked database?

If you are getting a null validation error, it could be the way the
linked table is designed. One of the fields could have it's
AllowZeroLength set to No, but you could be trying to insert such a
record without converting it to a null. Or there could be a required
field that is getting data from the SQL linked table.
 
Back
Top