Queries :: Can't Delete Record In Unmatched Query
Jan 12, 2014
Access 2010
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.
View Replies
ADVERTISEMENT
Oct 25, 2014
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.
View 3 Replies
View Related
Jun 19, 2015
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.
View 8 Replies
View Related
Jan 12, 2006
Hello,
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));
Please help!
thanks,
Pablo:confused:
View 3 Replies
View Related
Aug 13, 2013
I am needing to compare some information in a way I cant with the normal Unmatched Query.
I have 2 tables with the fields:
| PHONE1 | PHONE2 | FIRST | LAST |
What I need to do is have Table 2 check against Table 1 to see if any of the 'PHONE1' records match 'PHONE1' or 'PHONE2,' and if they do have them remove just the information from PHONE1, not the entire record.
Then I would have to repeat the process to do this with PHONE2. Having it check against 'PHONE1' and 'PHONE2' in Table1.
If both PHONE1 and PHONE2 have a match, the entire record would be deleted. If not, it would keep whichever did not match.
I am currently having to do this manually, because if I just run an Unmatched Query it gets rid of the whole record regardless of if PHONE2 is a good number or not.
If any more information is needed, let me know. Like I said, Im relatively new to working in Access and dont know much.
All of the fields are 'TEXT' type.
Access 2003 on Windows XP.
View 8 Replies
View Related
Oct 22, 2013
I need to design an unmatched query on 2 tables to identify employees names that exist in 1 table but not the other (or names are misspelled, etc.). The tables exist in on a server and I do not have the ability to change either. Table 1 has the following structure:
EMPLOYEE NAME: Doe, John ID-12345
Table 2 has the following structure:
LAST NAME: Doe
FIRST NAME: John
Since the field names are not equal, the unmatched query wizard (as far as I know) will not work. I've tried using "NOT LIKE", LEFT JOINS, RIGHT JOINS, NUll Values, etc. in the SQL but nothing has worked as yet.Here's the SQL I've been working with but this returns all values (haven't figured out why):
SELECT DISTINCT [Table 1].[EMPLOYEE NAME]
FROM [Table 1], Table 2
WHERE ((([Table 1].[EMPLOYEE NAME]) Not Like "([Table 2].[LAST NAME]*"));
View 2 Replies
View Related
Apr 19, 2013
I am trying to create a query that matches two other querys and finds records that are in the primary, but not the secondary query. I have tried to use the query wizard for this, but it will not work for me the way I want.
If I have A,B,C,D in one query and only have A,B,D in the other I want to find C, so that I can format an email funtion in my database using that data.
View 1 Replies
View Related
Mar 24, 2013
I am trying to do the good 'ol sales report (query) to include customers with no sales.
I have a customers table, account number table, sales table & sales (line) detail table. (all linked in that order)
If I run a query to show customers (in the customer table) with account numbers, that works
An unmatched query to show customer without an account number works (but of course the unmatched account number field isn't shown).
How can I get the two two be shown together with the "unmatched" having a null or 0 for their account number?
I am guessing in principle, the resulting solution can be modified to show customers without sales alongside those with sales?
View 3 Replies
View Related
Jun 19, 2015
I need constructing a find unmatched query. I don't really know what I'm doing with SQL and I think that I can only take things so far with query design view.
I have two tables. T_Productions and ProPro (see attached). In Access query design, I can only specify one matching criteria. I need a query that will show me records from T_Productions that don't have matching Production_Code (PpNum in ProPro), Avantage_Product_Code (PpPrNum in ProPro) and Step (PpEtape in ProPro) in ProPro.
What I have now only gives me the records where Avantage_Product_Code don't match PpPrNum.
Code:
SELECT T_Productions.ID, T_Productions.Production_Code, T_Productions.Avantage_Product_Code, T_Productions.Step, T_Productions.Quantity_Required
FROM T_Productions LEFT JOIN ProPro ON T_Productions.[Avantage_Product_Code] = ProPro.[PpPrNum]
WHERE (((ProPro.PpPrNum) Is Null));
View 4 Replies
View Related
Apr 15, 2013
I created 2 combobox that list specific table in a data base. I want to create an unmatched query based on the selection from those two combobox.
All field in those table are the same.
View 3 Replies
View Related
May 19, 2011
I have two simple tables. I want to delete the records from Table1 that are on Table2. I've created a select query that gives me what I want but when I change to a delete query, I get this message: "Unable to delete from specified tables"
I think my problem has something to do with security but I can't figure out what to change.
View 13 Replies
View Related
Jul 23, 2015
I have table1 that I append data to. The unique_ID is the rpt_date.
On form1 (that shows the table1 data) I want to delete the Max rpt_date from the table1.
I have tried to create a query involving MaxOfrpt_date linked to the table1 by the rpt_date and then create a delete query but it doesn't work !!
View 1 Replies
View Related
Aug 20, 2015
I have a cancelled service that gives a -1 to an ISCANNED field for each EventDate after the date the services are cancelled. Now I am permitted to Delete all of the cancelled records except the Min() date that =-1.
I built a query to find the value for the First Event date that =-1. I then built a delete query to delete all records after the First Event date that =-1 but it had to Join the "Find the value of the First query" to get the table records to show the records that were going to be deleted.It cannot delete.
Is there a module or VB that can be used to make the delete query work without using the joined "Find the value of the First query"?Something that will say "delete all the records after the first cancelled record"
View 7 Replies
View Related
Apr 11, 2013
Is there anyway to join 15 queries into 1 (all have the same data from a different source but need merged into 1 large query) rather than doing 12 unmatched queries?
Let's say I have 5 records in the first query, 5 records in the 2nd, and 3 records in the third - I want to end up with 1 query that has 13 records.
View 4 Replies
View Related
Feb 15, 2008
i have a table1 in my msaccess and i linked another table in mysql to ms access, (table2).
when table1 matches the record in table2, it will delete the record in table1.
my query is
delete *
FROM Table1
where table1.artid = table2.artid;
but it always ask me the value of tbl2.artid, the value of tbl2.artid is in the database of mysql and it has already records...
how can i compare the records of table1 (msaccess) and table2 (mysql) and delete the duplicate records?
:D
thanks in advance
View 11 Replies
View Related
Dec 26, 2013
I want to find unmatched records, where there is a condition. Specifically, which employees did not get a specific mail.
My employees are in this table:
tblEmployees
---------------
EmployeeID (PK)
FullName (text)
and sent mails are logged in tblMailsSent
tblMailsSent
--------
MailID (FK)
EmployeeID (FK)
so I want to find all the employees in tblEmployees whose EmployeeID may or may not be in tblMailsSent with some other MailID's but definitely not with the specific one I am looking for.
I find my own method for doing this rather clumsy, so do you happen to have a nice recipe?
View 3 Replies
View Related
Sep 1, 2005
Hi,
I have table tblSample (IdNo, SCode, StaffNo) where IdNo is AutoNumber. This is sample of data,
IdNo SCode StaffNo
10 ABC 50424
11 DEF 50424
12 GHI 50424
21 ABC 50424
22 DEF 50424
23 GHI 50424
26 ABC 50424
27 DEF 50424
28 GHI 50424
Is it possible for me to delete records from IdNo 21-23 and IdNo from IdNo 26-28. Any idea? Thanx in advance..;)
View 1 Replies
View Related
Feb 12, 2007
Hi,
Please, anyone know how to delete few table's record by using one query ?
I want to delete 10 tables, all the records inside at one times. I tried there is only one tables at a time. Is that imposible to do that?
shinyo
View 1 Replies
View Related
Jan 13, 2014
When this query runs I want the popup parameter to open and ask for an ChID number. User enters that number and it deletes that record. I cant figure out what to enter on the criteria line. I have [ChID] and that wipes all records. I tried = [ChID] and it does not run.
View 4 Replies
View Related
Oct 9, 2006
I have two tables. PreOrder and Order Tables. When our customer actually order a product, I want all information of that product to move from PreOrder table and append it to Order table. Currently, my user has to run an append query to add the record from PreOrder and add it to Order table. After that, she has to run a delete query to delete the record from PreOrder table. Since our query is setup to run by date, and order number..My user has to type the same thing twice. Is there anyone out there know the easy way? Thanks in advance..
View 1 Replies
View Related
Nov 20, 2013
I am wondering if there is a quicker way to export a query to excel then have the data in that query removed from the original table. (effectively cutting the data from the table and exporting to excel)
I understand that this can be done by exporting the query to excel then running the same query as a delete query to remove the data but I just wondered if this is the most efficient way.
I have experience of VB in excel but currently only use the basic macro builder in Access though if Access VB is more efficient I can easily learn.
View 5 Replies
View Related
Aug 18, 2014
Okay I have 2 tables and I used queries to filter and delete the info off those tables. Now I combined the 2 tables by using a query which is query 1. What I found was a few cells that are blank cells.
I tried creating another query based on query 1 from the 2 tables to try and delete it but it won't work. I tried everything and nothing seems to work. I can't filter the data since I am creating another query called query 3 to display the final results.
View 2 Replies
View Related
Jan 29, 2015
I have a delete query where i want to delete only the row that contains the max value of the IDnum field from the table STM, where it links two tables on CellTell
I currently have this:
DELETE DISTINCTROW STM.*, STM.IDNum
FROM dpl_00c_tbl_StmCellDups
INNER JOIN STM ON dpl_00c_tbl_StmCellDups.STM_CellTel = STM.CellTel
WHERE (((STM.IDNum)=(select max(IDNum) from `STM`)));
It doesn't want to throw out any values when i run it, or view it.
View 2 Replies
View Related
Jul 26, 2005
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?
View 2 Replies
View Related
Sep 4, 2005
Hi,
I can create unmatch quiries using tables but i don't seem to be able to do it using quiries.
I want to look at the values in on Query1 and compare it with Query2 and return the results that don't have a match.
Is there somthing different i need to do when using quiries instead of tables.
Regards
Pete
View 2 Replies
View Related
Oct 22, 2006
Hi!
Please find attached database.
I have created a query to find unmatched data.
Where I'm doing wrong?
S_log = 301 & T_log = 292 record.
Unmatched should be 9 record
I'm getting 301.?????
Can any one help me in this.
Regards,
Terry.:confused:
View 3 Replies
View Related
