query two tables

Hi, I am very new to Access and am having some trouble.

I have two tables in my DB, one called Data that contains approx 14,000 
names and another table called Returned that has approx 1000 names. I want to 
create a query to remove the Returned names from the list of names in the 
Data table so that the resulting query only gives me the remaining 13,000 
names that were NOT in the returned table.

I tried creating a query with a Criteria in the name filed of "[data]![Name] 
Not Like [Returned]![Name]".  This results in a total of 1000 names in the 
query instead of the 13,000 I would expect to get. I guess it is being 
limited to the number of names in the Returned table.

How can I achieve the results I am looking for, which is a list of names 
from the data table that do not include the names in the Returned table?

Your help is greatly appreciated.

Thanks
Robert


0
Utf
5/29/2007 8:15:02 PM
access.queries 6343 articles. 1 followers. Follow

4 Replies
591 Views

Similar Articles

[PageSpeed] 29

You can use the query wizard to create an Unmatch records query, that list 
all the records that are in one table but not in the other.


-- 
Good Luck
BS"D


"rt10516" wrote:

> Hi, I am very new to Access and am having some trouble.
> 
> I have two tables in my DB, one called Data that contains approx 14,000 
> names and another table called Returned that has approx 1000 names. I want to 
> create a query to remove the Returned names from the list of names in the 
> Data table so that the resulting query only gives me the remaining 13,000 
> names that were NOT in the returned table.
> 
> I tried creating a query with a Criteria in the name filed of "[data]![Name] 
> Not Like [Returned]![Name]".  This results in a total of 1000 names in the 
> query instead of the 13,000 I would expect to get. I guess it is being 
> limited to the number of names in the Returned table.
> 
> How can I achieve the results I am looking for, which is a list of names 
> from the data table that do not include the names in the Returned table?
> 
> Your help is greatly appreciated.
> 
> Thanks
> Robert
> 
> 
0
Utf
5/29/2007 8:33:03 PM
I'm sorry, but I am very new to access. How do I create an Unmatch records 
query?

thanks for your assistance.

"Ofer Cohen" wrote:

> You can use the query wizard to create an Unmatch records query, that list 
> all the records that are in one table but not in the other.
> 
> 
> -- 
> Good Luck
> BS"D
> 
> 
> "rt10516" wrote:
> 
> > Hi, I am very new to Access and am having some trouble.
> > 
> > I have two tables in my DB, one called Data that contains approx 14,000 
> > names and another table called Returned that has approx 1000 names. I want to 
> > create a query to remove the Returned names from the list of names in the 
> > Data table so that the resulting query only gives me the remaining 13,000 
> > names that were NOT in the returned table.
> > 
> > I tried creating a query with a Criteria in the name filed of "[data]![Name] 
> > Not Like [Returned]![Name]".  This results in a total of 1000 names in the 
> > query instead of the 13,000 I would expect to get. I guess it is being 
> > limited to the number of names in the Returned table.
> > 
> > How can I achieve the results I am looking for, which is a list of names 
> > from the data table that do not include the names in the Returned table?
> > 
> > Your help is greatly appreciated.
> > 
> > Thanks
> > Robert
> > 
> > 
0
Utf
5/29/2007 8:43:01 PM
Either use the query wizard when you create a new query, or use this SQL

SELECT BigTable.*
FROM BigTable LEFT JOIN SmallTable ON BigTable.[Name]= SmallTable.[Name]
WHERE SmallTable.[Name] Is Null

************
Note: you need to change the names of:
BigTable = the name of the table with 14,000 records
SmallTable = the name of the table with the 1000 record
[Name]= the name of the field that link the two tables

You copy and paste this SQL to a new query, 
If you need more help, please post the name of the tables and the field that 
link them together


-- 
Good Luck
BS"D


"rt10516" wrote:

> I'm sorry, but I am very new to access. How do I create an Unmatch records 
> query?
> 
> thanks for your assistance.
> 
> "Ofer Cohen" wrote:
> 
> > You can use the query wizard to create an Unmatch records query, that list 
> > all the records that are in one table but not in the other.
> > 
> > 
> > -- 
> > Good Luck
> > BS"D
> > 
> > 
> > "rt10516" wrote:
> > 
> > > Hi, I am very new to Access and am having some trouble.
> > > 
> > > I have two tables in my DB, one called Data that contains approx 14,000 
> > > names and another table called Returned that has approx 1000 names. I want to 
> > > create a query to remove the Returned names from the list of names in the 
> > > Data table so that the resulting query only gives me the remaining 13,000 
> > > names that were NOT in the returned table.
> > > 
> > > I tried creating a query with a Criteria in the name filed of "[data]![Name] 
> > > Not Like [Returned]![Name]".  This results in a total of 1000 names in the 
> > > query instead of the 13,000 I would expect to get. I guess it is being 
> > > limited to the number of names in the Returned table.
> > > 
> > > How can I achieve the results I am looking for, which is a list of names 
> > > from the data table that do not include the names in the Returned table?
> > > 
> > > Your help is greatly appreciated.
> > > 
> > > Thanks
> > > Robert
> > > 
> > > 
0
Utf
5/29/2007 9:06:02 PM
Thank you. this worked

"Ofer Cohen" wrote:

> Either use the query wizard when you create a new query, or use this SQL
> 
> SELECT BigTable.*
> FROM BigTable LEFT JOIN SmallTable ON BigTable.[Name]= SmallTable.[Name]
> WHERE SmallTable.[Name] Is Null
> 
> ************
> Note: you need to change the names of:
> BigTable = the name of the table with 14,000 records
> SmallTable = the name of the table with the 1000 record
> [Name]= the name of the field that link the two tables
> 
> You copy and paste this SQL to a new query, 
> If you need more help, please post the name of the tables and the field that 
> link them together
> 
> 
> -- 
> Good Luck
> BS"D
> 
> 
> "rt10516" wrote:
> 
> > I'm sorry, but I am very new to access. How do I create an Unmatch records 
> > query?
> > 
> > thanks for your assistance.
> > 
> > "Ofer Cohen" wrote:
> > 
> > > You can use the query wizard to create an Unmatch records query, that list 
> > > all the records that are in one table but not in the other.
> > > 
> > > 
> > > -- 
> > > Good Luck
> > > BS"D
> > > 
> > > 
> > > "rt10516" wrote:
> > > 
> > > > Hi, I am very new to Access and am having some trouble.
> > > > 
> > > > I have two tables in my DB, one called Data that contains approx 14,000 
> > > > names and another table called Returned that has approx 1000 names. I want to 
> > > > create a query to remove the Returned names from the list of names in the 
> > > > Data table so that the resulting query only gives me the remaining 13,000 
> > > > names that were NOT in the returned table.
> > > > 
> > > > I tried creating a query with a Criteria in the name filed of "[data]![Name] 
> > > > Not Like [Returned]![Name]".  This results in a total of 1000 names in the 
> > > > query instead of the 13,000 I would expect to get. I guess it is being 
> > > > limited to the number of names in the Returned table.
> > > > 
> > > > How can I achieve the results I am looking for, which is a list of names 
> > > > from the data table that do not include the names in the Returned table?
> > > > 
> > > > Your help is greatly appreciated.
> > > > 
> > > > Thanks
> > > > Robert
> > > > 
> > > > 
0
Utf
5/29/2007 11:30:00 PM
Reply:

Similar Artilces:

Erratic results from query criteria
I am getting different results from running the same query with the same selections. One moment it is all behaving as expected, the next it has gone haywire. (I have done what appears to me to be EXACTLY what I have done in another database, where it works perfectly every single time.) In a query I have, amongst others, the following fields: Category SubCategory Company I want to be able to select any OR ALL of the relevant fields. I have the following criteria: Like "*"&[Type Category otherwise leave blank for ALL]&"*" Like "*"&a...

Update Query ?
Is there a way to 1) Assign events when adding in data thru an update query for example if you append a bunch of data into a table could you then do some kind of an update query to say if there is an initial event and no others tag this one Renewal 1 etc.. the only real data you have to go on would be Customer A and the total amt .. the event, renewal event and the delta would all need to be added in example: Say Customer A comes in with an initial deal then Customer A renews their deal So, the data would look like when you're done.: Year Custo...

Query that excludes records-Fetch XML limitation?
I posted about this a few weeks ago, but I got sidetracked for a while. I haven't been able to figure this out yet, and I'm starting to think it may not be possible. I am looking for any and all creative suggestions. TIA. My Sales users need a way of finding leads that have not been contacted since x date. For example, they want a quick way to lookup all leads with no phone call activities between 11/1/2004 and today. I tried c360 SearchPac and no matter how I build a query, it returns all leads with phone calls that fall outside of the dates I have selected, regardless of whet...

tracking inventory on two dbs
I have a web biz and a brick and morter biz. I also have rms running two seperate dbs (1 for each biz). I want to track the inventory on only one DB but be able to sell inventry on both databases. How do we do that? Any seamless way to do that? Thanks Brent HI Brent, seems sort of harder solution, but let me explain you how any system knows that where this particular item belongs to , if you have already criteria develop then you can handle this in your web application may be you can create a field called "flag" which describe the DB server ID, and send data back and f...

pass through query
If a combobox is populated by a pass-through query, does it requery every time the form is closed and reopened? Thanks, Sam Yes. John Spencer Access MVP 2002-2005, 2007-2010 The Hilltop Institute University of Maryland Baltimore County Sam wrote: > If a combobox is populated by a pass-through query, does it requery every > time the form is closed and reopened? > Thanks, > Sam ...

Pivot table fields missing on all computers
I have Excel 2007 installed on two computers. I also have Excel 2010 beta installed on one of the computers. I am unable to create a pivot table on ANY of them. None will show the field list. All copies of Excel work fine in all other aspects. I have searched the net for solutions and found many others have the same problem but few suggestions to fix it. Many questions never got any response at all. I did find a reference to try removing the .xlb file from the XLStart folder. I did, with no results. I have done all the proper show/hide clicks. I even tried using the ...

Help with a partial match query
I asked this question here many, many years ago and have since forgotten the answer. I have a table with address data in it, spread over several fields. One of the fields is called "Zip" and is a text field of 5 characters in length. I am trying to write a query that will prompt the user to enter the first FOUR digits of the zip code and have it return all matches for those four characters. For example, 1234 would return 12345, 12346, and 12347 and so on. My recollection is that the query was something like: SELECT * FROM addresses WHERE Zip LIKE "[Enter...

I cannot delete two orphaned mailboxes.
Windows 2003 Server Exchange 2003 Server I have two orphaned mailboxes that will not allow me to delete. If I run the cleanup agent they will not show as disabled with the red x as all others do. WhenI click on the user I get A referal was returned from the server Facility: LDAP Provider ID no. 8007202b Microsoft Active Directory - Exchange Extension Is there a way to delete this other than system manager? Any thoughts greatly appreciated, MengDe create a temp user and reconnect the mailbox to that user. then delete the user and mailbox "MengDe" <MengDe@discussions.micro...

using access for pivot table source
I would like to use an access query as the source for my pivot table. want to have a parameter (in the access query) that can be changed b the user running the pivot table at run time of the pivot table. th parameter would limit the amount of data returned, ie, dollars ove $100,000 or dollars over $1,000,000. When I try to run the pivot table, i get an error back too fe parameters. expected 1. any thoughts! Thank -- Message posted from http://www.ExcelForum.com You can't create a pivot table from a parameter query. You could programmatically create the query definition as part of th...

combining two queries
I have a query that finds the running sum of Inventory using the dsum function. I have another query that finds the running sum of Accounts Receivable again using the same method. I have a list box on a form showing the data based on the inventory running sum query. I would like this list box to have the AR data in it also. Is this possible? Tom, I'm assuming that what you mean by a running Inventory and running Accounts Recievable is that these queries have a date field, and sum some other field (or combination of fields) for all values before that date, for a particular produ...

Query query
I am trying to create a new field in which it shows the sum total of 10 fields (Charge Time, Charge Time 2, Charge Time 3, etc.); what is the easiest way to do this? -- Joe NewFieldName: [Charge Time] + [Charge Time 2] + [Charge Time 3] .... etc -- Good Luck BS"D "joet5402" wrote: > I am trying to create a new field in which it shows the sum total of 10 > fields (Charge Time, Charge Time 2, Charge Time 3, etc.); what is the easiest > way to do this? > -- > Joe I tried this, and it doesn't work. Is there something I'm forgetting? -- Joe ...

How do I split one cell of 6 numbers into two cells of 3 and 3?
I have a column of data that has six numbers in each cell, however I need to split this into two columns, where the first column is the first three digits and the second column is the last three. There is no separator (space, comma or dash) between the numbers. Any suggestions? Use Text To Columns. Select your column of data, then: <Data> <TextToColumns> Click "Fixed Width", then <Next>, Click in the data to display the "break line", and drag it to separate the numbers as desired. Then <Finish> -- HTH, RD =================================...

Load query with outdata until search
I have a form with sub form on it I use this form to search for products we keep in stock with prices, I have a Search text box on the form then i type in the product then hit the search button and it requerys the sub form Only problem i have is when i first load the form it show every product in the subform, so takes a long time to load Is there a way to load the page with out the date then i can type in product i want to search for then it displays the products Simon What about setting the .RecordSource property of that form (?subform) to "" in the OnLoad event,...

access tables and relationship
i have 3 tables with primary key of Car_registrationNumber in tblstock C_DrivingLicenseNumber in tblcustomer S_ID in tblstaff my linking table which connect all three table in one to many relationship is tblhire my problem is this] in my tblhire i have three fields which are the three primary keys at the top when i click on the C_DrivingLicenseNumber primary key it shows me the customer driving licnse number and first name and surname but when i click on the Car_registrationNumber it only shows me the Car registration Number not the car make and model why to be more clear hav...

location of web query
I have a file that refreshed 2006 data that I am now changing to 2007 but I cannot recall where a web query is on a certain worksheet, is there an easy way to find in which cell the query resides? ...

Marketing list query to list active accounts not already a member
Is there a way to run a query for marketing list membership, in order to get a list of active accounts NOT already on the list? We can't just add all accounts since some may not want to be on the list. Thanks, - Steve Yates - ITS, Inc. - It's not over until the FAT table sings. ~ Taglines by Taglinator - www.srtware.com ~ Hi Steve Not sure what output you need, but if you query in SQL then your query should link the acccount and the listmember tables. You coulddo the samein a report -- Alistair 2B.net http://www.crmdeployment.co.uk "Steve Yates" wrote:...

email attachment(s) from query results
Through a series of forms and queries, my users select a list of pdf files that should be sent via email. This list might contain anywhere from 1 to 20 pdf's, and the actual files and number of files vary with each use. At this point, this list is emailed to someone who manually creates an email with the appropriate attachments. This is not a terribly efficient workflow. Already existing in the database, I have a hyperlink field containing the full path to each of the pdf files. What I'm looking for is some way for access to loop through the final query results, retrieve the...

Merging two worksheets into one to create a pivot table
Hello, I am currently doing a project at work which requires me to take tw Inventory Activity Reports and merge them into one so my manager ca use them to create a pivot table to show a month by month sale breakdown. The common field that needs to be linked is the Product ID I have an April sheet and a May sheet. How do I turn them into on sheet and combine the data without me going line by line and doing so? I simply want to merge the sheets so a pivot table can be created fro the single sheet -- Message posted from http://www.ExcelForum.com If you don't have more than 65536 records...

Another simple query
Dear all, I have a table that contains first names and last names in different fields. Is there any way to write a query that will give as a result the first name and last name in the same field? I hope this makes sense. Thanks in advance! SELECT [last_name_field] & ", " & [first_name_field] AS whole_name FROM [your_name_table] Change the field and table names to whatever your actual fields and tables are. Cheers, Jason Lepack On Apr 23, 9:47 am, Homer <peter.a.r...@googlemail.com> wrote: > Dear all, > > I have a table that contains first names an...

Help build query
I have two tables: Counters:' Every 10 minutes a record is added with updating the counter Data_Hora ID_Contador E_SVazio 23-04-2010 0:00 CTE003 16.528.690 23-04-2010 0:00 CTE002 21.527.520 23-04-2010 0:10 CTE003 16.528.750 23-04-2010 0:10 CTE002 21.527.570 23-04-2010 1:20 CTE003 16.528.800 23-04-2010 1:20 CTE002 21.527.620 23-04-2010 1:30 CTE003 16.528.850 23-04-2010 1:30 CTE002 21.527.670 23-04-2010 2:40 CTE003 16.528.890 23-...

web query query!
Hi All I've developed a worksheet that retrieves external data from a web page (stocks & shares info). At the moment I display the data at a certain cell in the worksheet so other cells in the sheet can calculate values of my shares. As I don't need the all the external data to be viewed (I only need one column of values), is there a way to extract the data I need from the web query without displaying it all in the worksheet? Thanks in advance Dave ...

Basic Update Query
I have the following two tables Invoice-InvoiceProduct and there's a many to one relation between them, I use the following query to work out the total base on all the InvoiceProducts that share the same InvoiceNumber but it returns empty anythoughts? UPDATE Invoice SET Invoice.Total = Sum(InvoiceProduct.SubTotal) WHERE (((Invoice.InvoiceNumber)>0)); InvoiceProduct ========= InvoiceNumber ItmeID Quantity Price SubTotal 10 1 2 $100 $200 <- this update query works fine 11 2 5 $8 ...

Text Box with words splitting onto two lines
I have a text box in publisher with a lots of text but it splits up words onto two lines Eg It is a very sun- ny day today is there a way to stop it so it puting words on 2 lines Thanks S.Dickson@shos.co.uk wrote: > is there a way to stop it so it puting words on 2 lines For the current text box: Tools > Language > Hyphenation For all new text boxes: Tools > Options -- Ed Bennett - MVP Microsoft Publisher http://ed.mvps.org ...

Query too Complex...
words I have seen way too much of today! And if it is not "too compex" then I get a message saying "insufficient resources". I have spent the day trying to break up boards into smaller sections and trying to find other ways to get the same answer. Eventually I have to bring the seperated boards back together again, so the end result is still the same: Query is too complex Does anyone have a suggestion that I could try to get around these frustrating comments? Should I be using subqueries? - would that help simplify things in the FROM clause? Tara Without seeing ...

Append 2 tables with variable names
Hi there... Here's my problem. I have 2 tables in an access 2000 database. The table names are unknown...they could have any name. Through VBA how can I append one table to the other? Hi, you can run append query: currentdb.execute "Insert into " & strTable1 & " (Field1, Field2) Select Field1, Field2 From " & strTable2, dbfailonerror Where strTable1 and strTable2 variables hold tables names -- Best regards, ___________ Alex Dybenko (MVP) http://accessblog.net http://www.PointLtd.com "ambushsinger" <ambushsinger@dis...