Complex Queries (Append/Update/Delete?)

Sep 4, 2005

I am accessing an oracle database that has several thousand records in it. I am quering for specific requirements, but would like to save my query results in a local access database for faster searching capabilities. Is there a way for me to set up a query that will go out to my oracle table files, select the records that pertain to my search criteria, and add records to locally stored tables without duplicating itself each time that I run the query? I would appreciate any assistance in this matter. Thanks for your help!

View Replies


ADVERTISEMENT

No Warning Messge When Runing The Delete, Append Or Update Query!

Apr 11, 2007

why isn't my Access giving me warning before runing the delete, append or update query because usually it warns you that you are about to append, update or delete the following number of records. It must be the settings, can someone help!

View 9 Replies View Related

Append/delete Queries

Sep 23, 2007

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.

thanks,
Vincent

View 14 Replies View Related

Queries :: Append And Delete Query - Single Records

Mar 27, 2013

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.

View 2 Replies View Related

Suppressing Dialog On Multiple Append And Delete Queries In A Macro

Aug 19, 2004

I 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?

View 10 Replies View Related

Queries :: Extract And Replace With Additional Data Append Delete Query

Jul 1, 2015

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.

View 1 Replies View Related

Modules & VBA :: Code To Delete Table And Then Run 4 Append Queries To Refresh Data

Jun 11, 2014

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.

View 9 Replies View Related

Update/Append Queries

Sep 19, 2005

Just wondering if someone can point me in the right direction so that i can solve my problem?

Basically what i have is a select query that carries out some calculations based on data entered. These calculations are expressions as i am sure you guys know. what i want to do is put the value from the expression/calculation into my table in the correct fields made for these values. However i have tried everything i can think of to get this data into the tables fields but to no avail.

example. Expr1: [field1]*[field2] the answer created by [Expr1] is the value i want to be placed in [field3]

Can anyone help me on how i can do this

View 4 Replies View Related

Update Queries Vs Append Queries, Indexes?

Aug 7, 2007

This is a very simple problem most likely for the masses, but I am new to access. I have employees who enter will enter information about specific tests on electronics components into separate tables by a form. I know my method is poor, but this is how it works. I got assigned this database at my internship this summer because they ran out of thigns for me to do.....I've never even used access before, so as long as it works, they're happy. I have a form which which writes to a first table with 20 fields. I then have separate update queries which take the data in the first table and put it in all the other tables. Trust me, i know this is pretty much the opposite of the whole point of a relational database, but I am and was limited by time, for the size of the database they want, there was no time to learn about normalization etc etc. Sorry this is wordy, but THE QUESTION is......if they run multiple tests wtih the same information for some of the fields I have fields named "Run#, Unit#, Date" etc, even if there is records wtih the same information, hwo do i get it all to show. Right now, it seems to be rewriting over the same records. If they run 3 tests on unit 10 on August 1st, how do i get it so all those show up. I think its somethign wtih in the table, for the primary keys, changing the Index: No, Yes(Duplicates) Yes(No Duplicates) but I could be way off. Thanks, if the question doesnt make sense i'll try to rephrase it, I apologize I'm running on about 45 min of sleep:confused:

View 3 Replies View Related

Queries :: Append And Update Simultaneously Within Same Table

Jul 25, 2015

I am using a DB to, among other things, calculate the monthly salary of my service users. Obviously, I have to do it every moth. Most of the data from the previous month will be the same in the next month, except date. Even if some of it is different, having the previous data on sight calculate the new salaries.

Every month I have to select all records with the month date (say 07/2015) I want to copy and

(1) copy paste the records into the salaries table, and
(2) search and replace the new records' dates.

