Queries :: Delete Query Where Code And Client Are Equal
Aug 8, 2013
I am trying to delete a record in tblinclude where record from tblexclude are equal to clientid and codeid
Here is the sql
DELETE tblinclude.ClientID
FROM tblexclude INNER JOIN tblinclude ON (tblexclude.ClientID = tblinclude.ClientID) AND (tblexclude.CodeID = tblinclude.CodeID)
WHERE (((tblinclude.ClientID)=1));
I get the error Specify the table containing the records you want to delete. I've searched for this but I am just not getting it today.
I have a table called "EquipmentRequired" which is populated by 4 append queries,
5000BaseReq, 6000BaseReq, 6000IFBBReq, EquipmentReq which get some of their information by counting fields in another table but all have the same field names.
The queries contain all data that is initially used to append new records to the table and this works fine.Unless some information changes or a record is added then I would like to add a button to a form and call it "update equipment" behind which would run a vba code firstly to delete all the records in "EquipmentRequired" table then run the 4 queries without the warnings and re-populate the table.
same as the title goes, why i can't query those figures greater than 15% only? How am i suppose to query them right?.Here is my database you cant check query1 and query 2. I am using Ms Access 2010.
I am trying to run a query where one field has a Not Equal parameter AND another field has Not Equal parameter (for example, Product Code Not Equal "A" AND Client State Not Equal "NY)
The result of the above query are all records that have Product Code = "A" are missing from the result. I want some of the "A" Product Codes; I just want to exclude the ones where the Client state is NY.
The list of values for each field is too big to state it in a positive way (I would have to list all the other 28 product codes and all the other 49 states).
What am I doing wrong? Thank you in advance for any suggestions.
Brand new on here and desparate for some help and guidence.
So far with Access I have just used it as a store of addresses to mailshot prospective clients.
However, I now need a more complex database and this is where you might be able to help.
First things first, most of my clients are in universities. This means that I can be used by more than one person in more than one department at a university.
Does this mean I need to do three tables:
1/ "University Details" which gives the address details 2/ "Department" storing the departments of the unis we work for 3/ "Client" Name of the client(s) in that department.
I have a database with with 100s of values for a field. What I would like to be do is specify a value via a form and the query will return all options that equal the specified value.
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.
1. Client Info table which contains the following fields: Clientid LastName FirstName
2.DatesDistribution table Clientid DateAttended
The relationship is one to many for these tables.I have been trying to create a query that will show the following:
Clientid LastName FirstName DateAttended (where this field only shows the most current date that the client visited)
Example: ClientInfo has 2 records:
Clientid=jsmith LastName=Smith FirstName=Jim
Clientid=tjones LastName=Jones FirstName=Tom
[code]...
I have tried grouping the records in Client Info table to get distinct names and using a Last function to get the most current date with little success.
I have a large table with many fields and many rows. There is no primary key. I'll call one field ParentPN, and another field ChildPN. There are many other fields as well. I want to identify all rows where BOTH the ParentPN and ChildPN occur more than once. I know how to create a query to identify duplicates of ONE field in the table, but not two. I can solve this with VBA: I will read the two fields of interest in the first row, then compare both values with every other row. If it finds another row with BOTH ParentPN and ChildPN identical with the first, that's a "hit". Then, repeat with all the other rows. I could find ways to make this run faster, but I was wondering if there are any build in functions to accomplish this. I looked at the Find Duplicates query builder, and all I see is I can select ONE field to search for dupes, not two.
I have two date columns in my table called "End date" and "Closing date".
An example could be 14-06-2015 and 13-04-2017.
I need to make a query which is checking if the two dates are equal to the last day of their respective month. I don't have two columns in the table with the last day of month, so I first need to find out what the last day in the month is.
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.
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.
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.
I am working on an Insurance DB.. I have two tables tbl_Clients and tbl_PolicyDetails with a One-Many relationship.. tbl_Clients have the Clients basic info, Name, Tel, Address etc. The tbl_PolicyDetails have the details of the policy for the customer like Start date, policy term, amount they pay, policy status etc. Policy Status can be any of the following Quote, Active, Suspended, Lapsed, Cancelled, Terminated..
One client can have many Policies.. When one policy finishes (like when cover upgraded/downgraded/any changed their insured property address etc.) we categorise them as Termination of old policy thus the client will have a New policy.. At any stage we only need the newest policy per client. My problem now is I need to list all Clients with policy status Active, Suspended, Lapsed, Cancelled..
Code: SELECT tbl_Clients.cli_ID, tbl_Clients.firstName, tbl_Clients.address, tbl_PolicyDetails.policyCover FROM tbl_Clients INNER JOIN tbl_PolicyDetails ON tbl_Clients.cli_ID = tbl_PolicyDetails.cliID_FK WHERE tbl_PolicyDetails.policyStatus = "Active" Or tbl_PolicyDetails.policyStatus = "Suspended" Or tbl_PolicyDetails.policyStatus = "Lapsed" Or tbl_PolicyDetails.policyStatus = "Cancelled";
Code: cli_ID firstName address 1 Paul Bournemouth 2 Mark Bristol 3 Lauren London 4 Caryn Devon
[code]...
When a Client wishes to Cancel a policy it is not Terminated, the policy status is just Cancelled.. Similar to Lapsed policy, if the policy hits 30 days of no pay then it is passed on to a Debt collection agency.. If in future the Client comes back a new policy is generated for both cases.. So in this case Client Lauren and Caryn will have been repeated twice, in the report I am trying to generate (based on the above query).. I do not wish to display the Cancelled policy if the customer has another policy which again can be Active, Suspended, Lapsed, Cancelled..
Code: cli_ID firstName address policyCover policyStatus 1 Paul Bournemouth PDB Active 2 Mark Bristol PDB Active 3 Lauren London PDBCH Suspended 4 Caryn Devon PDE Active
If I filter the date, e.g., from April 1st to 30th, I want to get as a result 4 records, one of each client and only the latest record from CLIENT 3 (04/22).
If I filter the date, e.g., from April 1st to 20th, I want to get as a result only the records of clients 4 and 3 from 04/16 (as every other contact is from beyond 04/20).
If I filter the date, e.g., from April 25th to 30th, the return must be only the top 2 records (the only ones after April 25th).
I tried using a query grouping the totals on "process key" and the other cells set as "first", but if I filter from April 1st to 20th, for example, no records from CLIENT 3 gets shown, since Access processed the "first" record request before the date filter, and therefore only considered the contact from 04/22, and as it is later than 04/20, wasn't shown on the query.
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 am trying to create a query to find duplicates and delete the duplicates. The result will eventually be used in another query (append query) to update a table.I have a table with 4 columns lets say for simplicity they are A, B, C, D
I want my query to find duplicates within B and deleting them. The catch is before deleting them I need to look into column A to ebsure they are actually duplicates. Example below
Example A B John Doe Tires John Doe Wipers Allison Doe Tires Allison Doe Tires
As you can see from the above Tires is a duplicate and need to be deleted.
A B John Doe Tires John Doe Wipers Allison Doe Tires
Table1 has a record of unique tasks. Table2 has a list of people to do those tasks
They are linked by a Unique_ID (one to many - Table1 to Table2).Sometimes there can be an instance where there are Unique_ID's in Table2 but the reference Unique_ID is missing from Table1.
I have a select query that can show me these like so.
table2 table1 Unique_ID Unique_ID Like * Not Like *
I can open the query and simply delete the records fine however i wish to automate this.What I want to do is delete the unrefenced Unique_ID's from table2.I have tried to just convert this select query into a delete query but it asks me which table I wish to delete and I cannot see where to enter this information.
I have a Query which can pull data to a form, then the form will evaluate the ID, after saving/recording, I need to delete the evaluated data on the query.
My problem is how can i delete it? There is Table1 for outputing the evaluation, Query1 where the evaluation can get the Data.
Process Flow: > Form pulls data from Query1 > Evaluation was saved on Table1 > Evaluated ID should also be removed on Query1
I have a query, and when I execute it, the correct data set is returned. However one field shows "DELETE" as the value for all records.
when I ran it on the SQL server management studio as a test it ran fine. End user does not have access to the tables, so I created a small MS Access application. When I ran the query in MS Access, then connected table dbo_DocRef > Notes shows a value of DELETED.
I have this code on the a Close Button and if the record is incomplete I want it to be deleted from the table. The ID field is an autonumber so it is the 1st field to get a value if a user starts entering data. I get a msg box saying 0 fileds will be deleted why? the id's do match but it does not delete??
