I have an unmatched query that I would like to convert to a delete query. I would like to be able to delete the unmatched records from two tables using one query. I tried something like this (below), but I get an error message stating "could not delete from specified tables". What am I missing?
DELETE PUBLICIST and STORYPERSON STEPS Old table.*
FROM [PUBLICIST and STORYPERSON STEPS Old table] LEFT JOIN [STEP TABLE] ON
[PUBLICIST and STORYPERSON STEPS Old table].Pernr = [STEP TABLE].Pernr
WHERE ((([STEP TABLE].Pernr) Is Null));
I have a Unmatched query that displays what I need but I want to clean up my database and remove them but its not letting me. See sql below.
Code: DELETE Warranty FROM Warranty LEFT JOIN Serials_Repair ON Warranty.[Serial] = Serials_Repair.[Serials_Repair] WHERE (((Serials_Repair.Serials_Repair) Is Null));
I changed "Delete" it was "Select" thinking that would work but it does not.
I created 3 find unmatched queries from the query wizard. The first one works great. I can delete a record in the query and it deletes the record in the main table.
The second one allows me to delete a record and gives me the pop up box that says " you are about to delete 1 record". I click yes, but when I check the table or reopen the query the same record reappears.
The third query does not even give me the option to delete.
I can create a regular select query and delete the records form 2nd and 3rd but not from the queries made from the unmatched wizard.
I am using the same fields in all 3 queries.
I do not have enforce referential integrity or the other 2 options checked in the relationship properties. (not sure if this makes a difference.)
I even tried to redo the unmatched queries but I get the same result for all 3.
I want to be able to delete the records in the other 2 queries.
I was able to create a query that selects records from one table that have several fields which don't match with another table.
The end-game with this query is to have it delete the records in table 1 that don't match records in table 2. The delete query tells me that it could not delete from specified tables.
I've attached the select query, the delete query and the error, and the relationship table for the db.
I have a query pulling data from several tables. In the middle I've got join between a part number in a BOM table and a part number in an inventory table.
What I want is the query to return the inventory location when we have stock. When we have nothing in, then there would be no entry in the inventory table and it should return a blank or null in this and all the proceeding tables.
Sorry, I really couldn't think of a solid title for this. I'll try and explain and provide some code as well.
I've got a series of tables, one of which is linked to a Mobile Device. I have a query (herein called Query A) that queries a masterfile, named MasterFileEdit, based on selections the user makes on the front end. Query A then returns the results, sorted by section number. Query B is set up to query based on the same selections, but instead DELETE all of those records from the masterfile. Doing this makes sure that no duplicate work is performed.
Here's the code for Query A. INSERT INTO [TABLE A1] ( OID, SystemLoc, SystemPN, Location, Part_Number, Qty, Date_n_Time, NL, RCHKL, RCHKP, oddeven, Auditor, upperlower, area, aisle, [section] ) SELECT TOP 100 MasterFileEdit.OID, MasterFileEdit.SystemLoc, MasterFileEdit.SystemPN, MasterFileEdit.Location, MasterFileEdit.Part_Number, MasterFileEdit.Qty, MasterFileEdit.Date_n_Time, MasterFileEdit.NL, MasterFileEdit.RCHKL, MasterFileEdit.RCHKP, MasterFileEdit.oddeven, MasterFileEdit.Auditor, MasterFileEdit.upperlower, MasterFileEdit.area, MasterFileEdit.aisle, MasterFileEdit.section FROM MasterFileEdit WHERE (((MasterFileEdit.oddeven) Like "*" & [Forms]![LocationAudit]![oddeven] & "*") AND ((MasterFileEdit.upperlower) Like "*" & [Forms]![LocationAudit]![upperlower]) AND ((MasterFileEdit.area) Like "*" & [Forms]![LocationAudit]![area]) AND ((MasterFileEdit.aisle) Like "*" & [Forms]![LocationAudit]![aisle])) ORDER BY MasterFileEdit.section;
Everything after the 'FROM MasterFileEdit' after is the user selections that come from a form.
I need help writing the DELETE query. Here's what I've got thus far:
DELETE MasterFileEdit.OID, MasterFileEdit.SystemLoc, MasterFileEdit.SystemPN, MasterFileEdit.Location, MasterFileEdit.Part_Number, MasterFileEdit.Qty, MasterFileEdit.Date_n_Time, MasterFileEdit.NL, MasterFileEdit.RCHKL, MasterFileEdit.RCHKP, MasterFileEdit.oddeven, MasterFileEdit.Auditor, MasterFileEdit.upperlower, MasterFileEdit.area, MasterFileEdit.aisle, MasterFileEdit.section FROM MasterFileEdit WHERE (((MasterFileEdit.oddeven) Like "*" & [Forms]![LocationAudit]![oddeven] & "*") AND ((MasterFileEdit.upperlower) Like "*" & [Forms]![LocationAudit]![upperlower]) AND ((MasterFileEdit.area) Like "*" & [Forms]![LocationAudit]![area]) AND ((MasterFileEdit.aisle) Like "*" & [Forms]![LocationAudit]![aisle]));
I can't get the DELETE to sort. The query runs, but doesn't select the records it should. In fact, it doesn't select any of the correct records.
Thoughts: does the TOP 100 modifier work with Deletes? Is this query even possible?
I know this probably has been asked a 1000 times but I don't seem to be able to edit the uncommented VBA-codes I found online to work on my database.
I already ran a query that shows me the duplicate records of my original table. Now I m looking for a way to
a) delete all duplicate records (not just specific ones by using a criteria) and b) unite the unique records (result of a)) with the original ones that were not displayed with the "find-duplicates-query" ....
I created an unmatched query with the wizard and it works fine - when I want to compare with only one field. Is there anyway possible for it to compare two fields? I need for it to look at field1 and field2 when checking for unmatched records. Any ideas?
I'd like to create a query that shows me what employees haven't had a certain training (i.e. Bob hasn't had heavy machinery training). I've played with the unmatched records wizard and it just seems to not work out no matter how I connect them. I think the only two tables I need be concerned with are the 1st and 2nd table. Can anybody give me an idea on how to work this?
I have a query in an Access 2003 database that list fields from two tables. I want a query based on this query to pull out records where the interest rate does not match the PrimePlusMargin. See code below:
SELECT [95_1324].ACCOUNT, [95_1324].[CLIENT'S NAME], [95_1324].[OPEN DATE], [AR PlanDefinition].[ARL Plan], [95_1324].[Interest Rate], [AR PlanDefinition].Margin, 6 AS Prime, [Prime]+[Margin] AS PrimePlusMargin FROM 95_1324 INNER JOIN [AR PlanDefinition] ON [95_1324].[ARL Plan] = [AR PlanDefinition].[ARL Plan] ORDER BY [95_1324].[OPEN DATE] DESC;
I think I figured it out but it doesnt work completely. I created another field called "No Match" and set the criteria to "No Match" See code:
When I run the code, the pop up parameter asking for PrimePlusMargin. I know I spelled it correctly. If I just say ok, it returns all records. PrimePlusMargin is an alias field. Does this matter?
In table 1 I have some document #s e.g. 320 321 322 170 171 172 151 152
In table 2 I have some document #s e.g. 170 171 151
I would like to create a query, that will look at the document #s starting with 17* and 15* in table 1 and look for it in table 2, if the document #s do not exist, I would like the query to spill out the following:
I have a a table that changes monthly, and each month I need to find the records from the old table that are not in the new table and visa versa. I made the entire row in the table the key. and wrote two find unmatched queries. However, the queries are not finding all the information that is no longer there or that has been added. Can someone please take a look and see what I am doing wrong? Any help appriciated!! Thanks
tbl_returns ID NAME RETURN 123 Store A 9999 123 Store A 8888 789 Store C 9999
I want to find the stores that exist in the stores table, but not in the returns table WHERE the RETURN is equal to 9999. This would hopefully return 456 Store B
My problem is when I put a condition in my query it returns nothing as the condition is based on the left joined table.
SQL SELECT tbl_stores.store, tbl_stores.name FROM tbl_stores LEFT JOIN tbl_returns ON tbl_stores.store = tbl_returns.store WHERE tbl_returns.store Is Null AND tbl_returns.survey_id=2 GROUP BY tbl_stores.store, tbl_stores.name, tbl_returns.store
Hello all, I have 2 tables. I wish to fetch all the records which are not common in two tables.
I do not have Exp with the Unmatched query wizard. I want to sleect the records based on more than one criteria and wizards allows to select on only one field.
Hello Forum -- I have written a query that uses one table and one query. As you would expect, it returns records where there is a match. I want to be able to return all the records that match the records in the table AS WELL as the records from the table that have NO match in order to come up with the sums that are zero
As a word of explanation, this DB is for budgetary purposes. I need to generate reports that include sums of actual expenses as well as $0.00 when no actual expenses have yet occured. But, I'm not at all clear how to write the underlying query so as to get both.
Any help the forum can provide would be fantastic.
I have a table listing 147 Company training Policy# with each Position to which each policy applies. Not all Policy# applies to every position, i.e. Driver versus Office staff. There are 32 employees in 7 different positions.
I have a table of Employee Records with standard information including Employee ID and Position.
I created a select query from the Employee Record and the above training requirements that provides a data list of every employee to each pertinent training policy by inner joining Positions with three columns Policy# and Title from the Policy# table and Employee ID from the Employee Record table.
I am trying to create a query that will tell me by Policy# what Employee has no record of being initially trained in that particular Policy #. I have built a query but it doesn’t give me a complete listing of missing employees.
SELECT qryPolicyByEmployeeID.CWSPolicy, qryPolicyByEmployeeID.EmpID, qryPolicyByEmployeeID.Title FROM qryPolicyByEmployeeID LEFT JOIN TrainingLog ON qryPolicyByEmployeeID.EmpID = TrainingLog.EmpID WHERE (((TrainingLog.EmpID) Is Null)) ORDER BY qryPolicyByEmployeeID.CWSPolicy, qryPolicyByEmployeeID.EmpID;
My problem occurs at the “Where” statement. When inquiring on a specific Policy# and the EmpID is in the training log for any other Policy# that EmpID does not appear in the listing. I am trying to figure a way to get around this.
Has anyone a suggestion as to how I can make that happen? I am very flexible about changing table layouts if that will get me what I need.
This is my first post on this forum so I was wondering whether you guys could help me.
I import data from a server daliy. My DB has three tables containing print files;
Print: shows leaflets that have been printed Package: shows leaflets that have been packaged Dispatch: shows leaflets that have been dispatched
Since data can only be imported at the end of the business day, I needed to create a query which will show the files that have printed but havent been packaged. To do this I created an unmatched query and tested it, it works fine.
This unmatched query has now been placed in a sub form, I would like the users to add comments next to the rows in the queries. However since the unmatched query cannot be updated I was wondering if there is any way of around it
I have two tables with 'BatchNo' in common. However, there are some batches on Table1 where the sum of the 'Paid' amount does not equal the sum of the 'Paid' amount on Table2. I tried using the unmatched query wizard to find these records, but because it may find the batch number on Table2, it returns nothing.
How do I say look for the 'BatchNo' AND the 'Paid' amount and return the ones that are not equal?
Hi all, im hoping someone can help. This is the problem.
I have 3 tables that have tax payer information for 1 year broken up into 4 months, 1 table for each 4 month chunk. all 3 tables mostly have the same taxpayers, but each table has payers that are not in 1 or both of the other tables. each table has a 2 part primary key, the business number is the first part, and branch number for businesses with chains. the next 4 fields are each month of taxes paid. heres the design of each table.
is there an easy way to merge all of this data into 1 table easily. ive tried using make table or append queries, but it only addes the information where the IDs are in each table. this leaves out the ids that are in only 1 or 2 tables.
if you need me to clarify or explain anything else, please let me know. i can post screenies or the design, but obviously, not the data.
I thought this would be relatively simple task to complete but its proving tricky.
I have two tables with the same data: E1 and E2
E1 has two columns and is as follows
A 1 B 2 C 3 D 4
E2 is similar except for one record '5'
A 1 B 2 C 5 D 4
Now the task is to show those records that do not match...in this instance
E1.Field1 E1.Field2 E2.Field2 C 3 5
Now I've used the find unmatched wizard and this is the SQL: SELECT E1.Field1, E1.Field2, E2.Field2 FROM E1 LEFT JOIN E2 ON E1.Field2 = E2.Field2 WHERE (((E2.Field2) Is Null));
this is giving me partially the right answer as in E1.Field1 E1.Field2 E2.Field2 C 3
My task is to compare our master membership table with a second table, removing matching names in the second table from the master table. (The second table are those individuals who chose not to receive a paper newsletter, so we want to save money and trees :) and not send them one.)
So to remove those names I tried the find unmatched query wizard but that only allows me to chose on one field it seems. There is no single unique identified field that would do this for both tables.
I thought if I built an unmatched query using the fields 'Last_Name' and 'Address1' and 'PostalCode' that should be sufficient for the task.
Here are portions the individual SQL statements that I need to get into one query.
FROM tblAllMembers LEFT JOIN qrySeekbyGroup ON tblAllMembers.Last_Name = qrySeekbyGroup.[Last Name] WHERE (((qrySeekbyGroup.[Last Name]) Is Null));
FROM tblAllMembers LEFT JOIN qrySeekbyGroup ON [tblAllMembers].[Address1] = [qrySeekbyGroup].[Mailing Address Line 1] WHERE ([qrySeekbyGroup].[Mailing Address Line 1] Is Null);
FROM tblAllMembers LEFT JOIN qrySeekbyGroup ON [tblAllMembers].[Postal_Code] = [qrySeekbyGroup].[Postal Code] WHERE ([qrySeekbyGroup].[Postal Code] Is Null);
How is the SQL written so the find unmatched query will use all three of these sections in on query.
