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 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.
I have a database that needs data to be reentered every school term, at the moment i am having to delete selected data fields manually. im looking to create a query so that the data is deleted by running it. The data would be returned to a blank field. I have tried using a delete query but it is asking for the selected table, even though a selected table exists. Using the update query i am faced with updating the query to a typed word however i just want it blank.
View 1 Replies View RelatedI have 4 queries that run and prepare a temporary table for which I then have a sales report generated . THis report has been active for over a year exactly as is but now the past week I am getting an error message that says "The expression is typed incorrectly or is too complex to be evaluated".I used the query designer in access to create them, here they are in SQL VIEW
My Make Table Query:
SELECT [Job Table].[Job Number], [Job Table].[Date Sold], Commission.originalCRate, Customers.[Last Name], [Job Table].Salesperson, [Job Table].Split, [Job Table].[Job Type], Commission.SaleAmount, Commission.JobCost, Commission.SPR INTO [New Sales Report Temp Table]
FROM (Customers LEFT JOIN [Job Table] ON Customers.[client id] = [Job Table].[Client id]) LEFT JOIN Commission ON [Job Table].[Job Number] = Commission.[Job Number]
WHERE ((([Job Table].[Date Sold])>#12/31/2012#) AND (([Job Table].Split)=No));
Query 2:
INSERT INTO [New Sales Report Temp Table] ( [Job Number], [Date Sold], [Last Name], Split, SaleAmount, Salesperson, [Job Type], OriginalCRate, SPR, JobCost )
SELECT [Job Table].[Job Number], [Job Table].[Date Sold], Customers.[Last Name], [Job Table].Split, [saleAmount]*[SPr] AS TotalSale4, [Job Table].Salesperson, [Job Table].[Job Type], Commission.OriginalCRate, Commission.SPR, [jobCost]*[SPR] AS JCost
FROM (Customers LEFT JOIN [Job Table] ON Customers.[client id] = [Job Table].[Client id]) LEFT JOIN Commission ON [Job Table].[Job Number] = Commission.[Job Number]
WHERE ((([Job Table].[Date Sold])>#12/31/2012#) AND (([Job Table].Split)=Yes));
[code]...
When I run the queries as a query and view data in datasheet they all work, however when I run the report I get the error message.My Access DB is a front end connected to an SQL server backend. I have never created queries in SQL just in Access.
I have a Table1 served by Form1..It is a list of: UnqID, process, quantity, totaltime(in seconds).I want to click on a record to bring up a filtered Form2 with the chosen record on it.What I want to be able to do is to now split the quantity (and the time) and put these new records back into Table1 and delete the original record
EG
ID1,10,write a report,2400
I want to delete this and replace it with two (or three/four etc) replacements, but still adding up to 10 quantity and 2400 seconds so that the new data could be:
ID2,5,write a report,1200
ID3,5,write a report,1200
My initial thoughts are to create a holding table to:Append filtered data on Form2 to a holding Table1hld (i don't know how to do this) delete data in Table1.then enter the new quantities into a holding Table2 (that I will input myself) and then append (through a series of queries back into Table1).The first problem is how to append (and subsequently delete) the filtered record from Form2 to Table1hld.
I am getting the following error on my query:
"This expression is typed incorrectly, or it is too complex to be evaluated For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."
Below is my SQL for this query:
SELECT DISTINCTROW [OI Category Aging Query].[Age List], [OI Category Aging Query].[Reporting Entity], Sum([OI Category Aging Query].[Accrual $]) AS [Sum Of Accrual $], Sum([OI Category Aging Query].Unearned) AS [Sum Of Unearned]
FROM [OI Category Aging Query]
GROUP BY [OI Category Aging Query].[Age List], [OI Category Aging Query].[Reporting Entity];
It's just a simple query to sum up two columns from another query. This query is for an OI Category Aging report, and I have another MI Category Aging report that is working perfectly fine. They almost mirror each other, except for a few calculation differences. They both start from the same table. And my OI Category Aging Query seems to be working just fine. I cannot find any errors or N/A's in either the Accrual $ column or the Unearned column. Any suggestions on what to do or where to go from here? A simple sum on only the Accrual $ column or the Unearned column doesn't work either.
The OI Category Aging Query is 198,711 rows, so I can't drop it into excel.
Any help would be greatly appreciated!
Thanks,
Jason
I need to delete duplicate rows (or create new table without them) but the duplicate is not the entire row of data.
Date Time LeagueMatchingId League HomeMatchingId HomeName AwayMatchingId AwayName
HomeScore AwayScore HomeRedCardCount AwayRedCardCount FTOU FTOverOdds FTUnderOdds
20121202 10/02/2014 20:34:02 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98
20121202 10/02/2014 20:34:02 17 FRANCE LIGUE 1 147 Lorient 154 Toulouse 0 0 0 0 1.5 0.88 -0.98
[Code] .....
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.
Query4 is the result from left joining of Query3 and Query1 and I would like to delete some of the null data in query 4
while I execute the following code
DELETE DISTINCTROW Query4.*, [working hour] AS Expr1
FROM Query4
WHERE (((query4.[working hour])=0));
I get the warning of "Could not delete from specific table"
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.
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 RelatedOkay 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.
Hi,
I want to create a delete query that uses information stored in an external excel file. This file is constantly being updated so it isn't ideal to keep loading it into the access database. I need to use the excel file to remove any records appearing in my access table that are matching with the excel file. Make sense? Can anyone out there help - it this at all possible? Thanks,
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.
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.
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
I have two linked tables (Unique_ID)
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.
How to DELETE rows in one table that have a field value that matches a field value from another table? Tried these but sytax errors:
DELETE * FROM tmpBankDebitsMinusJE
WHERE tmpBankDebitsMinusJE.ConcatenateBankDebits IN
(SELECT tmpJournalEntryChangeOrders.Concatenate);
DELETE FROM tmpBankDebitsMinusJE
WHERE ConcatenateBankDebits IN
(SELECT Concatenate IN tmpJournalEntryChangeOrders);
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.
What am I missing?
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 use the following code to delete from a table all records except those meeting the WHERE criteria:
Code:
DELETE tblABC.*, tblABC.SubjectID
FROM tblABC
WHERE (((tblABC.SubjectID)<>99 And (tblABC.SubjectID)<>432));
I'd like to run this exact same query, but on many other tables, all of which are stored in tblTablesToClean (TableID, TableName).
Any good way to have Access loop through the list of tables in tblTablesToClean, each time passing the name of the table into the DELETE code and running the code, until all tables have been processed?
I am creating a database where the records of individual students are to be moved to different table depending on where they are in the graduation process. In order to do this, the secretary will enter the student in the "90 Hr Request" table (think of it as the first step), and move them down the line of tables (4 in total) until the final "Completed" table. Each table in the progression has more and more fields. However, a student (with their ID number as the primary key) can only be in one table at a time.
I understand this does not sound like a traditional database, as the data is not normalized. However, this is being used more as a filing system than anything else. Their data is primarily paper based (for legality reasons), but wishes to keep it organized in a database so they can run queries and print reports.
Currently, I am creating a macro that will run specific queries (in an order). I have made an append query that will move the records over, then I used a update (to null) and delete query combo to delete the old individual record. I made the delete + update query work by using a selected criteria.
How to make the append query move only one student's record at a time.
Hi at the moment I am using a bit of VBA code like below:
Public Const currentYear As String = "0708"
Static Function GetCurrentYear()
GetCurrentYear = currentYear
End Function
I then call this from my ms access sql statement with GetCurrentYear() am I correct in thinking this will only need to be evaluated once (I am just thinking in terms of performance) as it is a static function and a constant or is there a quicker way to do this. I couldn't see a way to easily get the value from a constant without a function. I may be missing something though thanks for any advice.
The control source for the Activity Summary report in my DB is the primary data table. The report contains about 12 fields, each with a statement like this one as its control source: =Sum(Abs(Year([RecDate])=Year(Date()) And ([NotifType]=9)))
Up until yesterday afternoon the report was working just fine. But suddenly when I run the report I'm getting this error:
"The expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."
I have no idea why this problem suddenly popped up when the report had been running just fine for weeks.
Here's what I've done so far to try and fix it:
1. Copied the report and gave it a new name, thinking if the report were somehow corrupt creating a new one might fix it. No such luck.
2. Tried running the report after removing each control source statement one-by-one, but the error didn't disappear until all the statements had been cleared.
Anyone have any ideas? I'm willing to post the DB if that will help.
Hey guys,
I am getting this error "This expression is typed incorrectly, or is too complex to be evaluated"
For this query:
SELECT first([TblProp].[Name]) AS [SName], First([TblProp].[CommentDate]) AS DateCommented, First([TblProp].[No]) AS BNum, First([TblProp].[Indication]) AS Ind, First([TblProp].[PropSubmitted]) AS DateSub, First([TblProp].[Contact]) AS PrimCon, First([TblProp].[Prepared]) AS PrepName, First([TblProp].[Comment]) AS Comment,First([TblProp].[Value]) AS ValueNew, First([TblProp].[Rating]) AS Prob
FROM TblProp
GROUP BY [TblProp].[RFPNo];
This query had been working fine for a good long time, but suddenly it starts throwing up this message. I haven't changed anything at all with this query. Its very puzzling. I went through each of the fields and its the comment field that is causing the problem.
Would anyone have any ideas on why this might be happening?
Thanks for reading this!
Polo
I am trying to sum 3 different time fields together in a report that all are the same format but it wont allow me to.
The expression i have built is =Sum([ACDTime]+[AgentRingTime]+[OtherTime])
but I get back a error message saying...
"This expression is typed incorrectly, or it is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables."