473,839 Members | 1,398 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Query for records with no matching records in another table?

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
6 8168
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
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.*********@he uvelqop.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
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******** ***********@new s2.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

"Dave" <dm******@islan d.net> wrote in message
news:RI9pd.3208 07$nl.198385@pd 7tw3no...
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******** ***********@new s2.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
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******** ***********@new s2.e.nsc.no...

"Dave" <dm******@islan d.net> wrote in message
news:RI9pd.3208 07$nl.198385@pd 7tw3no...
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******** ***********@new s2.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

"Dave" <dm******@islan d.net> wrote in message
news:5Ccpd.3154 86$Pl.191541@pd 7tw1no...
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
11210
by: Joe Del Medico | last post by:
I have two tables A & B and I want to delete all the records in A that are not in B. Can I do this in the query builder? It seems like a simple problem. I can easily find the records in A that aren't in B by using the query wizard to come up with a query "A without matching B". I added table A to the query builder and Query "A without matching B" joined them with the index field (Loan Number) made the query a delete query and
3
4615
by: Tom Mitchell | last post by:
All: I'm stumped on a query. How do I find duplicates in a table where one of the duplicates has values is a certain field and the other doesn't. For example, I have the following table: tblItems ItemID ItemLabel
5
1792
by: Bob Weisenburger | last post by:
I have a table of "memos". each record in that table has a primary key "memo ID" that is autonumber. I have another table that is "memo receipts" with each record having a "receipt id" field that is not a primary key. When a user reads a memo, I create a receipt record that shows that they have read and acknowledged the memo. "receipt id" is set equal to "memo id". I have this part working fine. Next what I want is when a user checks...
5
3301
by: Lyn | last post by:
This one is difficult to explain, so I will cut it down to the basics. I have a major table 'tblA' which has an autonum field 'ID-A' as primary key (of no significance to users). 'tblA' contains many fields including picture and memo fields. The main user-selectable field is 'NameA'. There is also a crossreference table (let's call it 'tblB') which provides a secondary method of accessing records in 'tblA'. The main fields in 'tblB'...
8
3728
by: Maxi | last post by:
There is a lotto system which picks 21 numbers every day out of 80 numbers. I have a table (name:Lotto) with 22 fields (name:Date,P1,P2....P21) Here is the structure and sample data: "Date","P1","P2","P3","P4","P5","P6","P7","P8","P9","P10","P11","P12","P13","P14","P15","P16","P17","P18","P19","P20","P21" 1/1/2005,1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21 1/2/2005,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22
10
2115
by: Robert | last post by:
I am an attorney in a non-profit organization and a self-taught programmer. I'm trying to create a client db that will allow me to search for potential conflicts of interest based either on Social Security # or on Last Name. I've created two different tables with the following fields in each table: ClientInfo Client# (primary key) First Name Middle Name Last Name
5
7575
by: DougieC | last post by:
Hello all. First off, Im not a programmer so I am some what stumbling through this. 8 years of social science education never trained me to handle real world problems, such as figuring out how to develop a Data Base for the military. So a summary of my problem. I am using MySQL, on the suggestion of the higher ups, to combine multi-yeared data into one master table. My logic works like this. Populate a Master Table with unique...
5
1416
by: CindySue | last post by:
I have three tables. LP, TA and PR. I need to create a report that selects only those records in LP which have one or more matching records in either the TA or the PR table (or it could have multiple matches in both). I created a report with records from LP and two sub reports with records from TA and LP, but of course I get records that don't have entries in either table. I tried to create a query using joins, but it gave me an error message...
16
3529
by: ARC | last post by:
Hello all, So I'm knee deep in this import utility program, and am coming up with all sorts of "gotcha's!". 1st off. On a "Find Duplicates Query", does anyone have a good solution for renaming the duplicate records? My thinking was to take the results of the duplicate query, and somehow have it number each line where there is a duplicate (tried a groups query, but "count" won't work), then do an update query to change the duplicate to...
0
10908
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10587
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10295
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9426
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7829
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
7018
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5682
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
4064
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3136
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.