For example, records with 07/2015 with say 08/2015 (I have to replace downwards to ensure I don't change the record of the month I need to keep).

It can't be that difficult, but I have tried append and update queries to make this automatically (say by clicking a button) but so far no luck.

View 10 Replies View Related

Noob Insert/update/delete Queries

Dec 2, 2007

Hi guys,

I was wondering if someone could help? I am using Access 2002 and I am struggling to find out out how you can insert/update/delete records through a form using the design view. Is this possible or do you need to do this another way?

Could some one point me in the direction of a comprehensive tutorial or outline some instructions for what I need to do?


I need to create a form that inserts people's details into a table



When user types in a surname as a parameter query, up pops the form with the details of the person stored in the database, and the user can update the details through the form and the details are saved to the table they came from.



Thanks in advance!

View 9 Replies View Related

Queries :: How To Make Cascade Update / Delete

Jan 13, 2015

What I'm trying to do is making Cascade update and delete between two tables. For example in my case I have Field called Full name in table1 and I want the same field in table2 that will update when something is added in table1 and vice versa. I have tried relationship but I got an error that those fields does not have any unique value.

View 3 Replies View Related

Disabled Append Queries - Due Patch Update To Windows

Mar 28, 2008

Hi,

I'm hoping someone might be able to help point in the right direction. Our IT department applied a patch update to windows 2000 overnight, which has had an impact on the Append queries in my DB.

Basically I have a number of append queries that use lookup tables to append the appropriate data from the underlaying table to the required table. They all have a relationship of "only include rows where the join fields from both tables are equal".

These were all working fine yesterday, but this morning, even though there is data in the underlaying table that meets the criteria, not data is being identified.

Has anyone any idea, as our IT department don't know and are not prepared to undo the patch update.

Your assistance would be most appreciated.

John

View 1 Replies View Related

Queries :: Append Multiple Data With Update Query?

Aug 6, 2013

In access Im working with two tables, this is my setup

tableA.documentnr
tableA.revison

tableB.documentnr
tableB.revision

Both tables are filled with data, Table B contains the same kind of data as table A, But tableA has documentnumbers with different revisions (for example revision a,b,c, for each revision a seperate row). Table B might have an identical document, but just one revision (like revision a).

Now I like to append the data of tableA to tableB, except if a revision is similiar to a revision in table A. (There is more metadata involved, but I will do it step by step)

Im not working with primarykey data, becayse in the end result table B will also have multiple (identical)document numbers with different revisions on different rows.

I tried to use the update query but it doenst append the documentnumbers where the revision is not present in table B I attached a image of the tables.

View 9 Replies View Related

Queries :: Run Append Query And Update A Field At Same Time

Nov 7, 2013

is possible to run an Append Query and update a field at the sametime?

i.e. I would like the MealDate field to be incremented by 1, just not sure how to write the code within the query!!!

This is what I have so far!!! not working

MealDate: ([MealDate](Date()+1))

View 5 Replies View Related

Queries :: Update Or Insert / Append Record In Database

Jun 5, 2013

I have two tables "TABLEA" and "TEMP"

fields in both tables are
Cust ID (Primary key)
Cust Name
Address
Cheque No
Amount
Location
Zone

I need query when i click on command button on form

if "Cust ID" which is primary key in "TEMP" Table match with "Cust ID" from "TABLEA"

It will update the record in "TABLEA" if not then append the record

View 1 Replies View Related

Queries :: Update Or Delete Query To Remove Data From Table

Sep 18, 2014

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 Related

Queries :: Append Query - Automatically Update Tables Depending On Value Of Yes / No Field

Feb 25, 2014

I am giving two tables and I need to create a macro that automatically updates these tables depending on the value of a Yes/No field. If it's No, it's in the 1st table TableOne, if it's Yes it automatically updates to TableTwo.

So, the best way I saw to go about is to set up an append query and then create a macro that runs it

So my tables have the values FirstName, LastName and isValid (more but keeping it short)

So for my append query, I put TableTwo in the pop up I get. Then, where it asks for the field I put it

Field:FirstName
Table:TableOne
AppendTo:[TableTwo].[FirstName]
Criteria:[TableOne].[isValid] = 1

I do this for all (it was autocompleted except the Criteria field). I tried to keep Criteria with data only for isValid but that didn't work. I wrote it for all the field names, still didn't work. Whenever I click run it says it'll append 0 rows.

View 5 Replies View Related

Complex Update

Oct 30, 2005

I need to import information from an XML file that includes a wide array or codes. Each code stipulates which amount is entered into a particular field.

The importation and conversion is not a problem this has been achieved successfully, however the problem exist in the update procedure. Due to the extensive amount of different codes (est. 30) an IIF statement becomes to large and complex to evaluate.

The following statement though being very incorrect it does show an example of what is being attempted.

IIf([ClaimUpdate]![claimtype]="com1" Or "com2",[tblClaims]![CommencementCIAmount],"") Or IIf([ClaimUpdate]![claimtype]="RCM*",[tblClaims]![ReCommencementCIAmount],"") Or IIf([ClaimUpdate]![claimtype]="SCH1",[tblClaims]![SchoolBasedCommAmount],"")

Basically if table [claimupdate]![claimtype] = "com1" then [tblClaims]![Related Field] = amount else leave blank.

Question is there a better way to construct the criteria or should a module or procedure be used instead to evaluate? Or is there a way to construct a nested IIF statement to evaluate a large complex criteria?

Any suggestions greatfully recieved...

If it looks like a problem, acts like a problem it must be a problem!

View 1 Replies View Related

Complex Update Issue

May 11, 2007

I could do with a bit of help on what has become a complex update issue to me.

I have a junction table to allow me to have a many to many relationship called Nominal_Junction. This relates a table called Principle_Nominal_Table to a table called Information_Scores via Integer fields called NominalID and InformationID.

The Information_Scores tables has a Integer field that is the total score for that record.
As you would expect from a junction table, a nominal can have many information records associated with them, and a Information record can have many nominals associated with them also.
I need to know the total score for each nominals summed information reports.
ie nominal 31 is associated with records 2, 4 and 5, the scores of which are 7, 6 and 3 hence the nominals total score for nominal 31 would be 16!

I thought the simplistic approach would be to re-calculate all the nominal scores everytime a record is saved. I created a query to calculate the scores against the nominalID in design view. This worked. I then tried to use this in a SQL update statement. See below. I cannot for the life of me get it to work and its gone way beyond me. Any help would gretaly be appreciated as this is the last module I need to get working.
See code:


strSQL = "UPDATE Principle_Nominal_Table " & _
"SET Overall_Nominal_Score = (SELECT Sum(Information_Scores.Overall_Score), As Overall_Nominal_Score, NominalID " & _
"FROM Information_Scores INNER JOIN Nominal_Junction ON Information_Scores.InformationID=Nominal_Junction. InformationID) " & _
"WHERE ((Principle_Nominal_Table.NominalID = Nominal_Junction.NominalID ));"

As stated, any help would be appreciated as my mind is now scambled!:eek:

View 2 Replies View Related

Complex Update Issue

May 11, 2007

I could do with a bit of help on what has become a complex update issue to me.

I have a junction table to allow me to have a many to many relationship called Nominal_Junction. This relates a table called Principle_Nominal_Table to a table called Information_Scores via Integer fields called NominalID and InformationID.

The Information_Scores tables has a Integer field that is the total score for that record.
As you would expect from a junction table, a nominal can have many information records associated with them, and a Information record can have many nominals associated with them also.
I need to know the total score for each nominals summed information reports.
ie nominal 31 is associated with records 2, 4 and 5, the scores of which are 7, 6 and 3 hence the nominals total score for nominal 31 would be 16!

I thought the simplistic approach would be to re-calculate all the nominal scores everytime a record is saved. I created a query to calculate the scores against the nominalID in design view. This worked. I then tried to use this in a SQL update statement. See below. I cannot for the life of me get it to work and its gone way beyond me. Any help would gretaly be appreciated as this is the last module I need to get working.
See code:


strSQL = "UPDATE Principle_Nominal_Table " & _
"SET Overall_Nominal_Score = (SELECT Sum(Information_Scores.Overall_Score), As Overall_Nominal_Score, NominalID " & _
"FROM Information_Scores INNER JOIN Nominal_Junction ON Information_Scores.InformationID=Nominal_Junction. InformationID) " & _
"WHERE ((Principle_Nominal_Table.NominalID = Nominal_Junction.NominalID ));"

As stated, any help would be appreciated as my mind is now scambled!:eek:

View 1 Replies View Related

Append/delete- It Has Been A While!

Dec 27, 2006

Hey everybody,

Admittingly I last did a query like this 4years ago and I know there is a simple way of doing it, but I have completely forgotten!

My problem: I am wanting to create a query which will automatically detect if a customer has NOT made an order in a period of twelve months (a customers details/orders are stored in the database: tables customer and tables order). Those who have not made any orders in 12months can be deleted (the option should be to delete them not automatic deletion.

I want the query to delete (if it is selected) all traces of that customer including their orders.

I tried creating the query and using the date function: <Date()-365, however, I want to make sure it will work.

Thanks and I hope people can help!

View 2 Replies View Related

Delete All Rows Then Append

Oct 24, 2006

I need to append some rows into a table, before I add these rows i need to delete all of the old rows.

I can't use a Make Table query because I am updating a linked table in another database.

So how can I (automatically) delete all rows in that table before or during the append process.

Thanks
Steve

View 2 Replies View Related

Append / Delete Query

Jul 26, 2007

I am trying to run a query that appends data into a table.. however, the way im setting it up.....lets say i have a table named Accouts, and under accounts there is #1 to 30. When I try to add a new account through a form, and append it to that, it takes the 31 there nwo are after i added one, and adds it to the 30 alraedy there, creating 61 instead of 31. is there anyway i can take into account for duplicates when im creating a query. im new to databasing, just got stuck with this assignment for the summer, and have no idea what im doing :( and i dont have time restart after learning normalization. i wish i did, but i dont. if it works it works, thats the bottom line. thanks everyone.

View 2 Replies View Related

Append And Delete Query

Jun 9, 2005

I have 2 databases which are mirror images of each other. one db is named rent and the other is named renthistory. in the db i have 2 tables, tblcustomer and tblradio. i would like a button on my form to append the current record to the renthistory db and then delete the current record. how can i do this?

Thanks in advance!
Tim

View 2 Replies View Related

Delete Results From Append Query

Jun 23, 2005

Sorry, I really couldn't think of a solid title for this. I'll try and explain and provide some code as well.

I've got a series of tables, one of which is linked to a Mobile Device. I have a query (herein called Query A) that queries a masterfile, named MasterFileEdit, based on selections the user makes on the front end. Query A then returns the results, sorted by section number. Query B is set up to query based on the same selections, but instead DELETE all of those records from the masterfile. Doing this makes sure that no duplicate work is performed.

Here's the code for Query A.
INSERT INTO [TABLE A1] ( OID, SystemLoc, SystemPN, Location, Part_Number, Qty, Date_n_Time, NL, RCHKL, RCHKP, oddeven, Auditor, upperlower, area, aisle, [section] )
SELECT TOP 100 MasterFileEdit.OID, MasterFileEdit.SystemLoc, MasterFileEdit.SystemPN, MasterFileEdit.Location, MasterFileEdit.Part_Number, MasterFileEdit.Qty, MasterFileEdit.Date_n_Time, MasterFileEdit.NL, MasterFileEdit.RCHKL, MasterFileEdit.RCHKP, MasterFileEdit.oddeven, MasterFileEdit.Auditor, MasterFileEdit.upperlower, MasterFileEdit.area, MasterFileEdit.aisle, MasterFileEdit.section
FROM MasterFileEdit
WHERE (((MasterFileEdit.oddeven) Like "*" & [Forms]![LocationAudit]![oddeven] & "*") AND ((MasterFileEdit.upperlower) Like "*" & [Forms]![LocationAudit]![upperlower]) AND ((MasterFileEdit.area) Like "*" & [Forms]![LocationAudit]![area]) AND ((MasterFileEdit.aisle) Like "*" & [Forms]![LocationAudit]![aisle]))
ORDER BY MasterFileEdit.section;

Everything after the 'FROM MasterFileEdit' after is the user selections that come from a form.

I need help writing the DELETE query. Here's what I've got thus far:

DELETE MasterFileEdit.OID, MasterFileEdit.SystemLoc, MasterFileEdit.SystemPN, MasterFileEdit.Location, MasterFileEdit.Part_Number, MasterFileEdit.Qty, MasterFileEdit.Date_n_Time, MasterFileEdit.NL, MasterFileEdit.RCHKL, MasterFileEdit.RCHKP, MasterFileEdit.oddeven, MasterFileEdit.Auditor, MasterFileEdit.upperlower, MasterFileEdit.area, MasterFileEdit.aisle, MasterFileEdit.section
FROM MasterFileEdit
WHERE (((MasterFileEdit.oddeven) Like "*" & [Forms]![LocationAudit]![oddeven] & "*") AND ((MasterFileEdit.upperlower) Like "*" & [Forms]![LocationAudit]![upperlower]) AND ((MasterFileEdit.area) Like "*" & [Forms]![LocationAudit]![area]) AND ((MasterFileEdit.aisle) Like "*" & [Forms]![LocationAudit]![aisle]));

I can't get the DELETE to sort. The query runs, but doesn't select the records it should. In fact, it doesn't select any of the correct records.

Thoughts: does the TOP 100 modifier work with Deletes?
Is this query even possible?

Thanks in Advance for your Help.

Matt

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved