Multiple Join Delete Query
Nov 16, 2014I am trying to delete all records on [TableA] that Match [TableB] at Columns [SKU] and [ASIN].
View Replies
ADVERTISEMENT
Delete Query Inner Join
Dec 20, 2007Hi,I am very bad in queries and need your help on this one.I am trying to run a simple delete query having an inner join but it gives an error every time. The error being "Specify the table containing the records you want to delete"here are the details of the simulated table and the query than I am trying to run table1 : name number abc 1 xyz 2 table2 name cityabc AQuery that I am trying to run is:Delete Table1 from Table1 INNER JOIN Table2 on Table1.name = Table2.name;Thanks
View 2 Replies View RelatedDelete Query With Join
Jan 11, 2007Hi, i am having some problems with a delete query,
This is because i am using 2 tables in the query
TBL_12a_Requisition_Sub
and
TBL_12_Purchase_Requisition_Sub
The 2 tables are linked by fields both named [Requistion Number]
Basically where the 2 tables match i would like to delete all records in TBL_12_Purchase_Requisition_Sub. The query works fine when in design view but when running it you get the error message (specify the table containing the records you want to delete). The problem with this is i have already done this in the query.
I have looked over the net and it looks like you need to do something with a select statement in sql, but i can't figure it out. Any help would be much appreciated. Thanks.
Query - JOIN Multiple Tables
Jun 11, 2006Hello All;
Could someone please assist me on this?
The following is the Tables:
ContentTypes ---> Site Content Directory
Cats ---> Categories
Sierra ---> Store Content for Sierra
BrushStrokes ---> Store Content for BS
I have "tried" to create a QUERY for the 4 Tables
(The code that I have, originally came with 3-Table Query connection
I have added in the 4th Table "BrushStrokes")
This is the Query that I created. Before the code, a little insite
At first there was: RIGHT & LEFT JOIN's, But I would receive this:
Error 3258
After not really finding any good examples of how to create the
2- Queries and have then work together as one.
I decided to try something else, which was to create all [INNER JOIN]'s
Which got rid of the Error, and the code still will not work in my site.
========
Microsoft JET Database Engine error '80040e14'
Syntax error in JOIN operation.
=======
So, here is the code, please let me know what is wrong with it?
And if possible, a good example of creating the [2 - Queries] that work as one.
Thank you all
Carrzkiss
-----------------------SQL QUERY------------------
SELECT Cats.CatID, Sierra.CatID, BrushStrokes.CatID, Sierra.ContentID, BrushStrokes.BSContentID, Sierra.Product_Title, BrushStrokes.BSProduct_Name, Cats.CatDescription, Cats.CatTypeId, Sierra.Product_Th_Image, BrushStrokes.BSThumbnail, Sierra.Our_Price, BrushStrokes.BSPrice, Sierra.Retail_Price, BrushStrokes.BSRetail, Sierra.Aff_Name, BrushStrokes.Aff_Name, Sierra.Brand_Name, BrushStrokes.BSArtistName, Sierra.YAvailable, BrushStrokes.BSStatus, Sierra.Percent_Savings, Sierra.Unique_Product_ID, BrushStrokes.BSProductID, Sierra.Main_Cat, BrushStrokes.BSCol_Cat, ContentTypes.ContentType, Sierra.Product_Description, BrushStrokes.BSDescription
FROM ((ContentTypes INNER JOIN Sierra ON ContentTypes.ContentTypeID = Sierra.ContentTypeID) INNER JOIN BrushStrokes ON ContentTypes.ContentTypeID = BrushStrokes.ContentTypeID) INNER JOIN Cats ON (BrushStrokes.CatID = Cats.CatID) AND (Sierra.CatID = Cats.CatID)
WHERE (((Sierra.Display)=1) AND ((BrushStrokes.Display)=1));
-----------------------------END SQL QUERY------------------
Using 2 Sum Functions In Multiple Join Query.
Sep 16, 2004I have a problem with using two Sum() functions in a multiple join query. It is an Access database that is accessed by VB6.
These are my tables with some sample records:-
Payment(payID, payCustID, payDate, payAmount)
1, 4, 30/09/2004$5.00
2, 4, 30/09/2004$120.00
3, 4, 30/09/2004$20.00
Customer(custID, custName)
1, steven
2, julie
3, blake
4, kate
Invoice(invID, invCustID, invDate, invAmount)
1, 1, 10/09/2004, $50.00
2, 2, 11/09/2004, $50.00
3, 1, 11/09/2004, $150.00
4, 3, 10/09/2004, $20.00
5, 4, 10/09/2004, $5.00
6, 4, 11/09/2004, $120.00
This is my query:-
SELECT DISTINCTROW Customer.custName, Sum(Invoice.invAmount) AS SumOfinvAmount, Sum(Payment.payAmount) AS SumOfpayAmount
FROM (Customer LEFT JOIN Invoice ON Customer.custID = Invoice.invCustID) LEFT JOIN Payment ON Customer.custID = Payment.payCustID
GROUP BY Customer.custName, Customer.custID;
Result:-
custName SumOfinvAmount SumOfpayAmount
blake $20.00
julie $50.00
kate $375.00 $290.00
steven $200.00
The resulting SQL query returns incorrect invoice and payment totals for Kate. The Sum() function has duplicated. Can anyone tell me how I would change my query to fix this problem?
Thanks Steven
Query Multiple Tables Using INNER JOIN And Multivalue Fields
Jan 28, 2013Here is my current table structure (I have omitted some fields from this example and have given some sample data in italics to make the table structure more clear.
tblEmployees
ID (autonumber) 3
EmployeeName John
EmployeePhone 555999555
EmployeeLocation New York
tblClients
ClientID (autonumber) 1 , 2 , 3
ClientName ABC Company , XYZ Company, PQR Company
fkeyLocationID
tblLocations
LocationID 1 , 2
Location New York , Chicago
tblEmployeeClients (junction table)
fkeyID 3
EmployeeClients (multivalued number) 1,2
The junction table tblEmployeeClients only stores ID of the Employee and in the second column (which is a multi-valued field), the ID of each of the clients the employee Supports.
I am trying to generate a report that lists say, EmployeeName alongside the clients supported by the Employee (listing the client location is not required, however, it would be good to know how to do that as well).
The report (for the example above), should look like this:
Name Clients Supported
John ABC Company, PQR Company
Currently, I am able to get :
John 1, 2 i.e the client ID for the clients that the employee supports instead of the corresponding company names.
Modules & VBA :: ADO - How To Delete Data From Only One Table From A Join
Dec 17, 2013How to Delete data from only one table from a Join? OR How to set the ADO recordset unique table property?
On Access 2010 module I have a class that manipulate data (save, read, edit and delete) from this statement:
Code:
Private Const strNomeTblFonte as string = _
"SELECT ER.*, ET.intTipoExame, ET.txtNomeExame, FROM tblExamesTipos
ET INNER JOIN tblClientesExamesRequisitados ER ON ET.idExamesTipos = ER.intQualExame;"
Private Sub Class_Initialize()
Set mCol = New Collection
[Code] ....
Problem: I need to delete data from only one table (tblClientesExamesRequisitados) of a inner join, but only delete from the "wrong" (tblExamesTipos) table.
After going to msdn on title: Unique Table, Unique Schema, Unique Catalog Properties-Dynamic (ADO) I attempted to address the problem with this line in the Class_Initialize():
Code:
Recordset.Properties("Unique Table").Value = "tblClientesExamesRequisitados"
But only generates this: Run Time Error 3265 - Item not found in this collection...
I know, if I open another Rst and use a Distinctrow, or open only one table, as in "DELETE * FROM tblClientesExamesRequisitados WHERE intQualExame = " & miQualExame & ";" it will resolve, but, why "Unique Table" isn't functioning?
Multiple Delete Query With Junction Table??
Dec 14, 2007Hi there.
I'm trying to archive records by using append and delete queries that span accross 12 tables.
the VBA will execute them all. However, I have a junction table near the end; It will be the Junction table, then a parent then a child table. I can get the junction table keys deleted but after that I can't get the Parent and the child table records deleted.
Does anyone know how to do this. the parent table can't delete any records because I've deleted the junction table keys first but if I delete the parent table keys access obviously wont let me because it's leaving orphan records.
I'm sure there's a way to do this. hope someone can help me.
Thanks.
Queries :: Join Multiple Fields From One Table To Same Table In A Query
Nov 21, 2014I have a table that is basically a survey form. The same series of options was available for 35 questions, and the table used to have a text string written for each answer. Because of all the repetitive data, I created a second table that assigned a number value to each of the nine possible options in these 35 separate fields. What happened is that, instead of the same text strings repeated over and over (and taking up real estate), now each of the 35 columns had a single number in them.
Now comes the day of reckoning and TPTB want a query with the raw data and the original text strings back in instead of the numbers. I was thinking doing something along the lines of a DLookup, but I can't seem to make that work in a query correctly. Apart from calling the same table and linking it over and over to the different fields in the original data table (see photo for how insane that is).
Multiple Join Problem
Feb 11, 2005I am working on a "course registration" system using asp (vbscript) and MS Access. There are 4 tables as shown:
activity (1 entry per course - key is ActivityID)
activitysections (1 entry for each course section for an activity - key is SectionID - ActivityID is also in the record)
registrations ( 1 entry for each registrant in the section - key is RegistrationID - Section ID and PersonID are also in the record)
users ( 1 entry for each student with their demographic info - PersonID is the key)
When I run the following query on the test Access database:
SELECT activity.ActivityName, activity.ActivityStatus, activity.ActivityID, activitysections.SectionNumber, activitysections.SectionStatus, activitysections.SectionSchedule, activitysections.SectionCapacity, activitysections.SectionParticipants, activitysections.SectionDuration, users.FirstName, users.Initial, users.LastName, users.NameSuffix, users.PreferredName, users.Gender, users.BirthDate
FROM users INNER JOIN ((activity INNER JOIN activitysections ON activity.ActivityID = activitysections.ActivityID) INNER JOIN registrations ON activitysections.SectionID = registrations.SectionID) ON users.PersonID = registrations.PersonID;
I get the correct results - 1 entry wherever there is a registrant for a section of an activity.
However, I want to run a query that lists all the sections regardless of whether there is a person registered. To do that I changed the query to a left join as follows:
SELECT activity.ActivityName, activity.ActivityStatus, activity.ActivityID, activitysections.SectionNumber, activitysections.SectionStatus, activitysections.SectionSchedule, activitysections.SectionCapacity, activitysections.SectionParticipants, activitysections.SectionDuration, users.FirstName, users.Initial, users.LastName, users.NameSuffix, users.PreferredName, users.Gender, users.BirthDate
FROM users INNER JOIN ((activity INNER JOIN activitysections ON activity.ActivityID = activitysections.ActivityID) LEFT JOIN registrations ON activitysections.SectionID = registrations.SectionID) ON users.PersonID = registrations.PersonID;
I get this wonderful little message saying "join expression not supported". Can anyone give me some advice on what I am doing incorrect. Thanks.....
Three Tables - Multiple Join Duplication
May 1, 2014When I join three tables I get duplicated records in Access SQL. How can I solve this? Using distinct is no options.
View 4 Replies View RelatedReports :: Join Together Multiple Rows To One Field?
Jul 1, 2015I have been struggling with combining multiple rows into one row for an order summary report.
Specifically, I have three tables (Products, Orders, and OrderDetails)
Products
=======
ProductID = AutoNumber
ProductName = String
Orders
======
OrderID = AutoNumber
OrderDate = Date/Time
OrderInfo = Memo
CustomerName = Text
CustomerAddress = Text
PaymentDetails = Text
OrderProductDetails
=========
OrderNumber = Number (Linked to OrderID)
ProductNumber = Number (Linked to ProductID)
Notes = Memo (Notes on product customization)
The OrderProductDetails table is in many to one relationships with both Orders and Products table. If I place an order with 3 products, the OrderProductDetails datasheet would be:
OrderNumber ProductNumber
========================
1 78 (Product 1)
1 89 (Product 2)
1 56 (Product 3)
On my report, instead of 3 entries for this order, I would like show just one entry under a column "Products Ordered" ---> Product 1, Product 2, Product 3.
I tried the Concatenate macros I found online, however, I am unable to configure them properly to use for this purpose.
I am using Access 2010.
Query Lets Me Delete But Doesn't Actually Delete...
Apr 2, 2007I have a query based on two tables, joined on one field (this field is the primary key in Table A. Table B doesn't have a primary key). When working inside this query, it gives me the option to delete records. However, when I try, the row disappears from the query but then if I save and close and either re-open table A or the resulting query, that record is back. It doesn't actually delete. If I go to table A directly and delete it, it's gone for good.
How can I make it so that when I delete the record inside the query it actually deletes? And if that's not an option, can I make it so that it doesn't seem like I can delete records inside the query so that people don't make the mistake?
How To Delete Record Using Delete Query
May 19, 2011I 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.
Modules & VBA :: Join Multiple Values Into String For Summarizing Data On Reports And Exports
Mar 14, 2014I have to join multiple values into a string for summarizing data on reports and exports. This process in vba is taking up to 10 minutes to process and will get worse as the size of these reports grow.
My method so far is to query the individual items into a recordset, loop through the values, adding them to the string then return the string in the query.
Here is an example:
Public Function SO_Description(intSO As Integer) As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQLSelect As String
SO_Description = "Profiles: "
Set db = CurrentDb
[Code] ....
Delete Multiple Records
May 9, 2007how do i delete multiple records in a table, for example if i have a table that as serial_num 123 twice, how do i delete the two records, basically i don't want to delete one record, i want to delete the two records. Thanks.
View 5 Replies View RelatedHow To Delete Multiple Records
Aug 15, 2005Hi,
I'm trying to delete say 4000 records, out of total 5000 records. I want to keep 1000 records based on the ID.
How can I write the SQL DELETE query, so that only 1000 records remain based on the query?
Thank you in advance for your great help.
regards,
Pranesh
Alberta, Canada
Delete Records With Multiple Relationships
Sep 20, 2006Hi
I am in the process of designing a sports database and have come across a problem that I can not seem to fix.
I have 3 tables that are linked, they are Customer, Program (this is the customers exercise program) and Health (the customers health records).
I have tried to set up a query that deletes a customer if a command button is clicked. This works find as long as the table I am deleting the data from is not linked to another table.
However when I try to delete a customer record it says that I can't as the data is linked and I have to delete the relationships first.
The only way I can think of doing this is to setup a query that deletes all the data in each table at the same time. I can not get this to work though.
Is this a problem that can be solved or will I have to change the way my relationships are set up?
I would appreciate any help or suggestions.
Thanks
How Do I Delete Multiple Rows Or Records?
Apr 26, 2006Is there a way to select several rows of records in a datasheet view of a table and delete all of the selected records?
Every time I try to delete more than one record at a time, I get the following message: "The Micorsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time."
Then it will let me delete one record out of the selection.
I am the only one in that particular table at the time I am trying to delete the record.
Thanks in advance for your help...
Need To Delete Multiple Tables From One Form
Mar 22, 2008Hi, I am designing a database for a basic payroll function. So far everything was going well until i started to design forms. I cannot delete multiple table entries from one table that connects two tables. This situation is as such:Employee Table: contains entries as follows:ID: Autonumber (PK)First Name Last Name PayCategory (Salary or Hourly) (List Box)PayInfo Table: contains entries as follows:ID: Autonumber (PK)Employee Name (Text)Pay RateThe relationship of these 2 tables is a one to many relationship; where the ID from Employee Table is connecting to Employee Name in PayInfo Table.When i created a form using these 2 tables and added a delete button to it, the entries from Payinfo table get deleted when i click the delete button, but not from the employee table.I have tried everything i can think of :( . What am i doing wrong? Can someone please guide me in the right direction. Any help would be greatly appreciated.
View 2 Replies View RelatedModules & VBA :: How To Delete Multiple Records
Sep 7, 2014If I want to delete multiple records (select more than a record and right-click delete) how can I achieve this
given the following with one record:
Private Sub Form_Delete(Cancel As Integer)
DoCmd.RunSQL "Delete * from Trial where TrialID =(Text40)"
Me.Refresh
End Sub
PS:- I am trying to solve the cannot update /delete because of "modifications due to multiple base tables "issue and I am not sure if attempting to add in the On delete event would fix it (instead of an instead of delete trigger since it would be useless to setup triggers with one table to update ). I am thinking of doing the same with update in the after update event.
WHERE Clause On LEFT JOIN : Why Do I Get Join Expression Not Supported Message?
Nov 10, 2006I've been toiling with the issue of WHERE clauses on the "Right" side of Left Joins. I'm aware that you need to use JOIN ON......AND.... rather than JOIN ON....WHERE.... if the WHERE relates to the Right Hand table.
I've even got an example in my DB where the above works, but now am struggling to use the same theory for other tables. Therefore, I went and created two Mickey Mouse tables to test the logic but am getting an error.
I have
Table 1 with one field called Field 1 - values A, B, C
Table 2 as follows
Field 1.....Field 2.......Field 3
A.............100
C.............200..........XXX
I hoped to have a query that finds all records on Table 1 and records on Table 2 where Field 1 matches on the two tables and Field3 = XXX
My SQL is
SELECT Table1.Field1, Table2.Field1, Table2.Field2, Table2.Field3
FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1
AND Table2.Field3="XXX";
but I get Join Expression not supported
What am I doing wrong?
Thanks
Andrew
Create A JOIN Of Different Tables Called Join A Variable And List
Nov 16, 2013And then called this join as a symbol or variable, and then have it use to select the items from these joined tables, can this be done in Access? Here is an example of a code that I created, but it has an error message saying the FROM syntax is incorrect.
Code:
SELECT firstJOIN.trainID, firstJOIN.trainName, firstJOIN.stationID, firstJOIN.stationName, firstJOIN.distance_miles, firstJOIN.time_mins
FROM (trains INNER JOIN ((station INNER JOIN lineStation ON station.stationID = lineStation.stationID)
INNER JOIN bookingLeg ON bookingLeg.startID = station.stationID or bookingLeg.endID = station.stationID )
ON trains.trainID = bookingLeg.tid) as firstJOIN
Can Access do something similar to this, in the FROM statement I joined 4 tables, because each unique fields are in each table and I have to joined them to get those fields. I called this join firstJOIN and in the SELECT statement, I list those columns in the table by calling it firstJOIN.trainID. Can Access do something like this, but syntax it differently?
Delete Duplicate Records Based On Multiple Criteria
Sep 21, 2015I need developing a new delete query criteria to add onto existing delete duplicate queries for deleting duplicate values. I need the new delete duplicate query to take precedent over the other two so that those values retained in the first query are kept retained after going through the second and third delete queries. Attached is a document laying out the request.
View 1 Replies View RelatedDelete Records That Match Multiple Criteria From Second Table
Jan 23, 2012I would like to create a query that will delete records that match several fields from another table. This is complicated by the fact that one of the fields will be in one of 3 columns.I have attached a test database (no real details), all Sheet2 entries need to be deleted from Sheet1.
What I need to do is delete records that have the same 'Surname' and 'DPS' value but also the same 'Line5' value from Sheet2 in 'Line3' or 'Line4' or 'Line5' in Sheet1.The 'Surname' and 'DPS' are no problem, it's the variable position of the third field. I think I could do it in three separate queries but it would definitely be better in one.
Suppressing Dialog On Multiple Append And Delete Queries In A Macro
Aug 19, 2004I have a macro setup that deletes all the records in 2 different tables, then using about 8 queries, appends several records to the two tables where data was deleted. When these macros run, several Yes/No/Cancel dialog boxes come up for each query, first asking you if you are sure you want to do this, that it is going to change data in my table, and then second telling me how many records it's going to append or delete.
Is there a way to make it automatically run Yes to all of these dialog boxes? That would really help out a whole lot. Of instead of that, but maybe some sort of VB code that could do all the deleting and appending without the need of the Macros, and that doesn't require user intervention. I have my Macro's setup on the "On Click" event in a form, so using VB would be no problem at all.
Any Suggestions?