Unmatched query wizard??

I have succesfully made a Unmatched query using the wiz.

I changed the query to a delete query, but when I run it, it asks me which 
table do i want to delete the records from.

I keep screaming at it 'what do you mean which table isn't it obvious', but 
that doesn't work.

Can anyone tell me how to overcome this please.

Regards

Geeves1293


0
Utf
3/6/2008 9:47:01 AM
access 16762 articles. 3 followers. Follow

2 Replies
1291 Views

Similar Articles

[PageSpeed] 28

Use a subquery to select the unmatched records

This example deletes records in tblInvoice that have no matching records in 
tblInvoiceDetail:

DELETE FROM tblInvoice
WHERE NOT EXISTS
    (SELECT InvoiceID
    FROM tblInvoiceDetail
    WHERE tblInvoiceDetail.InvoiceID = tblInvoice.InvoiceID);

If subqueries are new, here's an introduction:
    http://allenbrowne.com/subquery-01.html#NotThere

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"geeves1293" <geeves1293@discussions.microsoft.com> wrote in message
news:0B1C6A29-E684-4E08-A73D-4480B7D750EC@microsoft.com...
>I have succesfully made a Unmatched query using the wiz.
>
> I changed the query to a delete query, but when I run it, it asks me which
> table do i want to delete the records from.
>
> I keep screaming at it 'what do you mean which table isn't it obvious', 
> but
> that doesn't work.
>
> Can anyone tell me how to overcome this please.
>
> Regards
>
> Geeves1293 

0
Allen
3/6/2008 10:04:29 AM
Thanks again.  Just managed to sort it.

Stopped screaming at it now.

Geeves1293


"Allen Browne" wrote:

> Use a subquery to select the unmatched records
> 
> This example deletes records in tblInvoice that have no matching records in 
> tblInvoiceDetail:
> 
> DELETE FROM tblInvoice
> WHERE NOT EXISTS
>     (SELECT InvoiceID
>     FROM tblInvoiceDetail
>     WHERE tblInvoiceDetail.InvoiceID = tblInvoice.InvoiceID);
> 
> If subqueries are new, here's an introduction:
>     http://allenbrowne.com/subquery-01.html#NotThere
> 
> -- 
> Allen Browne - Microsoft MVP.  Perth, Western Australia
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
> 
> "geeves1293" <geeves1293@discussions.microsoft.com> wrote in message
> news:0B1C6A29-E684-4E08-A73D-4480B7D750EC@microsoft.com...
> >I have succesfully made a Unmatched query using the wiz.
> >
> > I changed the query to a delete query, but when I run it, it asks me which
> > table do i want to delete the records from.
> >
> > I keep screaming at it 'what do you mean which table isn't it obvious', 
> > but
> > that doesn't work.
> >
> > Can anyone tell me how to overcome this please.
> >
> > Regards
> >
> > Geeves1293 
> 
> 
0
Utf
3/6/2008 4:57:01 PM
Reply:

Similar Artilces:

Query Question 07-07-07
I have a table of nominations that includes the fields NomineeName, NominatorName, and NominationMessage. I did not create the database, but have been asked to create reports from it. My problem is in the NomineeName. Users have been able to imput multiple names into the database for a single value, so that the would Like this: ID NomineeName Message 1 John Smith, Karen Jones Blah1 2 Jack Henderson Blah2 3 Jack Henderson; John Smith; Peggy Sue Blah3 What I need help on is ...

Self Destructing Queries #2
Doesn't anyone else have this problem?? -- baracuda ----------------------------------------------------------------------- baracuda9's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1626 View this thread: http://www.excelforum.com/showthread.php?threadid=27675 ...

Help! Need a Query or something!
I have a few credit cards in my MS Money file. My assistant keeps track of 2 of them. I would like her to be able to enter the credit card transactions into MS Money (at least), but I don't want her to see the private information I have littered throughout the rest of the .mny file.... Is there a way someone can write a VB procedure to push data from excel to money? My best preference would be a query that updates every time I open money, but I know I'm dreaming. I've tried the few products out there that change .xls to .qif, but! The problem is I'd like to have ...

Preventing Errors on calculations where query returns no results
Hi everyone I have a query which depending on the users selection may or may-not return any results. The expression below works fine when results are returned but when there are no records it returns '#Error': =IIf([expirydays]<1,"expired " & Abs([expirydays]) & " days ago") Is there a way to modify it to gracefully fall over (ie be blank) when there are no records? I have tried 'IIf([expirydays] is null,null' and 'IIf(len([expirydays])<1,null' but neither worked. Thanks in advance, John Apologies, the below refers to a c...

Control Excel Formatting From Access Query Export
I think this may be beyond Access's DDE capabilities but thought I'd float this out to see if anyone had an ideas/thoughts or leads/links: I export an (open) Access query to Excel using DoCmd.RunCommand (acCmdOutputToExcel) Once my query data reaches Excel, I want to format a) Page setup - for landscape/fit to page/legal etc b) Column width/Row height c) Wrap cell contents Try using Automation. Here's a excerpt from code I use to format the data after I export it. It doesn't do everything you're asking for, but the rest of what you want is rather simple to add: ...

