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 Replies


ADVERTISEMENT

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

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

Append Or Replace Data In Table

Dec 12, 2005

Hi, I'm fairly new with working with Access and programming in VBA, and I was looking for some help. I have a database with many tables, and relationships between the tables. In particular, I export two of these tables to text files from one machine and need to import them back into the database on another machine. I have this working if I delete all the records before useing the transfertext command to import that table. But my problem is that I need to either append a record if it doesn't exist in the promary keys, or if the new record exists in a primary key, I need to replace it. I was thinking about reading the table to a temp table from the text file and checking each record in some sort of query to see if it needs to be appended or replaced in the table. Does anyone have any ideas of how else this could be accomplished a little easier that looping through each record, for this might take a while, as I do not know how many records can be importing each time. All I know is that I need a way of taking data from a text file and either appending or replacing the records in the table in the database. I appreciate any help I can get with this. Thank you.

View 3 Replies View Related

Queries :: Extract Data From Query

Sep 15, 2013

I am trying to extract data from a query. Part of code when form loads, my questions is bold:

'query criteria
vOrgCode = Me.tOrgCode
'sql statement
strSQL = "Select tblMembers.tOrgCode, tblMembers.tGender, tblMembers.nMonthlyIncome, "
strSQL = strSQL & "tblMembers.nFarmSize, tblMembers.tPrimeCrop, tblMembers.nNoHills "
strSQL = strSQL & "From tblMembers Where tOrgCode = '" & vOrgCode & "'"

[code]...

i cannot build a simple query and save it as reference since the number of organizations may be 25 at the minimum. this is why i want to build it on the fly, so to speak.

View 3 Replies View Related

Queries :: Using Query To Extract Data Based On Numbers

Feb 18, 2014

I have a table with a field named BinNo which has a list of items with a bin no in the format 1.234( this number can be any number up to 6000.9999).

Is there a way for me to have a field on a form where I enter a number (i.e 1) and it lists all the items with a 1 before the decimal point but not 10 or an other number with a one in it. If I enter 10 then it will only give me the items with 10 before the decimal point and not 11 etc.

I have a query that pulls all the data into it but I only want the query to show the items relating to the number I enter into the form field.

View 2 Replies View Related

Queries :: Make Replace Query To Look In Order To Find What To Replace

May 6, 2014

I have this working query:

Code:
INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA )
SELECT Left([dbo_BACKUP_ACESSOS.LOGIN],255) AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA
FROM dbo_BACKUP_ACESSOS
WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>"ACTIVE DIRECTORY") AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));

But Iwant to be able to use a set of data to be used in the Replace Statement, so I create a table to add each string I would like to have replaced by "nothing", and trying to make the replace query to look there in order to find what to replace.I also created a table where I will list the systems that I dont want in the select, so I removed the "ACTIVE DIRECTORY" and replaced by the colum that have the list of system I dont want listed.This is the result:

Code:
INSERT INTO TB_SISTEMAS ( LOGIN, SISTEMA, PERFIL, DATA )
SELECT Replace((Left([dbo_BACKUP_ACESSOS.LOGIN],255)),[PREFIXOS_E_SUFIXOS]![Valor],"") AS LOGIN, dbo_BACKUP_ACESSOS.SISTEMA, Left([dbo_BACKUP_ACESSOS.PERFIL],255) AS PERFIL, dbo_BACKUP_ACESSOS.DATA
FROM dbo_BACKUP_ACESSOS
WHERE (((dbo_BACKUP_ACESSOS.SISTEMA)<>[SISTEMAS_EXCLUIDOS]![Sistema]) AND ((dbo_BACKUP_ACESSOS.DATA)="2014-03-23"));

The thin is that this keeps asking me to enter the parameter value for "PREFIXOS_E_SUFIXOS!Valor" and for "SISTEMAS_EXCLUIDOS!Sistema"

View 6 Replies View Related

Queries :: Update Query To Replace All Data In Field

Apr 8, 2013

Is it possible for an update query to simply replace all the data in a table's field with the results from a query?

For example, I have Table A with the field Years. This table performs various calculations within based on the field Years.

I would like an update query that simply replaces the Years field data in Table A with a new set of years based on a query's search criteria.

When I run the below query, I get an error saying the query must be updateable- but Im not trying to update the query, rather the Table

I've attached a photo ......

View 1 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

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 1 Replies View Related

Open For Append Leaves Additional Characters.

Mar 2, 2007

Hi,

I´m using the open for append method to write to a text file which is then converted to an xml file.

I loop through some lines in excel and for each line I´m writing it to the text file.

