Query for records with no matching records in another table?

Query for records with no matching records in another table?

Post by Sven Pra » Fri, 26 Nov 2004 06:44:45


Probably the answer is there just in front of me only awaiting me to
discover it,
but:

1: I want to build a query that returns all records in one table for which
there is no successful "join" in another table but I have not found what the
field
criteria should look like?

2: And if/when I succeed I should further like to build a new record (with
all fields)
to be added in the table where this record is missing.

Can this be done with an action query or am I barking up the wrong tree?

regards Sven
 
 
 

Query for records with no matching records in another table?

Post by Bas Cost B » Fri, 26 Nov 2004 06:53:43


Create a new query and choose the Unmatched Query Wizard.


It ought to be possible to insert those non-occurring records into the
other table, taking the query as data input. I never tried but that
doesn't tell all.

--
Bas Cost Budde, Holland
http://www.yqcomputer.com/
I prefer human mail above automated so in my address
replace the queue with a tea

 
 
 

Query for records with no matching records in another table?

Post by Sven Pra » Fri, 26 Nov 2004 07:15:41

Thanks, I'll try and see if it gets me going!

(And if nothing else works I can always create a new table with the missing
records and add this table separatly)

regards Sven
 
 
 

Query for records with no matching records in another table?

Post by Dave » Fri, 26 Nov 2004 09:30:41

The Unmatched query wizard should look after your first requirement and the
second one depends on what additional data (other than the join field) you
need to include in the new records. You may have to create a temporary table
that lists the join fields for the records you want to create; I have found
that when I try to do this using only queries, the final query, that I want
to do the appending, is not updateable.



the
 
 
 

Query for records with no matching records in another table?

Post by Sven Pra » Fri, 26 Nov 2004 09:52:37


My first requirement was indeed solved by the "unmatched query wisard".

I was not surprised discovering that it has been there all the time just
waiting for me to find it.

So far I have only found the possibility to create a new table with the
records to be added and then manually copy and paste all records in that
table into the other table.

I have a strong feeling that there should be an easier way of merging the
records from one table into another table of the same design?

And I had even expected there being a way of merging the records
selected in a query into an existing table?

regards Sven
 
 
 

Query for records with no matching records in another table?

Post by Dave » Fri, 26 Nov 2004 12:48:17

The general answer to merging data in to an existing table is an append
query. If you can use a select query to assemble the new data you need to
merge into the existing table, you should be able to change it to an append
query directly. Of course, you can also use an append query to merge
constant values (e.g. a particular date) or calculated values to the
appropriate fields in the existing table.






you


what
tree?