using Query Based distribution groups between domains
I am trying to use query based DG's for passing world wide notificatons across domains. I have a global QBDG containing QBDG from our 3 child domains. When I am sendin an email to this DG the email only reach the recipients in the domain where the global dg is placed, none of the the recipients in the other child domains gets the email. When I do a preview on the global DG the child domain qbdg is visible so it looks as if it would work. Any ideas appreciated. Sounds like an issue with the expansion server. Have you considered setting the expansion server to try and work through t...

another question on queries
I want to apologize ahead of time for being a novice with MS Access and VBA. I desperately need help with 2 queries that I am trying to put together. I want to thank anyone that can help me out with this situation. I want to put a select query(Query1) that uses one table and the criteria would be [Enter the Event Number]. When an event number is entered it will give me the Event Number, Event Name, Event Date, Event time, and House Size. Next, I need to create a 2nd query(Query2) that pulls up the records from another table by the Event Number. Below are the tables and fields that I am u...

Unmatched Records 01-11-10
Have 2 queries, one is "CustomerNoSales" and the other "CustomerSales", both queries have these 2 fields "CID" (customerID) and "PrdtID" (productID). I need to find out which records from the "PrdtID" field in the "CustomerSales" query that ARE NOT in the "CustomerNoSales" query. Have you tried the Query Wizard? Vanderghast, Access MVP "Mario" <Mario@discussions.microsoft.com> wrote in message news:5C005499-BB50-4002-B7E1-176C0D5EFF18@microsoft.com... > Have 2 queries, one is "Custom...

SPECIAL QUERY
HI ALL! I have a table with following 3 fields: Account_1, Account_2, Amount Any query that would result into following layout? Account (Containing Unique Names From BOTH Account_1, Account_2) Amount_1 (Containing Amounts of Accounts Listed In Account_1) Amount_2 (Containing Amounts of Accounts Listed In Account_2) For instance, consider the following sample: Account_1, Account_2, Amount ====== ======= ===== AccountA AccountB 1000 AccountC AccountA 2000 AccountB AccountC 3000 To be generating a result as follows: Account, Amount_1, Amou...

Summing multiple input records into one output rcd query
I need to produce, from multiple input records, for example Gross - Taxes withheld - Deductions Withheld - Employer Taxes - Net where each input record will contain one of the above amounts identified by an "Amount Type". This will be used to populate an Excel Spreadsheet for all payroll data by Payroll Period. I can summarize into a report by breaking on the employee name, but I really need to have the data into one line. I use a nested iif statement that identifies the Amount Type and outputs it into the appropriate field - if Amount Type is "Vac", i will out...

run a macro via a query
I have a series of queries that are tied together in a macro. Example: The macro first deletes the data in an existing maketable. It then appends new data from a linked table, changing the formatting from Number to text. It then runs a final query that pulls data based on the appended table. I would like to have one query that a person could run that would run the macro. Is this possible? Thanks, Perry -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200704/1 Macros can run queries. Queries (as far as I know) do not run Macros --...

Scheduling wizard fails with parameterized reports
Hi all, we are evaluating the report scheduling wizard and we encounter some issues. After many tests, it appears that the wizard works well with reports using CRM filtering, but fails with reports embedding Reporting Services parameters. The error is not explicit, it just says "an error occured" but I can't find any more information. Unfortunately, I have some very complex reports that requires the parameters to be used in the data source query, and CRM filtering is not the solution. So, how can we make parameterized reports to be successfully scheduled with the wizard ...

Query question 04-05-07
My table: Employee No Work Code No of hours Start Value 2 4.0 2 89 2 2 92 1 2 92 1 2 92 2 Here is my query and the results I am trying to get are: Work Code 89 is Earned Work Code 92 is Taken The start value is 4 – so I would need to add work code 89 and then minus 92 and then the result is 2. How would I set up a query? Thanks, ...

Want to use Group By in union Query
I have made query from 2 different table which works fine : SELECT [Customer], "Address:- " & [Address] & " Telephone:- " & [Telephone] & " Fax.:- " & [Fax] AS CustomerDetails FROM [MachHist] UNION SELECT [CoName], "TeleNo:- " & [TeleNo] & " FaxNo:- " & [FaxNo] & " Email:- " & [Email] & " MobileNo:- " & [MobileNo] & " PostBox:- " & [PostBox] FROM [TeleDir]; But when I try add to Group By at the end , it produces error : SELECT ...

Reusable Database connection for Combo Box, list box and other query
I have an app that is database driven and there are many times where I need to make queries out to the database to populate Combo Boxes and List boxes. In my current code, I have to explicitly set the code to populate the combo box based on the result set. Is there a way where I can get a handle to a result set, pass that to a function that parses the resultset and populates the combo box? I know in Java and other OO languages you have an abstraction to the database where you can pass around objects for manipulation but in Excel VB I'm not sure how one would do this. Any idea? On Jul 22...

