I have two queries, both delete from the same table and both have joins..This one works:
Code:
DELETE RequestCheck.*
FROM(
SELECT TOP 3 PayRequest.RequestID, PayRequest.GBPAmount, PayRequest.Currency, PayRequest.RequestDate
FROM PayRequest
WHERE (((PayRequest.Currency)="EUR Euro") AND ((PayRequest.RequestDate)>#11/16/2014#))
ORDER BY PayRequest.GBPAmount
) AS TOP3 INNER JOIN RequestCheck ON TOP3.RequestID = RequestCheck.RequestID
WHERE (((RequestCheck.CheckID)=8));
This one does not:
Code:
DELETE RequestCheck.*
FROM 300_tblDeleteRequestCheck INNER JOIN RequestCheck ON [300_tblDeleteRequestCheck].RCID = RequestCheck.RCID;
From table 1, I join fields A, B, and C to fields A, B, and C on table 2. From Table 2, I join Fields 1 and 2 to Table 3. All the joins are Join 1.
When I pulled (Queried) fields D, E, and F from Table 1, field D from Table 2, and field D from table 3, I have a sum of $1000 under field (column) E from Table 1.
The second time I pulled data, I added fields A and B from table 1 to the query. However, I get a total of $1500 from the same column. i.e Field E from Table 1. I can understand that there will be more rows to provide further data breakdown, but I could not understnad why the total change.
Hi- I have three queries: Queries: Contain: qryPrimary - primary_code, 1Reasons, EnteredDateCount as "CountP" qrySecondary - 2ndary_code, 2Reasons, primary_ref, EnteredDateCount as "CountS" qryTertiary - tertiary_code, 3Reasons, EnteredDateCount as "CountT", 2ndary_ref
In another query to combine data for a report, I need to see: All the "1Reasons" and "CountP"; their corresponding Secondary "2Reasons", "CountS"; and finally the Tertiary "3Reasons", and "CountT" that correspond to the Secondary's.
Like a fool I thought it would be easy. I know it is probably a join problem, but I can't figure it out.
I currently have query that looks at 2 tables that hold financial information. I am querying the data to do some adding and subtracting based off a financial class of a facility. The issue I am having is that one table may have a financial code that the other does not have but I still need to show that financial class for the facility.
My looks as follows
FROM [CashValue Link] INNER JOIN TCashValue ON ([CashValue Link].FINANCIAL = [TCashValue].FINANCIAL) and ([CashValue Link].Date1 = TCashValue.Date1) AND ([CashValue Link].FACILITY = TCashValue.FACILITY) AND ([CashValue Link].CLT = TCashValue.CLT)
Initially the query below used Inner Joins, however that limits my list, when i want to see ALL elements from the MasterTable no matter what, thus my inner joins became left and right joins as follows.
Simple Query: Query1
SELECT MasterTable.Station, MasterStable.Description, MasterTable.Area, AreaTable.Percent, GangTable.GangID, GangTable.Speed FROM (AreaTable RIGHT JOIN MasterTable ON AreaTable.AreaID = MasterTable.Area_ID) LEFT JOIN GangTable ON AreaTable.AreaID = GangTable.Area_ID;
This should simply display All Records in MasterTable, (Multiple Times if Necessary) listing all the elements of AreaTable that are Linked to the MasterTable, and all elements from GangTable that are linked to AreaTable. It does this, and displays them nicely. But I can't edit the fields. I get the result:
1 | Station One | Area 1 | 45% | 204 | 1000 1 | Station One | Area 1 | 45% | 304 | 500 1 | Station One | Area 1 | 45% | 404 | 750 2 | Station Two | Area 1 | 45% | 204 | 1000 2 | Station Two | Area 1 | 45% | 304 | 500 2 | Station Two | Area 1 | 45% | 404 | 750 3 | Station Three | Area 2 | 75% | 254 | 800 3 | Station Three | Area 2 | 75% | 354 | 600 3 | Station Three | Area 2 | 75% | 454 | 700
So you can see that Area 1 has multiple Gangs (204,304,404) and Multiple Stations (1,2). If you do a simple set up like this, you'll find that you can't change the Description field (Rename "Station One" to "Hello World"). It just doesn't work, no matter which way I've tried, I can't seem to make a Query that presents all the information from MasterTable and All the Information IN AreaTable and All the Information in GangTable which will allow me to also edit the fields.
Any Help would be most appreciative, I'm tearing my hair out on this one. Thanks, Jaeden "Sifo Dyas" al'Raec Ruiner
ps - It just seems that with Junction Tables and all the many to many relationship designs I have tried, you'd be able to change the non-related fields. I understand that you can't change the "ID" fields, but the others should be editable.
I have 2 forms which allow the user to first select a catergory. They can then select a sub category based on the selection made in the first box.
I have the form working 90% but can't get the list to filter based on the previous selections.
The code I'm using to generate the listbox rowsource is shown below;
"SELECT tblcatctry.CtryID, tblcatctry.Country, tblcatvtry.zoneID " & _ "FROM tblcontactsCountry RIGHT OUTER JOIN " & _ "tblcatctry ON " & _ "tblcontactsCountry.CtryID = tblcatctry.CtryID " & _ "WHERE (tblcontactsCountry.CompanyID IS NULL) OR " & _ "(tblcontactsCountry.CompanyID <> " & VarCompanyID & ") " & _ "GROUP BY tblcatctry.CtryID, tblcatctry.Country " & _ "ORDER BY tblcatctry.Country"
I want to be able to add in a join to the table tblcontactszone which has the selections previously made for the fields, ZoneID and CompanyID. How do I filter the above further?
I am building a select query which is grabbing data from multiple tables with items being linked by a unique field "Certificate_ID".
I have created joins between table A and Table B, and Table A and Table C, linking both by Certificate_ID
All have the join property set to select all Records from table A, but only those from Table B and Table C where the joined fields are equal.
Table A has 5000 records. Am I correct to assume that my query should only return a max of 5000 records as well? When I select Certificate_ID from A and another column from B it only gives me the 5000 unique records. When I add in a column from Table C it is however returning something like 7500 records, with several being duplicates with the same data in every column.
Why it is choosing to duplicate records and give me more than I want. I am sure I am overlooking something simple.
My boss and I are in a jam. We have been using Access to run a reporting process, but one of our tables will exceed the maximum fields allotted this month. Our thought, dump the table into SQL Server and use the GUI interface provided in Access Projects.
Unfortunately, the query designer seems to have a few drawbacks. The one that effects us the most is in using UPDATE queries where more than one table is used to determine records to be updated. In attempting it, we get the message: "The designer does not graphically support the Optional FROM clause SQL construct".
Now I know we can manually create Update Queries, but we often need 1,000+ in a short period of time. Manually punching in all the fields involved and other code just isn't timely.
Question: Is there some alternative, service pack or anything else that would allow us to graphically create these Update Queries using Access Projects or even SQL Server 7.0? I would hate to have to scrap all the work we did over something that seems so minor.
Is it possible to delete records in a table if it has no matching records in another related table by using a delete querie. I can find plenty of info on deleting matched records but nothing on unmatched.
Hey guys, I got two questions about delete queries,
Question 1: Is it possible to use the delete query to delete records from two different tables? What I'm looking at is deleting a customer from a customer list, and customer records from a distribution list, which are in two different tables. If not, is there an alternative way to accomplish this....ie write two queries but use macros somehow?
Question 2: I would like to add an "Are you sure?" message-box type feature into the delete query so that the user has the ability to think and back out if neccessary. Any suggestions for accomplishing that?
Im pretty new to database structure, terms and whatnots. Im developing a database for my organization solely based off of my limited knowledge of coding structures and languages. The question may seem a little novice, and just wanted to preface it with that 8)
Delete Queries seem to delete records based off of certain criteria. The question is this: Is there any way to use a delete query to delete a COLUMN in an entire table rather than a record? I dont even need criteria to be there, im basically importing information from an excel spreadsheet that is a common format for our organization and trying to remove info that isnt pertinent to our portion of the business. So basically theres a few columns that really just dont make a difference to us.
I got these two append and delete queries in my database that i want to run on startup. I was wondering if there is a way to not have the msg that pops up to ask if you are sure you want to append/delete 0 record to table if there isn't any record that matches the condition set. I'm sure there is a way because if there isn't any record that matches the condition set in the queries then i don't want to have to click "No" everytime the database starts.
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.
In this example I am only interested in the first and last rows, since the ones in between have the same data in the last few columns. They are not true duplicates since the time stamp for each is different.
Currently thinking I need to compare each and every row to the prior row in VBA and delete if criteria match.
I'm trying to delete data (no archiving required) from 5 tables that all have a one-to-many relationship between them and keep getting the following error: "Could not delete from specified tables".
I've tried everything I can think of including the following:
* Set unique records property to 'Yes' * Enable referential integrity and cascade delete records in the relationship diagram * Checked the DB isn't read only * Have correct permissions to delete records
I'm the only person in the database but I'm at a loss and short of me actually deleting the 23891 records manually I can't think of what else to do!
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.
Have joined "vessel_code" fields of the both tables by join type 2.
by using the query as data source made a form to edit/update/delete data. the problem is, when delete a record by using the form, it will delete the related data in the master file as well, which i do not want.
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"