By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,836 Members | 2,011 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,836 IT Pros & Developers. It's quick & easy.

Query for records with no matching records in another table?

P: n/a
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


Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Sven Pran wrote:
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?
Create a new query and choose the Unmatched Query Wizard.
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.


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.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
Nov 13 '05 #2

P: n/a
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

"Bas Cost Budde" <b.*********@heuvelqop.nl> wrote in message
news:co**********@news2.solcon.nl...
Sven Pran wrote:
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?


Create a new query and choose the Unmatched Query Wizard.
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.


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.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea

Nov 13 '05 #3

P: n/a
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.

"Sven Pran" <no*******@mail.please> wrote in message
news:ih*******************@news2.e.nsc.no...
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

Nov 13 '05 #4

P: n/a

"Dave" <dm******@island.net> wrote in message
news:RI9pd.320807$nl.198385@pd7tw3no...
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.

"Sven Pran" <no*******@mail.please> wrote in message
news:ih*******************@news2.e.nsc.no...
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


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
Nov 13 '05 #5

P: n/a
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.
"Sven Pran" <no*******@mail.please> wrote in message
news:q1*******************@news2.e.nsc.no...

"Dave" <dm******@island.net> wrote in message
news:RI9pd.320807$nl.198385@pd7tw3no...
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.

"Sven Pran" <no*******@mail.please> wrote in message
news:ih*******************@news2.e.nsc.no...
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


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

Nov 13 '05 #6

P: n/a

"Dave" <dm******@island.net> wrote in message
news:5Ccpd.315486$Pl.191541@pd7tw1no...
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.


Yes, I have looked at the append query, but must admit I didn't immediately
quite understand how to use it in practice.

Guess I shall take a closer look.

thanks Sven
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.