The problem is every string begins with " in the xml and ends with the return character. This means it doesn´t work as an xml file.

Does the write command leave these things or have I done something wrong?

Fuga.

View 4 Replies View Related

Queries :: How To Delete Evaluated Data On The Query

Aug 26, 2014

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

View 1 Replies View Related

Modules & VBA :: Transfer Spreadsheet And Append Additional Fields

Jun 1, 2015

I am trying to use transferspreadsheet to import access worksheet and then I want to append additional fields. I am able to import the excel sheet into access, but need appending the other fields.

Code:

Dim fd2 As FileDialog
Dim xlapp As New Excel.Application
Dim xlsht As Excel.Worksheet
Dim xlWrkBk As Excel.Workbook
Dim db As DAO.Database
Dim tb2 As TableDef
Dim fdx, fld As DAO.Field

[code]...

View 4 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 :: 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 :: Append Query To Insert Data From Excel

Feb 5, 2014

I want to create a append query in access 2003 to insert data into an existing table from Excel workbook.My Table name is TokenDetail in Access 2003.

And Excel File is TokenCreation.i want to create query with msg box and requered file path for data becuase my excel files have various path and name.

View 8 Replies View Related

Queries :: Append Query Needs To Add Data From A Field To The Table

Mar 13, 2014

I want to set a table field's default value to whatever is displayed in a certain field on a certain form at the time.In other words, say I have a database with a table called TABLE1, and two fields called NAME and SCHEDULENUMBER. I have a form called CreateSchedule with a SCHEDULE NUMBERCONTROL form and a NAME form, and I can enter names onto it, and it records to the proper SCHEDULENUMBER. So if I pull up SCHEDULENUMBER 4, and add three names, when I go back into TABLE1, I can see those three new names, and each one has the SCHEDULENUMBER set to 4.

What I'm trying to do is write an APPEND QUERY to copy a list of names from a different table, and paste them into TABLE1. The problem is that the other table doesn't have a SCHEDULENUMBER field. What I want to do is put a button on the CreateSchedule form that runs an APPEND QUERY, and sets the SCHEDULENUMBER to whatever value is displayed on CreateSchedule's SCHEDULENUMBERCONTROL field.

I tried setting a default value in TABLE1's field properties for that SCHEDULENUMBERCONTROL field, but I keep getting error messages. I just want TABLE1, whenever I add a new record (regardless of how I add the record: manually typing it or clicking the append query button) to look at the form CreateSchedule, and set it's own SCHEDULENUMBER field to whatever is displayed in CreateSchedule's SCHEDULENUMBERCONTROL form.

View 1 Replies View Related

Reports :: Getting Additional Data As Well As From Query / Table

Nov 28, 2014

I have a report which is bound to a query.The body of the report comes from the query.I also have additional data that is displayed on the form that is currently 'hard' coded into an a label.

I would call this data reference data. It's not specific to a client so there is no logical place to store it other than a reference table which will have no links to other tables. ie it's not truly relational. can I get data from this reference table in the same way I could do on a form by using a combo box and having a SELECT statement?

View 10 Replies View Related

Queries :: Export Query To Excel Then Delete Query Data From Table

Nov 20, 2013

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.

View 5 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

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

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

Delete And Append Query At The Same Time

Feb 8, 2007

I don't know if this is the right place for my post. Since is in regard to queries...

I have data that I want to archive from one table to another table within a press of a button in a single form. The append work fine for me, but i tried adding the code to run the delete query to the same button as the append's one is, but giving me error that MS can't run the delete query because i have the form open exclusively. If I run the delete query with the form closed it work. How can I make the button to excute both append and delete queries at the same time.

Thanks in advance for the help

View 7 Replies View Related

Delete And Append Query At The Sametime

Feb 8, 2007

hey guys,

I'm new to this site, I have search a lot in this form regarding Delete and Append query used at the same time and couldn't find answer.

I have data that I want to archive from one table to another table within a press of a button in a single form. The append work fine for me, but i tried adding the code to run the delete query to the same button as the append's one is, but giving me error that MS can't run the delete query because i have the form open exclusively. If I run the delete query with the form closed it work. How can I make the button to excute both append and delete queries at the same time.

Thanks in advance for the help

View 1 Replies View Related

Queries :: Extract Data From One Field To Another?

Jun 17, 2013

Full Name, Forename, Surname, Salutation. This data has been extracted from another database where the Surname field was not required but it is now. So i need to find a way to pull data from the salutation field into the surname field where salutation has data but surname is null. The next one when surname and salutation are null but full name has data i need to pull that across.

View 11 Replies View Related







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