Iif, And Expressions in a Query (Working with Dates)
I am trying to create an expression that will look at the 1st Pmt Date Long and if it's <= #1/1/2007# and #1/1/2007# < Last Pmt Date Long, to bring a field called Total Payment Forward. So, if the 1st Pmt Date Long is 3/1/2004 and the Last Pmt Date Long is 10/1/2009, it should be bringing the Total Payment Forward. At least, that is my goal. Below is my expression that I'm using in the field called "Jan 2007" : Jan 2007: IIf([1st Pmt Date Long]<=#1/1/2007# And #1/1/2007# < [Last Pmt Date Long],[Total Payment Forward],0) This only returns all the 1st Pmt...

Parameter criteria in MS Query to bring in all items for a single day
I think this is an easy one! I am querying on a date time field with e.g. 21/12/2003 07:00:00 and just simply looking to filter for all times on a specific date using a parameter query.(so keying 21/12/2003 in the parameter prompt returns all items for that day although the timestamps will be different) I guess I could create a calculated field using INT or similar but I'm sure there is an easier way than that. I don't think that worked anyway because of the data type situation. Presumably using ROUND is not appropriate. Also tried criteria 'between [date1] and [date1]+1' bu...

Query Problem
I have 3 tables, 06-07 projects, 07-09 projects, project construction. All three I set School District Name as the primary key. When I go to relationships, I pop up the three tables and hook them up with School District Name, no problem. When I build a query with two or three of the tables, no matter what I do it acts like I don't have good relationships set because I get 10,000 - 100,000 records which is 9500 - 95,500 more than I need the query to produce and there will be 30-50 copies of the same record of each all the way through. It acts like my relationship is not ok but I am ...

Query?? or something else?
Greetings all, I am new to actually messing around in access and can�t seem to find a solution to my problem. I have two tables one is Employees and the other is company. They are already set up with a one-to-many relationships. The problem I am having is when adding a new employee I am limited to selecting a value that exists in the company table, which can be 1 though 5. Is there a way though query or some other means to show the name rather than the ID for that field? Table outline [Employee] CompanyID FirstName LastName Department Title [Company] CompanyID Comp...

Using the Where clause in an update query
Code below was entered into the "Update To" field while writing a query in design view (Wizard)...As a result the "...operand without and operator..." dialogue box shows up. The intent is to update a number identification field to: "061-" & forms!main!line &"-"& equipmentlistings!numberset where forms!main!equipment = equipmentlistings!equipmenttype &"-"& forms!main!method Equipmenttype and Numberset are from the same table, equipmentlisting, all stored as text. Equipmenttype is chosen from a form. I&#...

Memory leak query
I'm using Excel 2002 and querying excel files using ADO. I have read on the microsoft site that there is a memory leak bug when you query open files, but could not find any fixes for the problem. (Other than their suggestions of effectively forming a duplicate copy of the data). Has anyone come up with a sensible work around for the bug, or found an upgrade (maybe Excel 2003??) that would help me? Thanks. Blue Aardvark wrote: > I'm using Excel 2002 and querying excel files using ADO. I have read on the > microsoft site that there is a memory leak bug when you query open ...

queries using the not command with more than one item
trying to do a query using not "example" or "example" can on ly get this to work with one item, not more than one need to say not abouit 8 items how do i do it? A couple of ways -- <>"Sam" AND <>"Joe" AND <>"Bill" AND <>"Jack" Not In ("RED","WHITE") -- Build a little, test a little. "access query question using not " "" wrote: > trying to do a query using not "example" or "example" > > can on ly ...

Many-to-many relations and associated queries
I have a rather complex situation (at least to me) that I can't seem to solve. I'll try to explain the situation with this simple database design. The situation is caused by an initial many-to-many relationship followed by a one-to-many relationship. Prog Table -- Prog_ID Prog_Name The above is the main table. __________________________________ Link Table -- Link_ID Prog_ID BA_ID LOB_ID Since each entry in the Prog Table can have many BAs and each BA can have many Progs this table had to be created. ___________________________________________________ BA Table -- BA_ID ...

Quantity on Hand filter for Inventory Wizard Worksheets
Posted on behalf of Digital Retail Solutions: We got another complaint today (over 100 now on this particular issue) regarding the fact RMS does not include any Quantity on Hand filter for ANY Inventory Wizard Worksheets. This wastes countless hours (all year long) sifting thru items you don't even have in stock. Absolutely maddening to end-users. I have sent this suggestion to MBS numerous times. Ironically the fix to this is so simple it's dumbfounding why it hasn't crept into a hotfix during the last 2 years (after I first suggested it). I haven't seen 1.3 yet but I...

Need help with chart graph not on chart wizard
Need help in creating chart graph. For example, we have tarped and untarped data we need to track. I want to create a chart for 9 landfill sites and show the tarped and untarped percentages on the same bar graph. I am comparing October and November data. I am using excel 2003. Does anyone know if this can be done? The chart wizards has something similar, but not showing the data the way we want to present it. Thanks! Neither of your posts described what you want to show, or how you want to show it. I'm sure what you want can be done, but I don't know yet what that is. -...